Sql Statement to Linq

saan mughal

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)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ronika Jency5y ago
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 mughal5y ago
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 Jency5y ago
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 mughal5y ago
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 Jency5y ago
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
};