Kako prevesti ovaj SQL upit u ekvivalentan LINQ?

[ nowa @ 19.06.2008. 10:50 ] @

Kako prevesti ovaj SQL upit u ekvivalentan LINQ?

select i.Name , sum (b.Quantum)
from Linq.dbo.Icecream i join Linq.dbo.Bill b
on i.IcecreamId = b.IcecreamID
group by i.Name, B.IcecreamID;

[ mmix @ 20.06.2008. 10:57 ] @

Imas par nacina, najkraci i najvise u duhu L2S-a je:

Code:
var xx = from i in db.Icecreams
select new { i.Name, suma = i.Bills.Sum(b => b.Quantum) };

sto generise sledeci SQL upit:

Code:
SELECT [t0].[Name], (
    SELECT SUM([t1].[Quantum])
    FROM [dbo].[Bill] AS [t1]
    WHERE [t1].[IcecreamId] = [t0].[IcecreamID]
    ) AS [suma]
FROM [dbo].[Icecream] AS [t0]

Ako bas hoces da forsiras inner join i da dobijes SQL upit kakav si naveo, onda je oblik sledeci (s tim sto se krece od tebele koja se sumira):

Code:
var xx = from b in db.Bills
group b by b.Icecream.Name into gb
select new { Name = gb.Key, suma = gb.Sum(a => a.Quantum) };

sto generise sledeci skript

Code:
SELECT SUM([t0].[Quantum]) AS [suma], [t1].[Name]
FROM [dbo].[Bill] AS [t0]
INNER JOIN [dbo].[Icecream] AS [t1] ON [t1].[IcecreamID] = [t0].[IcecreamId]
GROUP BY [t1].[Name]

U oba slucaja ces dobiti isti rezultat (mozda drugacije sortiran posto nemas orderby a ova dva primera koriste razlicite tabele kao polazne)

[ mmix @ 20.06.2008. 11:16 ] @

I da, pretpostavke

Drugo resenje podrazumeva da su ti imena sladoleda jedinstvena, ako nisu onda mora i IcecreamId da udje u groupby, sledeci query:

Code:
var xx = from b in db.Bills
group b by new { b.Icecream.Name, b.IcecreamId } into gb
select new { gb.Key.Name, suma = gb.Sum(a => a.Quantum) };

sto daje sledeci sql

Code:
SELECT SUM([t0].[Quantum]) AS [suma], [t1].[Name]
FROM [dbo].[Bill] AS [t0]
INNER JOIN [dbo].[Icecream] AS [t1] ON [t1].[IcecreamID] = [t0].[IcecreamId]
GROUP BY [t1].[Name], [t0].[IcecreamId]

I poslednja pretpostavka je da su ti tabele IceCream i Bill u db kontekstu uvezane relacijom. Ako nisu mora puna forma L2S query-a:

Code:
var xx = from i in db.Icecreams
         join b in db.Bills on i.IcecreamID equals b.IcecreamId
         group b by new { i.Name, i.IcecreamID } into gb
         select new { gb.Key.Name, suma = gb.Sum(a => a.Quantum) };

sto generise SQL:

Code:
SELECT SUM([t1].[Quantum]) AS [suma], [t0].[Name]
FROM [dbo].[Icecream] AS [t0]
INNER JOIN [dbo].[Bill] AS [t1] ON [t0].[IcecreamID] = [t1].[IcecreamId]
GROUP BY [t0].[Name], [t0].[IcecreamID]

[ nowa @ 23.06.2008. 19:01 ] @

Hvala puno, uz pretpostavke radi savrseno!