LINQ to EF, Left Join and group by clause

Question

I have this SQL:

select o.prod_id, SUM(o.[count])  as [count]
    into #otgr
    from otgr o
    where o.[date]<= @date
    group by o.prod_id

    select f.prod_id, SUM(f.[count]) as [count] 
    into #factory
    from factory f
    where f.[date]<= @date
    group by f.prod_id


    select p.name, p.id, f.[count] - ISNULL(o.[count],0)  as av_count
    from products p
    join #factory f on f.prod_id = p.id
    left join #otgr o on o.prod_id = p.id
    where f.[count] - ISNULL(o.[count],0) > 0

How can I translate this into Linq? I'm stuck with this code:

from otgrr in db.otgr
where otgrr.date <= date
group otgrr by otgrr.prod_id into otgrs
from fac in db.factory
where fac.date <= date
group fac by fac.prod_id into facs
from prod in db.products
join fac2 in facs on prod.id equals fac2.Key
join otg2 in otgrs.DefaultIfEmpty(new {id = 0, av_count = 0 }) on prod.id equals otg2.Key
where (fac2.SUM(a=>a.av_count) - otg2.SUM(a=>a.av_count)) > 0
select new products { id = prod.id, name = prod.name, av_count = (fac2.SUM(a=>a.av_count) - otg2.SUM(a=>a.av_count))

Thank to everyone, and sorry for my bad english

What's your specific problem? Where's it failing? Are there compile errors? — Polynomial, Nov 26 '11 at 14:57
Sometimes it is better to go back to the intentions why the SQL was written and then try to express those in terms of your class model and Linq, without caring much about a 1:1 translation. Code generation may even surprise you with a more efficient SQL execution plan! — Gert Arnold, Nov 26 '11 at 16:19

score 0 · Accepted Answer · answered Nov 26 '11 at 19:52

You can also check LINQPad. Of course, you can split this into multiple LINQ queries (after all, the execution is deferred, so it will be executed all as one single query, without using temporary tables. It should be faster in 99% of the cases).

But in your case it can be written more simply, by using navigation properties you probably have already set up:

var result= from p in products
            select new {Name=p.Name, 
                        Id = p.Id, 
                        Count = p.Factories.Where(f=> f.date <= date).Sum(f=>f.Count) 
                              - p.otgrs.Where(o=> o.date <= date).Sum(o=>o.Count)
                       };

LINQ to EF, Left Join and group by clause

1 Answers1