Translation of a Sql Statement to Linq

ww2912

can sombody Help me in convert this SQL Statement into Linq
select Teilenr, isnull( (Select Sum(Menge) from bedarf x where x.kz= 'B' and x.teilenr= bedarf.Teilenr and active='T'),0) - isnull((Select Sum(Menge) from bedarf x where x.kz='D' and x.teilenr= bedarf.Teilenr and active='T' ),0) From bedarf where Active= 'T' group by Teilenr

nescafe

First, get rid of the multiple references to the table bedarf. This will make the query much simpler. Example:

SELECT Teilenr, ISNULL(SUM(CASE WHEN kz = 'B' THEN Menge END) - SUM(CASE WHEN kz = 'D' THEN -Menge END), 0)
FROM bedarf
WHERE Active = 'T'
GROUP BY Teilenr

Then, take a look at the examples (e.g. C# in a Nutshell > Chapter 9 > Grouping when querying a database) to compose a LINQ Query.

Let us know what you've come up with and we can help you from there.

markhurd

From y In bedarf Where y.Active = "T"c Group By y.Teilenr Into grp = Group
        Select Teilenr, Diff = If((Aggregate g In grp, x In bedarf Where x.kz = "B"c And x.Teilenr = g.Teilenr And x.Active = "T"c Select x.Menge Into Sum), 0) _
                             - If((Aggregate g In grp, x In bedarf Where x.kz = "D"c And x.Teilenr = g.Teilenr And x.Active = "T"c Select x.Menge Into Sum), 0)

without attempting to optimise, as suggested by @nescafe.