如何在LINQ中高效实现可复用的T-SQL Not IN关联查询

系统涉及Products、Dealers两类核心业务数据集合，当前已完成基础LINQ查询编写，从数据上下文的ProductList集中筛选满足指定日期条件的产品数据，现有代码如下：

var productList = _context.ProductList
    .Where(x => x.Date == new DateTime(2021, 02, 01)).ToList();

注：原代码存在lambda符号笔误，原写法x.Date=>new DateTime(2021,02,01)为表达式方向错误，实际逻辑为日期等值匹配

待实现的约束逻辑 #

需要为上述查询补充完全等效的T-SQL约束逻辑，目标T-SQL约束片段为：

DealerCode Not IN(Select DealerCode from Dealers D With (nolock) Where D.ProductId = P.ProductId)

对应的完整参考T-SQL查询为：

Select * From ProductList P 
Where Date='2021-02-01' 
AND DealerCode Not IN(Select DealerCode from Dealers D With (nolock) Where D.ProductId=P.ProductId)

实现约束 #

• 曾尝试通过foreach内存遍历方式实现该约束，未达成预期效果
• 两类数据集合体量较大，该约束逻辑会被复用到8个不同的查询场景
• 不接受!x.DealerCode.Contains(...)这类内存集合判断的实现方式，需要数据库端高效执行、可复用的方案

等效LINQ实现方案 #

核心逻辑匹配 #

LINQ to Entities/EF Core 原生支持关联子查询翻译，可通过!Any()表述关联不存在的判断，生成的SQL完全匹配目标T-SQL逻辑，所有过滤逻辑下推到数据库执行，不会提前加载全量数据到内存。
针对nolock查询提示，可通过实体全局配置实现，无需在每个单独查询中重复编写锁提示逻辑。

可复用的扩展封装 #

由于逻辑需要跨多个查询复用，可将过滤逻辑封装为IQueryable<Product>的扩展方法，避免重复编写判断代码：

public static IQueryable<Product> FilterDealerNotInMatched(this IQueryable<Product> query, AppDbContext context)
{
    return query.Where(p => !context.Dealers
        .Where(d => d.ProductId == p.ProductId)
        .Select(d => d.DealerCode)
        .Contains(p.DealerCode)
    );
}

查询调用方式 #

var productList = _context.ProductList
    .Where(x => x.Date == new DateTime(2021, 02, 01))
    // 直接调用复用的过滤逻辑，自动生成等效子查询SQL
    .FilterDealerNotInMatched(_context)
    .ToList();

逻辑说明：上述写法最终生成的SQL会自动将Dealers子查询关联到外部ProductList别名P，完全匹配目标DealerCode Not IN(关联子查询)的逻辑；Dealers表的nolock提示可通过EF Core 的模型全局锁提示配置实现，不侵入业务查询代码。

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