.NET 6中带关联统计的TSQL转LINQ时的翻译异常问题

业务场景 #

我们要从Customer表中筛选出同时满足以下两个条件的客户：

• 启用了AutoAssignPaRequests功能
• 关联了至少一条有效的AutoAssignmentRule（比如规则处于激活状态）

原TSQL逻辑参考 #

假设你原本的SQL是通过左关联+分组筛选实现需求的：

SELECT c.*
FROM Customer c
LEFT JOIN AutoAssignmentRule ar 
  ON c.Id = ar.CustomerId AND ar.IsActive = 1
WHERE c.AutoAssignPaRequests = 1
GROUP BY c.Id, c.AutoAssignPaRequests -- 需包含Customer表的其他字段
HAVING COUNT(ar.Id) > 0

问题根源 #

LINQPad默认使用的查询提供器（如LINQ to SQL）对语法的容忍度更高，而ASP.NET 6中搭配的EF Core 6对LINQ表达式的翻译规则更严格——你之前在LINQPad中写的代码可能用到了EF Core无法转换成SQL的分组操作，因此触发了System.InvalidOperationException。

兼容EF Core 6的可行LINQ写法 #

推荐写法：用Any()替代分组（性能更优） #

EF Core对Any()的翻译支持非常完善，直接对应SQL的EXISTS查询，不仅能避免翻译异常，查询性能也比分组统计更好：

var validCustomers = await _context.Customers
    .Where(c => c.AutoAssignPaRequests)
    .Where(c => _context.AutoAssignmentRules
        .Any(ar => ar.CustomerId == c.Id && ar.IsActive))
    .ToListAsync();

若坚持使用分组逻辑 #

需要调整写法，确保所有操作都能被EF Core翻译成SQL，避免触发客户端评估：

var validCustomers = await _context.Customers
    .Join(
        _context.AutoAssignmentRules.Where(ar => ar.IsActive),
        customer => customer.Id,
        rule => rule.CustomerId,
        (customer, rule) => new { Customer = customer, Rule = rule }
    )
    .Where(x => x.Customer.AutoAssignPaRequests)
    .GroupBy(x => x.Customer.Id)
    .Select(g => g.First().Customer)
    .ToListAsync();

这里先过滤无效规则再做关联，分组后仅取每个客户的第一个实例，规避了原写法中可能存在的非分组字段引用问题。

贴近原SQL左关联逻辑的写法 #

如果想保留原SQL的左连思路，以下写法也能被EF Core正常翻译：

var validCustomers = await _context.Customers
    .Where(c => c.AutoAssignPaRequests)
    .Select(c => new 
    {
        Customer = c,
        HasValidRule = _context.AutoAssignmentRules
            .Any(ar => ar.CustomerId == c.Id && ar.IsActive)
    })
    .Where(x => x.HasValidRule)
    .Select(x => x.Customer)
    .ToListAsync();

避坑提示 #

• 不要在LINQ中使用EF Core无法识别的方法（如自定义静态方法、复杂聚合操作），这类代码会触发客户端评估，直接抛出翻译异常
• 优先使用Any()替代分组统计，既能减少踩坑概率，也能提升查询性能
• 确认ASP.NET 6项目使用的是EF Core 6版本，版本不匹配可能导致奇怪的翻译问题

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