用左外连接计数到实体

本文关键字：实体 连接 | 更新日期: 2023-09-27 18:03:12

我在Linq to Entities中理解左外连接计数有困难。

我的查询是:

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
                                            }
                           };

我无法使左外连接部分的计数正常工作。指针吗?

用左外连接计数到实体

如果您恰好有LocationPrizes -> WonPrizes的关联，您可以这样做:

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();
            }
    };

如果没有，这也可以工作(对我来说是下面的小编辑):

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()
            }
    };
相关文章：