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

如何使ef有效地调用聚合函数?

linq-to-entities linq entity-framework sql-server c#

Jez · 技术社区 · 7 年前

我正在尝试编写Linq to Entities查询,该查询将 ICollection 我的主对象的导航属性,并将一些元数据附加到它们中的每一个,这是通过将每个元数据连接到另一个DB表并使用聚合函数来确定的。所以主要对象是这样的:

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 LastUsedDate
FROM
    Room rm
LEFT OUTER JOIN
    Conference conf ON rm.Id = conf.RoomId
WHERE
    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查询?

2 回复 | 直到 7 年前

Ivan Stoev 7 年前

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

IQueryable plan.Rooms IEnumerable context.Conferences

IN IEnumerable<Guid> Contains

null Max (DateTime?)conf.EndTime conf

Gert Arnold 7 年前

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