Multiple ON conditions in JOIN using Fluent API

Question

A little bit of preface: I am working in EF Core 6 and was trying to create an SQL query using the Fluent API. The raw SQL query that I am trying to achieve is:

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) 

I have the following in EF Core:

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

Here are the models for the schemas (extraneous fields removed)

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; }

The relationships/model in the context I have defined for Table 1's entity is:

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

entity
    .HasMany((e) => e.TableNavigation) //Navigation from Table 1 to Table
    .WithOne((e) => e.Table1Navigation) //Navigation from Table to Table 1
    .HasForeignKey((e) => e.field2) //Represents Table
    .HasPrincipalKey((e) => e.field2) //Represents Table 1
    .IsRequired(false); //Needed for LEFT JOIN 

entity
    .HasMany((e) => e.Table0Navigation) //Represents navigation from Table 1 to Table 0
    .WithOne((e) => e.Table1Navigation) //Represents navigation from Table 0 to Table 1
    .HasForeignKey((e) => e.field1) //Represents Table 0
    .HasPrincipalKey((e) => e.field1) //Represents Table 1
    .IsRequired(false); //Needed for LEFT JOIN

The other tables do not have relationship definitions defined in the model.

• Important to note that it is possible for Table to have a field2 with value but Table 1 does not have a corresponding record that contain field2, but the result should still be returned, just the values normally gotten from the record in Table 1 would be NULL

• field3 is coming from Table 0 (t0) on a join between Table (t) and Table 1 (t1)

The problem that I am facing is that I can never manage to get it to do the join on both conditions, it can do it properly on the field2 but I can never get it to do it on field3

Ive tried pretty much everything but still can't figure out, maybe on of you could help?

Thank you in advance.

Answer 1

I used EFCore 7, but this should work on 6 also.

I used an anonymous type to tell EFCore what columns I need. It makes all the Joins as it feels necessary.

var id = Guid.NewGuid();

var data = ctx.Tables
    .Where((e) => e.Table0Navigation.field5 == id)
    .Select(e => new { 
        e.field0, 
        e.Table0Navigation.field1,
        e.Table1Navigation.field2,
        e.Table1Navigation.Table2Navigation.field4})
    .ToArray();

and I got a query

SELECT [t].[field0], [t0].[field1], [t1].[field2], [t2].[field4]
      FROM [Tables] AS [t]
      INNER JOIN [Table0] AS [t0] ON [t].[Table0Navigationfield1] = [t0].[field1]
      LEFT JOIN [Table1] AS [t1] ON [t].[Table1Navigationfield3] = [t1].[field3]
      LEFT JOIN [Table2] AS [t2] ON [t1].[Table2Navigationfield4] = [t2].[field4]
      WHERE [t0].[field5] = @__id_0

add more columns to the Select as you need them.