如何让EF高效调用聚合函数？

Question

我正在尝试编写一个 LINQ-to-entities 查询，它将获取ICollection我的主要对象的导航属性，并将一些元数据附加到每个对象，这是通过将每个对象连接到另一个数据库表并使用聚合函数来确定的。所以主要对象是这样的：public class Plan{    ...    public virtual ICollection<Room> Rooms { get; set; }}我的查询是这样的：var roomData = (    from rm in plan.Rooms    join conf in context.Conferences on rm.Id equals conf.RoomId into cjConf    select new {        RoomId = rm.Id,        LastUsedDate = cjConf.Count() == 0 ? (DateTime?)null : cjConf.Max(conf => conf.EndTime)    }).ToList();我想要的是生成一些高效的 SQL，使用聚合函数MAX来计算LastUsedDate，如下所示：SELECT    rm.Id, MAX(conf.EndTime) AS LastUsedDateFROM    Room rmLEFT OUTER JOIN    Conference conf ON rm.Id = conf.RoomIdWHERE    rm.Id IN ('a967c9ce-5608-40d0-a586-e3297135d847', '2dd6a82d-3e76-4441-9a40-133663343d2b', 'bb302bdb-6db6-4470-a24c-f1546d3e6191')GROUP BY    rm.id但是当我分析 SQL Server 时，它显示了来自 EF 的这个查询：SELECT     [Extent1].[Id] AS [Id],     [Extent1].[RoomId] AS [RoomId],     [Extent1].[ProviderId] AS [ProviderId],     [Extent1].[StartTime] AS [StartTime],     [Extent1].[EndTime] AS [EndTime],     [Extent1].[Duration] AS [Duration],     [Extent1].[ParticipantCount] AS [ParticipantCount],     [Extent1].[Name] AS [Name],     [Extent1].[ServiceType] AS [ServiceType],     [Extent1].[Tag] AS [Tag],     [Extent1].[InstantMessageCount] AS [InstantMessageCount]    FROM [dbo].[Conference] AS [Extent1]所以它从内存中选择所有内容Conference并Max()在内存中进行计算，这是非常低效的。如何让 EF 使用聚合函数生成正确的 SQL 查询？

Answer 1

等效的 LINQ to Entities 查询，它密切转换为您所追求的 SQL 查询，如下所示：

var roomIds = plan.Rooms.Select(rm => rm.Id);

var query =

    from rm in context.Rooms

    join conf in context.Conferences on rm.Id equals conf.RoomId

    into rmConf from rm in rmConf.DefaultIfEmpty() // left join

    where roomIds.Contains(rm.Id)

    group conf by rm.Id into g

    select new

    {

        RoomId = g.Key,

        LastUsedDate = g.Max(conf => (DateTime?)conf.EndTime)

    };

诀窍是从 EF 开始查询IQueryable，从而允许它完全转换为 SQL，而不是从plan.Rooms有问题的查询中开始，这IEnumerable使得整个查询在内存中执行（context.Conferences被视为IEnumerable并导致将整个表加载到记忆）。

SQLIN子句是通过in memoryIEnumerable<Guid>和Containsmethod实现的。

最后，无需检查计数。SQL 自然会处理nulls，您只需要确保调用可为空的Max重载，这是通过强制转换实现的(DateTime?)conf.EndTime。不需要像在 LINQ to Objects 中那样检查conf，null因为 LINQ to Entities/SQL 自然也会处理（只要接收器变量可以为空）。

Answer 2

由于plan.Rooms没有IQueryable附加查询提供程序，连接语句被编译为Enumarable.Join. 这意味着在其他运算符应用于它之前，它context.Conferences被隐式转换为IEumerable并且其内容被拉入内存。

您可以通过不使用来解决此问题join：

var roomIds = plan.Rooms.Select(r => r.Id).ToList();

var maxPerRoom = context.Conferences

    .Where(conf => roomIds.Contains(conf.RoomId))

    .GroupBy(conf => conf.RoomId)

    .Select(g => new

    {

        RoomId = g.Key,

        LastUsedDate = g.Select(conf => conf.EndTime)

            .DefaultIfEmpty()

            .Max()

    }

).ToList();

var roomData = (

    from rm in plan.Rooms

    join mx in maxPerRoom on rm.Id equals mx.RoomId

    select new 

    {

        RoomId = rm.Id,

        LastUsedDate = mx.LastUsedDate

    }

).ToList();

第一步LastUsedDate从上下文收集数据，然后与plan.Rooms内存中的集合连接。如果您对返回/显示房间 ID 以外的任何其他内容不感兴趣，则甚至不需要最后一步，但这取决于您。