如何将指定多表关联SQL转换为Entity Framework的Lambda表达式

基础链式Join写法（无需配置导航属性） #

该写法不依赖实体间的导航关系配置，直接对应原始SQL的JOIN逻辑：

// 固定匹配参数
var targetErpId = Guid.Parse("5221be90-3d97-46fa-9562-4ff9d910c14d");
var targetMerchantId = Guid.Parse("5fb34a5d-e7ee-49a8-a8e3-3df9e9027c39");

var queryResult = dbContext.Extracts
    // 关联ErpExtract表
    .Join(
        dbContext.ErpExtracts.Where(ee => ee.ErpId == targetErpId),
        extract => extract.Id,
        erpExtract => erpExtract.ExtractId,
        (extract, erpExtract) => new { extract, erpExtract }
    )
    // 关联ExtractSql表
    .Join(
        dbContext.ExtractSqls,
        preJoin => preJoin.erpExtract.Id,
        extractSql => extractSql.ErpExtractId,
        (preJoin, extractSql) => new { preJoin.extract, extractSql }
    )
    // 关联ExtractMerchant表
    .Join(
        dbContext.ExtractMerchants.Where(em => em.MerchantId == targetMerchantId),
        preJoin => preJoin.extractSql.Id,
        extractMerchant => extractMerchant.ExtractSqlId,
        (preJoin, extractMerchant) => new 
        {
            Id = preJoin.extract.Id,
            Name = preJoin.extract.Name,
            Sql = preJoin.extractSql.Sql,
            Version = preJoin.extractSql.Version
        }
    )
    .ToList();

提示：如果你的实体Id字段为字符串类型，直接去掉Guid.Parse方法，直接传入字符串格式的Id即可。

导航属性优化写法（推荐） #

如果你的实体类已经配置了正确的关联导航属性，可使用更简洁的写法，EF会自动生成等价的JOIN语句：

var targetErpId = Guid.Parse("5221be90-3d97-46fa-9562-4ff9d910c14d");
var targetMerchantId = Guid.Parse("5fb34a5d-e7ee-49a8-a8e3-3df9e9027c39");

var queryResult = dbContext.Extracts
    .Where(e => e.ErpExtracts.Any(ee => 
        ee.ErpId == targetErpId 
        && ee.ExtractSql.ExtractMerchants.Any(em => em.MerchantId == targetMerchantId)))
    .Select(e => new 
    {
        Id = e.Id,
        Name = e.Name,
        Sql = e.ErpExtracts.First(ee => ee.ErpId == targetErpId).ExtractSql.Sql,
        Version = e.ErpExtracts.First(ee => ee.ErpId == targetErpId).ExtractSql.Version
    })
    .ToList();

内容的提问来源于stack exchange，提问作者Rodrigo Lopes Fávaro