Efcore: Generate table joins instead of subquery joins

[roji]

For queries with includes, we currently generate joins with a subquery:

SELECT [b].[Id], [b].[Name], [b].[UserId], [t].[Id], [t].[BlogId], [t].[Description], [t].[UserId], [t].[Id0], [t].[Created], [t].[Hash], [t].[IsDeleted], [t].[Modified], [t].[PostId]
FROM [Blog] AS [b]
LEFT JOIN (
    SELECT [p].[Id], [p].[BlogId], [p].[Description], [p].[UserId], [p0].[Id] AS [Id0], [p0].[Created], [p0].[Hash], [p0].[IsDeleted], [p0].[Modified], [p0].[PostId]
    FROM [Post] AS [p]
    LEFT JOIN [PostInstance] AS [p0] ON [p].[Id] = [p0].[PostId]
) AS [t] ON [b].[Id] = [t].[BlogId]
ORDER BY [b].[Id], [t].[Id], [t].[Id0]

We could simplify this to:

SELECT [b].[Id], [b].[Name], [b].[UserId], [t].[Id], [t].[BlogId], [t].[Description], [t].[UserId], [t].[Id0], [t].[Created], [t].[Hash], [t].[IsDeleted], [t].[Modified], [t].[PostId]
FROM [Blog] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
LEFT JOIN [PostInstance] AS [p0] ON [p].[Id] = [p0].[PostId]
ORDER BY [b].[Id], [p].[Id], [t].[Id0]

We should measure the execution perf difference between the above two. Even if there is no (significant) difference, we could still decide to do this for SQL simplicity.

Originally raised in https://github.com/aspnet/EntityFrameworkCore/issues/17455.

[Gwindalmir]

Is there any way for a provider writer to override this?

I'm developing an EF 2.2 provider for an older database, and it doesn't support subqueries in a join clause at all. So currently the generated SQL is invalid.

In my case, I'm just executing the git BuiltInDataTypesBase test:

                var entity = context
                    .Set<StringKeyDataType>()
                    .Include(e => e.Dependents)
                    .Where(e => e.Id == "Gumball!")
                    .ToList().Single();

That generates this SQL statement:

SELECT "e.Dependents"."Id", "e.Dependents"."StringKeyDataTypeId"
FROM "StringForeignKeyDataType" "e.Dependents"
INNER JOIN(
    SELECT "e0"."Id" 
    FROM "StringKeyDataType" "e0"
    WHERE "e0"."Id"=N'Gumball!'
) AS "t" ON "e.Dependents"."StringKeyDataTypeId"="t"."Id"
ORDER BY "t"."Id"

However it is invalid for the particular DB vendor, and it must instead be:

SELECT "e.Dependents"."Id", "e.Dependents"."StringKeyDataTypeId"
FROM "StringForeignKeyDataType" "e.Dependents"
INNER JOIN ("StringKeyDataType" "t")
ON "e.Dependents"."StringKeyDataTypeId"= "t"."Id"
WHERE "t"."Id"=N'Gumball!'
ORDER BY  "t"."Id"

I've been digging into the code, and it's hard to find much information on how to change the query generation engine at that level.

Should I open a separate question for this?

[roji]

@Gwindalmir your LINQ query doesn't produce a subquery for me, either on 2.2 and on 3.1:

Repro for 2.2

```c#
class Program
{
static void Main(string[] args)
{
using var ctx = new BlogContext();
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();

    var results = ctx
        .Set<StringKeyDataType>()
        .Include(e => e.Dependents)
        .Where(e => e.Id == "Gumball!")
        .ToList();
}

}

public class BlogContext : DbContext
{
public DbSet StringKeyDataTypes { get; set; }

pragma warning disable 618

public static readonly LoggerFactory ContextLoggerFactory
    = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

pragma warning restore 618

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
        .EnableSensitiveDataLogging()
        .UseLoggerFactory(ContextLoggerFactory);

}

public class StringKeyDataType
{
public string Id { get; set; }
public List Dependents { get; set; }
}

public class Dependent
{
public string Id { get; set; }
}

</details>

<details>
    <summary>Repro for 3.1</summary>

```c#
class Program
{
    static void Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var results = ctx
            .Set<StringKeyDataType>()
            .Include(e => e.Dependents)
            .Where(e => e.Id == "Gumball!")
            .ToList();
    }
}

public class BlogContext : DbContext
{
    public DbSet<StringKeyDataType> StringKeyDataTypes { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class StringKeyDataType
{
    public string Id { get; set; }
    public List<Dependent> Dependents { get; set; }
}

public class Dependent
{
    public string Id { get; set; }
}

Out of curiosity, which database are you trying to develop for? This issue is about removing a subquery join in a very particular case, but there are quite a few others where doing so isn't possible. Subquery joins are a standard SQL feature, and a database which doesn't support them is likely to have many issues as an EF Core relational provider...

Finally, note that EF Core 2.2 is no longer supported - 2.1 and 3.1 are the current long-term support versions. Any new development should probably happen against 3.1.

[smitpatel]

Just to answer how to do it, add a custom implementation IQueryTranslationPostprocessor deriving from RelationalQueryTranslationPostprocessor and replace ShapedQueryExpression.QueryExpression which would be a SelectExpression with a different SelectExpression to generate same result without subquery joins. If you find any lacking APIs to make required change, then another option is to provider custom IQuerySqlGenerator which will just simplify subquery join to table join when printing it out to DbCommand text.

[Gwindalmir]

Thanks, at the time I started, 3.1 wasn't released, and supporting .NET Framework is a requirement, so I went with 2.2.

I'm not sure why you don't see it, as the SQLite driver included in this source constructs the same query.
I downloaded the release/2.2 tag as my reference point.

As for the DB in question, I'm not sure I should reference it, as I work for the company that makes it. I will say it supports primarily SQL-92 standard, with a few SQL-99 additions.

[roji]

@Gwindalmir the best way would be to open a new issue and include a short, runnable code sample with SQLite that shows it happening.

If you're still in development, I'd strongly recommend considering switching to 3.1 - it's the LTS version for years to come, whereas 2.2 is already out of support.

[Webreaper]

@Gwindalmir I don't see the subquery with a single include or ThenInclude, using SQLite. It took two ThenIncludes for me to generate the subquery (see the example in issue #19418 linked above). That was with .Net Core 3.1.

[Gwindalmir]

@Gwindalmir I don't see the subquery with a single include or ThenInclude, using SQLite. It took two ThenIncludes for me to generate the subquery (see the example in issue #19418 linked above). That was with .Net Core 3.1.

I'm going to migrate to 3.1, and test again. If the issue is resolved there, then that's great. If not, I'll open a new issue here.
Thanks for the help everyone!

[Gwindalmir]

Just as a follow-up, in case anyone else has the same problem:
Upgrading to EF 3.1 solved the issue!

[roji]

Good to hear, thanks @Gwindalmir.

[Webreaper]

Any updates on an ETA for the original issue in this thread to be resolved? :)

[roji]

@Webreaper no update at the moment - this issue is "consider-for-next-release", which means it's a stretch goal for 5.0. While it's considered important, we don't think it's as important as the other issues have have triage into the 5.0 milestone (but it may still get done).

[Webreaper]

Totally understand. Thanks for the update! Looking forward to .Net 5!