EF Core 6中使用Fluent API实现JOIN多ON条件的问题

目标原生SQL #

SELECT *
FROM [table] AS [t]
LEFT JOIN [table0] AS [t0] ON [t].[field1] = [t0].[field1]
LEFT JOIN [table1] AS [t1] ON [t].[field2] = [t1].[field2] AND [t1].[field3] = [t0].[field3]
LEFT JOIN [table2] AS [t2] ON [t1].[field4] = [t2].[field4]
WHERE ([t0].[field5] = GUID) 

当前实现代码 #

var query = db.Table
            .Include((e) => e.Table0Navigation)
            .Include((e) => e.Table1Navigation)
            .Include((e) => e.Table1Navigation.Table2Navigation)
            .Where((e) => e.Table0Navigation.GUID == request.GUID)

实体模型 #

Table #

[Key]
public int field0 { get; set; }

[ForeignKey("field1")]
public int field1 { get; set; }

[ForeignKey("field2")]
public string field2 { get; set; } = null!;

public Table1? Table1Navigation { get; set; }

public Table0 Table0Navigation { get; set; } = null!;

Table0 #

[Key]
public int field1 { get; set; }

public int? field3 { get; set; }

public GUID field5 { get; set; }

Table1 #

public int field3 { get; set; }

public string field2 { get; set; } = null!;

public int? field4 { get; set; }

public Table2 Table2Navigation { get; set; } = null!;

Table2 #

public int field4 { get; set; }

当前Fluent API配置（Table1） #

entity.HasKey((e) => new { e.field2 });

entity
    .HasMany((e) => e.TableNavigation) //Table1到Table的导航属性
    .WithOne((e) => e.Table1Navigation) //Table到Table1的导航属性
    .HasForeignKey((e) => e.field2) //对应Table的外键
    .HasPrincipalKey((e) => e.field2) //对应Table1的主键
    .IsRequired(false); //用于实现LEFT JOIN

entity
    .HasMany((e) => e.Table0Navigation) //Table1到Table0的导航属性
    .WithOne((e) => e.Table1Navigation) //Table0到Table1的导航属性
    .HasForeignKey((e) => e.field1) //对应Table0的外键
    .HasPrincipalKey((e) => e.field1) //对应Table1的主键
    .IsRequired(false); //用于实现LEFT JOIN

核心要求 #

• Table的field2有值但Table1无对应记录时，需返回结果，Table1相关字段为NULL
• Table与Table1的关联需同时满足[t].[field2] = [t1].[field2]和[t1].[field3] = [t0].[field3]两个条件

问题现状 #

当前配置仅能实现Table与Table1基于field2的单条件关联，无法生成AND [t1].[field3] = [t0].[field3]的JOIN条件，不符合需求。

解决方案 #

EF Core的常规导航属性关联无法直接处理这种跨表的多条件JOIN，需改用显式Join构建查询，同时保留数据关联：

var query = from t in db.Table
            join t0 in db.Table0 on t.field1 equals t0.field1
            // 多条件LEFT JOIN Table1
            join t1 in db.Table1 
                on new { t.field2, T0Field3 = t0.field3.Value } equals new { t1.field2, T0Field3 = t1.field3 }
                into t1Joined
            from t1 in t1Joined.DefaultIfEmpty()
            // LEFT JOIN Table2
            join t2 in db.Table2 on t1.field4 equals t2.field4 into t2Joined
            from t2 in t2Joined.DefaultIfEmpty()
            where t0.field5 == request.GUID
            select new 
            {
                Table = t,
                Table0Navigation = t0,
                Table1Navigation = t1,
                Table2Navigation = t2
            };

说明 #

1. 使用into和DefaultIfEmpty()实现LEFT JOIN逻辑，确保Table无匹配Table1记录时仍返回结果
2. 通过匿名类传递复合关联条件，同时匹配field2和跨表的field3
3. 若需要直接返回带导航属性的实体对象，可在查询后手动赋值导航属性，或结合Include与显式Join的条件使用

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