Count subscribers per category

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

Answer 1

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