代码之家 › 专栏 › 技术社区 › Suamere

EF:Include with where子句,+SubIncludes

where-clause include entity-framework c#

Suamere · 技术社区 · 6 年前

以下是问题的后续: Include with where clause . 这个问题是想找出所有醒着的乘客

var result = Context.Busses.Where(x => x.IsDriving)
    .Include(x => x.Passengers);

如果没有关于乘客的WHERE条款,也很容易将乘客的子关系包括在内,例如:

var result = Context.Busses.Where(x => x.IsDriving)
    .Include(x => x.Passengers.CarryOns)
    .Include(x => x.Passengers.Luggage);

这个回答

var result = Context.Busses.Where(x => x.IsDriving)
    .Select(bus => new {bus, Passengers = bus.Passengers.Where(x => x.Awake)})
    .AsEnumerable().Select(x => x.bus).ToList();

Include 在这一点上。我试着这样做,但是第一部分被第二部分覆盖了:

var bussesQuery = Context.Busses.Where(x => x.IsDriving)
    .Include(x => x.Passengers.CarryOns)
    .Include(x => x.Passengers.Luggage);
// var check = bussesQuery.ToList(); 
// The Sub-Relationship data is included here, but the Passengers are not filtered.
var result = bussesQuery
    .Select(bus => new {bus, Passengers = bus.Passengers.Where(x => x.Awake)})
    .AsEnumerable().Select(x => x.bus).ToList();
// The Sub-Relationship data is missing, but the Passengers are filtered

2 回复 | 直到 6 年前

Shadow 6 年前

通过查看您以前的查询,我看到您从N+1的极端到单个查询的极端。你以前有很多疑问,现在你想有一个,但请考虑下引擎盖下发生了什么。为了获取数据,实体框架需要交叉连接所有实体,因此对于每个包含的实体,您会在结果中获得额外的列,并且结果与该包含交叉连接。

假设你有5辆正在行驶的公共汽车,30个醒着的乘客和15个行李,结果你得到了公共汽车x行李x乘客=2250个记录,每个记录包含乘客和行李数据。如果您单独查询乘客和行李,您的记录将少得多(5*30+5*15=225),每个实体将提取一次。

做一个会返回所有内容的大查询并不是一个好主意——它速度较慢,维护起来更困难,也不值得花时间。只需查询醒着的乘客,然后查询行李。

Suamere 6 年前

要获得答案,请向下滚动到“答案”部分。

免责声明:

从旁白开始:查看我的最终请求的原始SQL如下所示:

SELECT * FROM [Busses] [bus]
LEFT JOIN [Passengers] [passenger] ON [passenger].[BusID] = [bus].[BusID] AND [passenger].[Awake] <> 1
    LEFT JOIN [CarryOns] [carryOn] ON [carryOn].[PassengerID] = [passenger].[PassengerID]
    LEFT JOIN [Luggages] [luggage] ON [luggage].[PassengerID] = [passenger].[PassengerID]
WHERE [bus].[IsDriving] = 1

当然,如果EF要为这些结果生成一些东西,就需要嵌套和关键字段知道如何映射它们。没什么大不了的。

var busses = context.Set<BusEntity>().Where(x => x.IsDriving);
var passengers = context.Set<PassengerEntity>().Where(x => x.Awake);
var carryOns = context.Set<CarryOnEntity>();
var luggages = context.Set<LuggageEntity>();

var passengerJoins = passengers.GroupJoin(
        carryOns,
        x => x.PassengerID,
        y => y.PassengerID,
        (x, y) => new { Passenger = x, CarryOns = y }
    )
    .SelectMany(
        x => x.CarryOns.DefaultIfEmpty(),
        (x, y) => new { Passenger = x.Passenger, CarryOns = x.CarryOns }
    ).GroupJoin(
        luggages,
        x => x.Passenger.PassengerID,
        y => y.PassengerID,
        (x, y) => new { Passenger = x.Passenger, CarryOns = x.CarryOns, Luggages = y }
    )
    .SelectMany(
        x => x.Luggages.DefaultIfEmpty(),
        (x, y) => new { Passenger = x.Passenger, CarryOns = x.CarryOns, Luggages = x.Luggages }
    );

var bussesToPassengers = busses.GroupJoin(
        passengerJoins,
        x => x.BusID,
        y => y.Passenger.BusID,
        (x, y) => new { Bus = x, Passengers = y }
    )
    .SelectMany(
        x => x.Passengers.DefaultIfEmpty(),
        (x, y) => new { Bus = x.Bus, Passengers = x.Passengers }
    )
    .GroupBy(x => x.Bus);

var rez = bussesToPassengers.ToList()
    .Select(x => x.First().Bus)
    .ToList();

我并不抱怨EF生成的SQL,但是单个SQL语句只有几百行。我对它进行了黑客攻击,删除了SELECT列,修改了一些ID以匹配这个问题,它是这样的:

