.NET7升级后LINQ表达式无法翻译问题求解

错误原因 #

EF Core 7对LINQ查询的翻译规则比旧版更严格，GroupBy(m => m.RegistrationNumber).Select(m => m.FirstOrDefault())这种写法无法被正确转换为SQL——因为SQL的GROUP BY是聚合操作，不能直接返回组内的整条原始记录，EF Core不再允许这种模糊的客户端隐式评估，所以抛出翻译错误。

可行解决思路 #

方法1：使用窗口函数（推荐，数据库端执行） #

利用SQL的ROW_NUMBER()窗口函数，按RegistrationNumber分组后为每条记录编号，然后取每组编号为1的记录，这是性能最优的方案，完全在数据库端完成逻辑：

DriveListViewModel model = new DriveListViewModel()
{
    Drive = await _db.Drive
        .Where(m => m.StatusId is 5 or 1010 or 1012)
        .Where(m => m.LoadingComplete == null)
        .Where(m => !m.UnitCode.Contains(excludeString))
        .Where(m => !m.UnitCode.Contains(excludeString2))
        // 用窗口函数分组并编号，排序规则决定组内取哪条作为第一条
        .Select(m => new 
        {
            Drive = m,
            RowNum = EF.Functions.RowNumber()
                .Over(partitionBy: m.RegistrationNumber)
                .OrderBy(m => m.DriverToLoad)
        })
        .Where(x => x.RowNum == 1)
        .Select(x => x.Drive)
        .Include(s => s.DriveStatus)
        .Include(d => d.Location)
        .Include(f => f.Item)
        .OrderBy(m => m.DriverToLoad)
        .ToListAsync(),
    machineryList = await _db.Machinery.ToListAsync(),
    cleaningList = await _db.Cleaning.ToListAsync(),
};

方法2：先取唯一RegistrationNumber再关联查询 #

先查询出所有符合条件的唯一RegistrationNumber，再通过这些编号查询对应的Drive记录，适合数据量较小的场景：

// 先获取符合条件的唯一RegistrationNumber集合
var uniqueRegistrationNumbers = await _db.Drive
    .Where(m => m.StatusId is 5 or 1010 or 1012)
    .Where(m => m.LoadingComplete == null)
    .Where(m => !m.UnitCode.Contains(excludeString))
    .Where(m => !m.UnitCode.Contains(excludeString2))
    .Select(m => m.RegistrationNumber)
    .Distinct()
    .ToListAsync();

// 查询每个RegistrationNumber对应的目标记录
DriveListViewModel model = new DriveListViewModel()
{
    Drive = await _db.Drive
        .Where(m => uniqueRegistrationNumbers.Contains(m.RegistrationNumber))
        .Where(m => m.StatusId is 5 or 1010 or 1012)
        .Where(m => m.LoadingComplete == null)
        .Where(m => !m.UnitCode.Contains(excludeString))
        .Where(m => !m.UnitCode.Contains(excludeString2))
        .Include(s => s.DriveStatus)
        .Include(d => d.Location)
        .Include(f => f.Item)
        .GroupBy(m => m.RegistrationNumber)
        .Select(g => g.OrderBy(m => m.DriverToLoad).FirstOrDefault())
        .OrderBy(m => m.DriverToLoad)
        .ToListAsync(),
    machineryList = await _db.Machinery.ToListAsync(),
    cleaningList = await _db.Cleaning.ToListAsync(),
};

方法3：客户端评估（仅适合小数据量） #

如果数据量不大，可以先把符合条件的记录拉到内存，再在客户端分组去重，明确开启客户端评估：

DriveListViewModel model = new DriveListViewModel()
{
    Drive = await _db.Drive
        .Where(m => m.StatusId is 5 or 1010 or 1012)
        .Where(m => m.LoadingComplete == null)
        .Where(m => !m.UnitCode.Contains(excludeString))
        .Where(m => !m.UnitCode.Contains(excludeString2))
        .Include(s => s.DriveStatus)
        .Include(d => d.Location)
        .Include(f => f.Item)
        .AsEnumerable() // 切换到客户端内存执行后续逻辑
        .GroupBy(m => m.RegistrationNumber)
        .Select(m => m.OrderBy(x => x.DriverToLoad).FirstOrDefault())
        .OrderBy(m => m.DriverToLoad)
        .ToListAsync(),
    machineryList = await _db.Machinery.ToListAsync(),
    cleaningList = await _db.Cleaning.ToListAsync(),
};

额外优化建议 #

1. 原代码中两次查询UserGroup可以合并，减少数据库访问次数：

var excludedGroups = await _db.UserGroup
    .Where(q => q.UserName == currUserName && q.IsActive == false)
    .Where(q => q.WorkGroupName is "BB" or "TS")
    .Select(q => q.WorkGroupName)
    .ToListAsync();

string excludeString = excludedGroups.Contains("BB") ? "BB" : "XX";
string excludeString2 = excludedGroups.Contains("TS") ? "TS" : "XX";

2. 可以合并多个Where条件，简化代码结构：

.Where(m => (m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012) 
            && m.LoadingComplete == null 
            && !m.UnitCode.Contains(excludeString) 
            && !m.UnitCode.Contains(excludeString2))

内容的提问来源于stack exchange，提问作者René Wester