Click here to Skip to main content
14,971,877 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi Everyone,
I am very new to linq,

this is the result i am looking for in linq, which i have done in the sql 
 category name  categoryID   count (number of products)
   Movies         2           65
   PS4 Games      3           26
   Printers       8           0
   Head phones    9           0
   Speakers       10          0


i have tried that, it worked, but it has turned "0" into "1". i mean, where no product exist in a category it should say "0" instead it says "1"
 
thanks in advance 


What I have tried:

var results = from procat in this.objDB.tbl_category
              join product in this.objDB.tbl_Product on procat.cat_id equals product.pro_fk_cat_id into productdetail
              from productdetaildata in productdetail.DefaultIfEmpty()
              select new CategoryModel
              {
                  cat_name = procat.cat_name,
                  cat_id = procat.cat_id,
                  related_count = productdetaildata.pro_fk_cat_id
              };


IEnumerable<CategoryModel> CategoryList = results.GroupBy(x => x.cat_id, (key, _data) => new CategoryModel
{
    cat_name = _data.Select(x => x.cat_name).FirstOrDefault(),
    cat_id = _data.Select(x => x.cat_id).FirstOrDefault(),
    related_count = _data.Count()
});
Posted
Updated 16-Mar-21 1:12am

1 solution

Not at all clear, since you didn't describe your table structure nor show your working SQL query. But guessing based on the names, all you need is something like this:
C#
IEnumerable<CategoryModel> CategoryList = 
    from procat in this.objDB.tbl_category
    join product in this.objDB.tbl_Product on procat.cat_id equals product.pro_fk_cat_id into productdetail
    from productdetaildata in productdetail.DefaultIfEmpty()
    select new CategoryModel
    {
        cat_name = procat.cat_name,
        cat_id = procat.cat_id,
        related_count = productdetaildata.Count()
    };
   
Comments
chan200uk 16-Mar-21 7:13am
   
sorry, i forgot, here is my sql



select cat_name, cat_id, count(pro_fk_cat_id) as count
from
tbl_category c left join tbl_product p on c.cat_id = p.pro_fk_cat_id
group by
cat_name,
cat_id,
pro_fk_cat_id

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900