Home

Translation of a Sql Statement to Linq

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

Comments

  • edited May 2016
    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.
  • 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.
Sign In or Register to comment.