EF Core 6实现带TOP 1的OUTER APPLY及LEFT JOIN的查询方法

针对你需要关联MarketingData与MaterialDetail，且Material表同一物料号存在多条记录的场景，用SelectMany配合Take(1)的写法可以让EF Core生成带OUTER APPLY取TOP1的高效SQL，同时完成与MaterialDetail的LEFT JOIN。

前提假设实体结构 #

基于你的表结构给出合理的实体定义（若和实际结构有出入，调整关联字段即可）：

public class MarketingData
{
    public int Id { get; set; }
    public string MaterialNo { get; set; }
    // 其他业务字段
}

public class Material
{
    public int MaterialId { get; set; }
    public string MaterialNo { get; set; }
    // 用于排序取目标记录的字段（示例为创建时间）
    public DateTime CreateTime { get; set; }
    // 其他业务字段
}

public class MaterialDetail
{
    public int DetailId { get; set; }
    public int MaterialId { get; set; }
    public string DetailInfo { get; set; }
    // 其他业务字段
}

方法式查询写法 #

var query = _dbContext.MarketingData
    // 关联Material，取每个MaterialNo对应的第一条记录（按CreateTime倒序取最新）
    .SelectMany(md => _dbContext.Material
        .Where(m => m.MaterialNo == md.MaterialNo)
        .OrderByDescending(m => m.CreateTime)
        .Take(1), 
        (md, m) => new { MarketingData = md, Material = m })
    // LEFT JOIN MaterialDetail
    .LeftJoin(_dbContext.MaterialDetail,
        joinKey => joinKey.Material.MaterialId,
        detail => detail.MaterialId,
        (joinResult, detail) => new 
        {
            // 投影你需要的业务字段
            MarketingDataId = joinResult.MarketingData.Id,
            MaterialNo = joinResult.MarketingData.MaterialNo,
            MaterialDetailInfo = detail?.DetailInfo,
            // 按需添加其他字段
        });

生成的SQL说明 #

EF Core 6会将上述查询转换成类似以下的SQL，核心逻辑就是通过OUTER APPLY逐行获取MarketingData对应物料号的第一条Material记录，再执行LEFT JOIN关联MaterialDetail：

SELECT 
    [md].[Id] AS [MarketingDataId],
    [md].[MaterialNo],
    [d].[DetailInfo] AS [MaterialDetailInfo]
FROM [MarketingData] AS [md]
OUTER APPLY (
    SELECT TOP(1) [m].[MaterialId], [m].[MaterialNo], [m].[CreateTime]
    FROM [Material] AS [m]
    WHERE [m].[MaterialNo] = [md].[MaterialNo]
    ORDER BY [m].[CreateTime] DESC
) AS [m]
LEFT JOIN [MaterialDetail] AS [d] ON [m].[MaterialId] = [d].[MaterialId]

写法优势 #

• 避免了先查询全量Material再过滤的低效逻辑，OUTER APPLY会针对MarketingData的每条记录精准获取对应物料号的单条目标记录
• 相比在投影中使用FirstOrDefault()（易生成嵌套子查询或多次查询），该写法会被EF Core优化为单次高效查询

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