CODEWITHSUNDEEP
c#linqentity-framework-4
John Sourcer
John Sourcer

Reputation: 451

Count with left outer join in linq to entites

I'm having trouble understanding a left outer join count in Linq to Entities.

My query is:

SELECT Locations.LocationId, Locations.LocationName, LocationPrizes.PrizeId, LocationPrizes.PrizeQuantity, Prizes.PrizeName, ISNULL(COUNT(WonPrizes.WonPrizeId), 0) AS WonPrizes 

FROM Locations 

INNER JOIN LocationPrizes ON Locations.LocationId = LocationPrizes.LocationId INNER JOIN Prizes ON LocationPrizes.PrizeId = Prizes.PrizeId 

LEFT OUTER JOIN WonPrizes ON Locations.LocationId = WonPrizes.LocationId AND Prizes.PrizeId = WonPrizes.PrizeId

GROUP BY Locations.LocationId, Locations.LocationName, LocationPrizes.PrizeId, LocationPrizes.PrizeQuantity, Prizes.PrizeName

My Linq is:

var locationPrizes = from l in context.Locations
                       select new
                           {
                               l.LocationId,
                               l.LocationName,
                               Prizes = from o in l.LocationPrizes
                                        select new
                                            {
                                                o.PrizeId,
                                                o.PrizeQuantity,
                                                o.Prize.PrizeJson
                                            }
                           };

I can't get the count on teh left outer join part working correctly. Any pointers?

Upvotes: 1

Views: 874

Answers (1)

p.s.w.g
p.s.w.g

Reputation: 149020

If you happen to have an association from LocationPrizes -> WonPrizes you can just do this:

var locationPrizes = 
    from l in context.Locations
    select new
    {
        l.LocationId,
        l.LocationName,
        Prizes = 
            from o in l.LocationPrizes 
            select new
            {
                o.PrizeId,
                o.PrizeQuantity,
                o.Prize.PrizeJson
                WonPrizes = o.WonPrizes.Count();
            }
    };

If not, this will work too (works for me with the minor edits below):

var locationPrizes = 
    from l in context.Locations
    select new
    {
        l.LocationId,
        l.LocationName,
        Prizes = 
            from o in l.LocationPrizes 
            select new
            {
                o.PrizeId,
                o.PrizeQuantity,
                o.Prize.PrizeJson
                WonPrizes = 
                    (from w in context.WonPrizes
                     where w.PrizeId == o.PrizeId 
                        && w.LocationId == l.LocationId
                     select w)
                    .Count()
            }
    };

Upvotes: 3

Related Questions