Entity framework generated SQL is different from 2.2 to 7

Question

Entity framework generated SQL is different from 2.2 to 7

Alexander Angelo 6

I recently upgraded a .NET core 2.1 web app to .NET 7 with upgrading EntityFrameworkCore to 7.0.1. 

a statement that use "Include" is now producing Sql queries with LEFT JOIN whereas with 2.1, they produce a mix of LEFT and INNER joins.

 _dbContext.T1
   .Include(t11 => t11.T11)
      .ThenInclude(t12 => t12.T12)
   .Include(p => p.P)
   .AsNoTracking()

produces

2.1 :

FROM T12
  INNER JOIN (
    SELECT DISTINCT ..................
    FROM T11
      INNER JOIN (
        SELECT ......
        FROM T1 
        LEFT JOIN P .........................

7 : 

FROM T1
LEFT JOIN P
 LEFT JOIN (
    SELECT ------
    FROM T11 
      LEFT JOIN T12
      -----

Wonder why the latest EFCore 7 produces a different SQL query to 2.1


I was expecting the generated SQL to be the same in both version of EFCore

Wenbin Geng 741 Microsoft External Staff

Hi, I used EF Core7.0.1 and tested the query statement you provided. The entity class used is:

    public class T1
    {
        public int id {  get; set; }

        public t11 T11 { get; set; }

        public p P { get; set; }
    }

    public class t11
    {
        public int id { get; set; }

        public t12 T12 { get; set; }
    }

    public class t12
    {
        public int id { get; set; }
    }

    public class p
    {
        public int id { get; set; }
    }

The generated sql is:

SELECT [t].[id], [t].[Pid], [t].[T11id], [t0].[id], [t0].[T12id], [t1].[id], [p].[id]
FROM [T1] AS [t]
INNER JOIN [t11] AS [t0] ON [t].[T11id] = [t0].[id]
INNER JOIN [t12] AS [t1] ON [t0].[T12id] = [t1].[id]
INNER JOIN [p] AS [p] ON [t].[Pid] = [p].[id]

This seems to be a discrepancy with the information you provided, so you will need to provide more information. For example, your entity class information. This will help reproduce your issue.

Your answer

Wenbin Geng 741 Reputation points Microsoft External Staff

2023-10-09T09:58:49.74+00:00

Hi, I used EF Core7.0.1 and tested the query statement you provided. The entity class used is:

public class T1 { public int id { get; set; } public t11 T11 { get; set; } public p P { get; set; } } public class t11 { public int id { get; set; } public t12 T12 { get; set; } } public class t12 { public int id { get; set; } } public class p { public int id { get; set; } }

The generated sql is:

SELECT [t].[id], [t].[Pid], [t].[T11id], [t0].[id], [t0].[T12id], [t1].[id], [p].[id] FROM [T1] AS [t] INNER JOIN [t11] AS [t0] ON [t].[T11id] = [t0].[id] INNER JOIN [t12] AS [t1] ON [t0].[T12id] = [t1].[id] INNER JOIN [p] AS [p] ON [t].[Pid] = [p].[id]

This seems to be a discrepancy with the information you provided, so you will need to provide more information. For example, your entity class information. This will help reproduce your issue.

Share via

Entity framework generated SQL is different from 2.2 to 7

Your answer