locked
Count subscribers per category RRS feed

  • Question

  • User-243787542 posted

    Hello.

    I have a table tblCategory(CatId, Category) and a table tblSubscribers(ScriberId,CatId,etc..)
    I want to count subscribers per category, count even the categories that has 0 subscribers. I am using Linq to sql.
    I have writen a method to do this job, my question is: if this is a good way to call the counter so many times. And if there is a better way to do this.

    here is my code.

    protected void Page_Load(object sender, EventArgs e)
    {
        List<SubScribersByCategory> scribersList = SubScribersByCategory.getGroupedList();
        if (scribersList.Count > 0)
            showList(scribersList);
    }
        
    public Category category { get; set; }
    public int subscribers { get; set; }
    private static string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    public static List<SubScribersByCategory> getGroupedList()
    {
    using (var db = new myDataContext(connStr))
    {
    List<Category> catList = Category.getCategoryListForOtherObject(db); List<SubScribersByCategory> counterList = new List<SubScribersByCategory>(); foreach (var x in catList) { SubScribersByCategory c = new SubScribersByCategory(); c.category = new Category() { category = x.category, catId = x.catId }; c.subscribers = Subscriber.countScribersByCatIdForOtherObjects(db, c.category.catId); counterList.Add(c); } return counterList; } }
    internal static List<Category> getCategoryListForOtherObject(myDataContext db) {
    var list = from c in db.tblCategories select new Category() { category = c.Category, catId = c.CatId }; return list.ToList(); } internal static int countScribersByCatIdForOtherObjects(myDataContext db, int catId) { var count = db.tblSubScribers.Count(x => x.CatId == catId); return count; }

    Thanks in advance

    Sunday, December 17, 2017 7:38 AM

All replies

  • User-832373396 posted

    <g class="gr_ gr_14 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="14" data-gr-id="14">Hi</g> <g class="gr_ gr_11 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="11" data-gr-id="11">plazzasele</g>,

    Sir, based on your requirement, please refer to the working way:

    note<g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation multiReplace" id="12" data-gr-id="12">:  .</g>Include could get the navigation properties value according to the foreign key relationship between Category and SubScriber table ;

     
    List<SubScribersByCategory> scribersList= db.tblCategories.Include(a => a.SubScribers)
    .Select(c => new SubScribersByCategory { category= c, subscribersCount= c.SubScribers.Count() }).ToList();

    Test data:

     public class SubScribersByCategory
        { 
            public int subscribersCount { get; set; }
    
            public virtual Category category { get; set; }
        }
    
    
        public class Category
        {
            
            public int CategoryId { get; set; }
            public string CategoryName { get; set; }
    
            public virtual ICollection<SubScriber> SubScribers { get; set; }
        }
        public class  SubScriber
        {
    
            public int SubScriberID { get; set; }
            public string SubScriberName { get; set; }
    
            public virtual Category Category { get; set; }
        }

    Context:

      public DbSet<Category> tblCategories { get; set; }
            public DbSet<SubScriber> tblSubScribers { get; set; }

    Reference:

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx 

    With regards, Angelina Jolie

    Monday, December 18, 2017 8:11 AM