[es] - Kako prevesti ovaj SQL upit u ekvivalentan LINQ?

nowa
bg

Član broj: 101490
Poruke: 20
*.teletrader.com.

Profil

Kako prevesti ovaj SQL upit u ekvivalentan LINQ?

^{19.06.2008. u 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;

^{19.06.2008. u 10:50}

mmix
Miljan Mitrovic
Software Architect
Pancevo, Srbija

Moderator
Član broj: 17944
Poruke: 1663
*.eunet.yu.

Profil

Re: Kako prevesti ovaj SQL upit u ekvivalentan LINQ?

^{20.06.2008. u 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)

▪ The word 'politics' is derived from the word 'poly', meaning 'many', and the word 'ticks', meaning 'blood sucking parasites' - Larry Hardiman
▪ If the good guy gets the girl, it's rated PG; if the bad guy gets the girl, it's rated R; and if everybody gets the girl, it's rated X
▪ Illegal aliens have always been a problem in the United States. Ask any Native American

^{20.06.2008. u 10:57}

mmix
Miljan Mitrovic
Software Architect
Pancevo, Srbija

Moderator
Član broj: 17944
Poruke: 1663
*.eunet.yu.

Profil

Re: Kako prevesti ovaj SQL upit u ekvivalentan LINQ?

^{20.06.2008. u 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]

^{20.06.2008. u 11:16}