SELECT *
FROM ( SELECT *
    FROM   (SELECT *
        FROM ( SELECT DISTINCT *
            FROM  [dbo].[Bus] AS [Extent1]
            LEFT OUTER JOIN  (SELECT *
                FROM    [dbo].[Passenger] AS [Extent2]
                LEFT OUTER JOIN [dbo].[CarryOns] AS [Extent3] ON [Extent2].[PassengerId] = [Extent3].[PassengerId]
                LEFT OUTER JOIN [dbo].[Luggages] AS [Extent4] ON [Extent2].[PassengerId] = [Extent4].[PassengerId]
            WHERE [Extent1].[IsDriving] = 1
        )  AS [Distinct1] ) AS [Project2]
    OUTER APPLY  (SELECT *
        FROM   (SELECT *
            FROM  [dbo].[Bus] AS [Extent6]
            LEFT OUTER JOIN  (SELECT *
                FROM    [dbo].[Passenger] AS [Extent7]
                LEFT OUTER JOIN [dbo].[CarryOns] AS [Extent8] ON [Extent7].[PassengerId] = [Extent8].[PassengerId]
                LEFT OUTER JOIN [dbo].[Luggages] AS [Extent9] ON [Extent7].[PassengerId] = [Extent9].[PassengerId]
            WHERE ([Extent6].[IsDriving] = 1) AND ([Project2].[BusId] = [Extent6].[BusId]) ) AS [Project3]
        OUTER APPLY  (SELECT *
            FROM     [dbo].[Passenger] AS [Extent11]
            LEFT OUTER JOIN [dbo].[CarryOns] AS [Extent12] ON [Extent11].[PassengerId] = [Extent12].[PassengerId]
            LEFT OUTER JOIN [dbo].[Luggages] AS [Extent13] ON [Extent11].[PassengerId] = [Extent13].[PassengerId]
            LEFT OUTER JOIN [dbo].[CarryOns] AS [Extent15] ON [Extent11].[PassengerId] = [Extent15].[PassengerId]
            WHERE ([Extent11].[IsAwake] = 1) AND ([Project3].[BusId] = [Extent11].[BusId])
        UNION ALL
            SELECT *
            FROM     [dbo].[Passenger] AS [Extent16]
            LEFT OUTER JOIN [dbo].[CarryOns] AS [Extent17] ON [Extent16].[PassengerId] = [Extent17].[PassengerId]
            LEFT OUTER JOIN [dbo].[Luggages] AS [Extent18] ON [Extent16].[PassengerId] = [Extent18].[PassengerId]
            WHERE ([Extent16].[IsAwake] = 1) AND ([Project3].[BusId] = [Extent16].[BusId])
)  AS [Project7]
ORDER BY ........................

对于我的个人测试数据,我的手写SQL查询返回54行,EF生成的查询返回大约30000行。因此,如果您只考虑数据的在线传输时间的增加,这是不可接受的。

答案部分

最好的办法是执行多个查询。我就是这样做的:

var bus = context.Set<BusEntity>().Where(x => x.IsDriving).ToList();
var busIDs = bus.Select(x => x.BusID).ToList();
var passengers = context.Set<PassengerEntity>().Where(x => x.IsAwake && busIDs.Contains(x.BusID)).ToList();
var passengerIDs = passengers.Select(x => x.PassengerID).ToList();
var carryOns = context.Set<CarryOnEntity>().Where(x => passengerIDs.Contains(x.PassengerID)).ToList();
var luggages = context.Set<LuggageEntity>().Where(x => passengerIDs.Contains(x.PassengerID)).ToList();
passengers.ForEach(x => {
    x.CarryOns = carryOns.Where(y => y.PassengerID == x.PassengerID).ToList();
    x.Luggages = luggages.Where(y => y.PassengerID == x.PassengerID).ToList();
});
bus.ForEach(x => x.Passengers = passengers.Where(y => y.BusID == x.BusID).ToList());

这产生了4个电话。SQL总共有大约40行。我对它进行了黑客攻击,删除了SELECT列,修改了一些ID以匹配这个问题,它是这样的:

SELECT * FROM [dbo].[Busses] AS [Extent1]
    WHERE [Extent1].[IsDriving] = 1

SELECT * FROM [dbo].[Passengers] AS [Extent1]
    WHERE ([Extent1].[Awake] = 1) AND ([Extent1].[BusID] IN (......................))

SELECT * FROM [dbo].[CarryOns] AS [Extent1]
    WHERE [Extent1].[PassengerID] IN (......................)

SELECT * FROM [dbo].[Luggages] AS [Extent1]
    WHERE [Extent1].[PassengerID] IN (......................)

我没有计时,但每当我停在这个答案代码上方的断点上,然后将F5移到结果的另一边时,它都是即时的。在我的研究中,当我对一个电话做同样的事情时,它花了一秒钟甚至更多的时间,明显的滞后。