EF Core 2.1优化IQueryable生成单SQL，筛选含全部指定产品的公司

核心实现思路 #

你遇到的N+1问题是因为EF Core 2.1对All搭配本地集合的查询翻译支持不完善，会触发客户端求值，导致先拉取公司数据再逐个查询关联产品。你可以通过匹配符合条件的产品数量的逻辑重写查询，这种写法EF Core 2.1可以直接翻译成单条服务端SQL。

优化后的LINQ代码 #

if (selectedProductIds.Count > 0)
{
    int requiredCount = selectedProductIds.Count;
    query = query.Where(x => x.Products.Count(p => selectedProductIds.Contains(p.ProductId)) == requiredCount);
}

原理解释 #

1. 逻辑等价性：因为同一家公司下产品ID唯一，只要该公司的产品中命中selectedProductIds的数量等于选中产品的总数量，就说明该公司拥有全部选中产品，和原All逻辑完全一致。
2. 翻译逻辑：该写法会被EF Core 2.1翻译为带IN条件的关联聚合查询，生成的SQL大致如下：

DECLARE @p0 int = 1, @p1 int = 2, @p2 int = 3, @p3 int = 3;
SELECT [c].*
FROM [Companies] AS [c]
LEFT JOIN [Products] AS [p] ON [c].[CompanyId] = [p].[CompanyId]
WHERE [p].[ProductId] IN (@p0, @p1, @p2)
GROUP BY [c].[CompanyId], [c].[Name], [c].[其他字段]
HAVING COUNT([p].[ProductId]) = @p3

全程仅执行一次SQL查询，完全避免N+1问题，也不需要手动拼接SQL。

扩展补充 #

如果后续业务中公司下可能存在重复产品，只需要调整为去重计数即可：

query = query.Where(x => 
    x.Products.Where(p => selectedProductIds.Contains(p.ProductId))
        .Select(p => p.ProductId)
        .Distinct()
        .Count() == requiredCount
);

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