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.

EF Core deals with entities and properties, not tables and fields. All ORMs try to give the impression of working with in-memory objects instead of tables and rows. Include isn't used to perform JOINs, it's used to eagerly load related objects that aren't included in the Select expression. do not have relationship definitions they should be added then. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 28, 2023 at 14:02
Please edit the question and give meaningful names to the entities and properties. Relations can only be defined in OnModelConfiguring. Include and Select use those relations to generate JOINs. It's unclear what Table 1 does not have a corresponding record that contain field2 means right now. Are you trying to define a relation on a non-primary key field? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 28, 2023 at 14:06
Then how would I go about doing the JOINs? And for the relationship definitions, how would I go about doing a [0,1] relationship? — TheCaeserSalad
– TheCaeserSalad, Commented Sep 28, 2023 at 14:08
@PanagiotisKanavos Regarding the Table 1 does not have a corresponding record that contain field2 it is a bit complex since the specific value of the field that we want to do the JOIN on does NOT necessarily exist in Table 1. So you could have a table t with a record like so: (field0 | field1 | ... | field2) (a | b | | c) It is possible for Table t2 to NOT have a record that contains the same value for field2 as in table 1, i.e in this case, the table t2 does not have a record with field2 value c (field3 | field2 | field4) (d | e | f) — TheCaeserSalad
– TheCaeserSalad, Commented Sep 28, 2023 at 14:19
@siggemannen Yeah, that one is ok, it works fine as a INNER JOIN as well (in the raw SQL) — TheCaeserSalad
– TheCaeserSalad, Commented Sep 28, 2023 at 14:44

Filip Franik · Accepted Answer · 2023-09-28 14:49:00Z

3

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.

edited Sep 28, 2023 at 14:49

answered Sep 28, 2023 at 14:37

Filip Franik

4986 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple ON conditions in JOIN using Fluent API

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related