aakash kumar aakash kumar - 2 years ago 61
SQL Question

I need to convert sql to Linq

This is my sql command:

count(b.Brand) as BrandCount,
SUM(a.Qty) as DeviceCount
from (
select * from DeviceList
) as a
join DeviceMaster as b
on a.DeviceMasterId = b.Id
group by b.Brand

Here is what I've tried so far:

var v1 = (from p in ghostEntities.DeviceMasters
join c in ghostEntities.DeviceLists on p.Id equals c.DeviceMasterId
select new table_Model {
Id = c.Id,
qty = c.Qty.Value,
month = c.DMonth,
brand = p.Brand,
model = p.Model,
memory = p.Memory

I am getting the values form two tables but can't group them or add the values.

Answer Source

Once you group by a table, you lose access to the fields of the other table in the join operation, a possible workaround would be:

var results = (from a in DeviceList
                join b in DeviceMaster
                on a.DeviceMasterId equals b.Id
                group new { a, b } by new { b.Brand } into grp
                select new
                    Brand = grp.Key.Brand,
                    BrandCount = grp.Count(),
                    DeviceCount = grp.Sum(x=> x.a.Qty.GetValueOrDefault())
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download