I need to convert sql to Linq

Question

This is my sql command:

select 
    b.Brand, 
    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
          }).ToList();

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

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 }).ToList(); — aakash kumar, May 05 '16 at 04:34
I am getting the values form two tables...but cant group them or add the values.. — aakash kumar, May 05 '16 at 04:35

score 2 · Answer 1 · answered May 05 '16 at 04:44

You should add group by into your LINQ query and use Distinct().Count() and Sum() aggregation functions:

var query = from a in ghostEntities.DeviceList
   join b in ghostEntities.DeviceMaster on a.DeviceMasterId equals b.Id
   group b by b.Brand into g
   select new { g.Key, count =g.Select(x => x.Brand).Distinct().Count(), sum = g.Sum(x => x.Qty) };

You can find a lot of LINQ samples at https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b, I hope it will help you.

Actually this line "g.Sum(x => x.Qty)" will not compile, x will be of type DeviceMaster and not DeviceList — Zein Makki, May 05 '16 at 05:22

Zein Makki · Accepted Answer · 2016-05-05T06:09:55.193

1

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())
                }).ToList();

edited May 05 '16 at 06:09

answered May 05 '16 at 05:02

Zein Makki

29,485
6
52
63

hi, the brand and brandcount works, but device count is giving an error. – aakash kumar May 05 '16 at 06:07
Cannot implicitly convert type 'int?' to 'int'. An explicit conversion exists (are you missing a cast?) – aakash kumar May 05 '16 at 06:08
Updated, check the answer – Zein Makki May 05 '16 at 06:10

I need to convert sql to Linq

2 Answers2