Sql Statement to Linq

Question

Hi Everyone, I am very new to Linq, i have a SQL statement which i want to convert to Linq but I am struggling with it, I will really appreciate any help, Sql statement: 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 Linq: var results = from d in this.objDB.tbl_category from m in this.objDB.tbl_Product .Where(m => m.pro_fk_cat_id == d.cat_id) group (d.cat_id,d.cat_name,m.pro_fk_cat_id)

Ronika Jency · Answer

Hello, Prodcount = _data.Count() change that count to below line Prodcount = _data.where(x => x.ProductcatId == null) ? 0 : _data.Count() if we don't have product, ProductcatId will be null. so we can handle that case here. if my answer, helped you. please accept my answer.

saan mughal · Answer

yup, i have tried that, it works, 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

Ronika Jency · Answer

Hello, declare below class public class productRepo { public string Name {get;set;} public int Id {get;set;} public int ProductcatId {get;set;} } public class productData { public string Name {get;set;} public int Id {get;set;} public int ProductcatId {get;set;} } taken data from DB first, var results = from procat in _context.tbl_category join product in _context.tbl_product on procat.cat_id equals product.pro_fk_cat_id into productdetail from productdetaildata in productdetail.DefaultIfEmpty() select new productRepo { Name = procat.Name, Id = procat.Id, ProductcatId = productdetaildata.pro_fk_cat_id }; then, do group by productcategoryid, you will get desired result var groupresults = results.GroupBy(x => x.ProductcatId, (key, _data) => new productData { FullName = _data.Select(x=>x.Name).FirstOrDefault(), Id = _data.Select(x => x.Id).FirstOrDefault(), Prodcount = _data.Count() });

saan mughal · Answer

thanks for the answer, I have tried that it is giving an error on the last line "Productcount = productdetaildata.Count", its not recognising Count() and also I think, the end result won't show data using group by, its like left join. so bring back all the duplicate records too. this is the result in am looking for which i have dont in the sql category name categoryID count Movies 2 65 PS4 Games 3 26 Printers 8 0 Head phones 9 0 Speakers 10 0 can you please have a look? thanks

Ronika Jency · Answer

Hello, Declare a below public class User { public string Name {get;set;} public int Id {get;set;} public int Productcount {get;set;} } Linq Query var results = from procat in _context.tbl_category join product in _context.tbl_product on procat.cat_id equals product.pro_fk_cat_id into productdetail from productdetaildata in productdetail.DefaultIfEmpty() select new User { FullName = procat.Name, Id = procat.Id, Productcount = productdetaildata.Count };