Entity Framework中LINQ带Count聚合查询慢、原生SQL快的优化问题

你遇到的查询性能差距，本质不是EF把数据拉到内存计算，而是原LINQ写法的逻辑结构，导致EF生成了冗余、执行效率极差的SQL，和你手写的高效执行逻辑完全不对等：

• 原代码中StartingLocation、EndingLocation的取值，是嵌套调用db.Adjustments的独立子查询实现的，每一条结果行都会触发两次独立的Adjustments表扫描排序，1000条结果就会产生2000次重复的表访问，完全没有利用聚合阶段的单遍计算能力。
• 原代码的Group逻辑是先把Assignments和Adjustments做全量Join，再基于Join后的结果分组，比先对Adjustments做独立聚合再关联主表的方式，多了一层不必要的跨表全量匹配步骤，直接拉长了执行计划的生成路径。
• 原代码中Group后的投影字段零散拆分到多层查询结构中，EF没法自动优化生成Min/Max的高效聚合逻辑，最终输出的SQL冗长复杂，执行效率自然差了10倍以上。

完全对齐你手写SQL的逻辑结构，先对Adjustments做单遍预聚合，再和主表关联投影，避免嵌套子查询和冗余Join：

// 预聚合Adjustments表的统计值，和手写SQL的子查询逻辑完全一致
var adjustmentGroupStats = 
    from adj in db.Adjustments
    group adj by adj.AssignmentID into adjGroup
    select new
    {
        AssignmentID = adjGroup.Key,
        StartingLocation = adjGroup.Min(x => x.LocationID),
        EndingLocation = adjGroup.Max(x => x.LocationID),
        TotalLocations = adjGroup.Count(x => x.LocationID != null),
        TotalLicensePlates = adjGroup.Count(x => x.ExpectedLicensePlateID != null),
        TotalAdjCompleted = adjGroup.Count(x => x.Status == "C")
    };

// 关联主表并直接投影结果，无嵌套查询
var assignmentResultQuery = 
    from assign in db.Assignments
    join stat in adjustmentGroupStats on assign.ID equals stat.AssignmentID
    // 保留原有的过滤逻辑
    where StartDate.HasValue 
        ? DbFunctions.TruncateTime(assign.CreatedDate) == StartDate 
        : assign.IsArchived == false
    select new AssignmentTotals
    {
        ID = assign.ID,
        UserName = assign.UserName,
        Status = assign.Status,
        ImportedDate = DbFunctions.TruncateTime(assign.CreatedDate),
        StartingLocation = stat.StartingLocation,
        EndingLocation = stat.EndingLocation,
        TotalLocations = stat.TotalLocations,
        TotalLicensePlates = stat.TotalLicensePlates,
        TotalAdjCompleted = stat.TotalAdjCompleted,
        IsSameUser = currUser == assign.UserName,
        IsArchived = assign.IsArchived
    };

// 组装外层业务对象
var assignmenTotal = new AssignmentUser
{
    IsSupervisor = supervisor,
    AssignmentTotals = assignmentResultQuery
        .OrderBy(x => x.ID)
        .ToList()
};

通过SQL Profiler观测优化后EF发送的SQL语句，结构会和你手写的高效SQL完全对齐：

• 先对Adjustments表按AssignmentID分组，单遍扫描计算最小/最大LocationID、各类统计计数，没有重复的子查询扫描
• 聚合结果集再和Assignments主表做内连接，应用原有过滤条件
• 整个执行路径没有冗余的相关子查询，性能会从原来的10秒级别下降到1秒以内，和手写SQL的执行效率持平。

内容的提问来源于stack exchange，提问作者causita