CODEWITHSUNDEEP
c#sqllinq
Frederik Hansen
Frederik Hansen

Reputation: 506

LINQ to Entities grouping

I have written the following SQL-query that illustrates what I'm trying to achieve:

SELECT
    K.id,
    SUM(BP.pris)
FROM Kunde K
JOIN Booking BO ON K.id = BO.Kunde_id
JOIN Bane_Booking BB ON BO.id = BB.Booking_id
JOIN Banepris BP ON BO.starttid = BP.time
WHERE BO.gratis = 0
GROUP BY K.id;

Basically it's retrieving the total amount spend by customers. Now I'm trying to convert this query into LINQ, as I'm using LINQ to Entities in my application. This is what I have so far:

from kunde in context.Kunde
join booking in context.Booking on kunde equals booking.Kunde
join banepris in context.Banepris on booking.starttid equals banepris.time
from baneBooking in booking.Bane
select new { Kunde = kunde, Booking = booking, BaneBooking = baneBooking, Banepris = banepris };

I would like however to be able to group these in the same LINQ-query so I dont have to group them manually afterwards. How would i go about achieving this?

I need to get the "kunde"-object and the sum of "banepris.pris".

Upvotes: 0

Views: 37

Answers (1)

James Curran
James Curran

Reputation: 103575

Without your database, I can't test this, but this should be close:

from K in context.Kunde
join BO in context.Booking on K.id equals BO.Kunde_id
join BP  in context.Banepris on BO.starttid equals BP.time
where BO.gratis == 0
group new { Kunde = K, Pris = BP.pris } by K.id into g
select new { ID=g.Key, Kunde = g.First().Kunde, Sum = g.Sum(k=>k.Pris)}

Upvotes: 2

Related Questions