Simple query generates many unnecessary LEFT JOINS

[altmann]

In our project we observe that a simple query of an entity causes many unnecessary LEFT JOINs.

The following setup causes the problem:

• An entity which has ...
  • a property of an OwnedEntity and
  • a property of a list of another entity
• Querying the database ...
  • with SingleAsync() (querying with ToListAsync() is no problem)
  • with including the list property Include(x => x.SimpleEntities)

    public class Person
    {
        public int Id { get; set; }

        public SimpleOwnedEntity SimpleOwnedEntity { get; set; }

        public IEnumerable<SimpleEntity> SimpleEntities { get; set; }
    }

    public class SimpleEntity
    {
        public int Id { get; set; }

        public string MyText { get; set; }
    }
    
    public class SimpleOwnedEntity
    {
        public DateTime? DateTime { get; set; }
    }

When I execute var person = appContext.Persons.Include(x => x.SimpleEntities).SingleAsync(x => x.Id == 1); then the following query is generated:

SELECT "x.SimpleEntities"."Id", "x.SimpleEntities"."MyText", "x.SimpleEntities"."PersonId"
FROM "SimpleEntity" AS "x.SimpleEntities"
INNER JOIN (
    SELECT DISTINCT "t".*
    FROM (
        SELECT "x0"."Id"
        FROM "Persons" AS "x0"
        LEFT JOIN "Persons" AS "x.SimpleOwnedEntity0" ON "x0"."Id" = "x.SimpleOwnedEntity0"."Id"
        WHERE "x0"."Id" = 1
        ORDER BY "x0"."Id"
        LIMIT 1
    ) AS "t"
) AS "t0" ON "x.SimpleEntities"."PersonId" = "t0"."Id"
ORDER BY "t0"."Id"

The problematic part of the query is the LEFT JOIN which is completely unnecessary. Person Id is compared with "x.SimpleOwnedEntity0"."Id" which is also the Person Id.

The bad thing is that we use in our data model many Owned Entities and the query generate for each Owned Entity one LEFT JOIN. Because of that our query in our real project contains many (more then 60) unnecessary LEFT JOINS.

We use in our project Sqlite which has an limitation: Maximal 64 tables in a join query. This limit is not configureable and because EF Core produces many unnecessary LEFT JOINs in a query we reach the limit of 64 tables in a join query.

We also tried to use the InMemoryDatabase from Ef Core but here we faced very poor performance caused by the many LEFT JOINs. So this is not a practical solution for us.

Our temporary workaround: We don't query the database with SingleAsync() because this contains the problem. Instead we query the database with .Where(x => x.Id == 1).ToListAsync() and then make a .Single().

Steps to reproduce

Here is a complete solution with a very small setup which produce the LEFT JOINs.
EfCoreSample.zip

Further technical details

EF Core version: v2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.5

[smitpatel]

Duplicate of #12022

[altmann]

@smitpatel It is similar but I think it is not the same issue. In my issue Owned Entities are LEFT JOINed with the parent entity which is my opinion not necessary because in the Owned Entity properties are at the same table.

You see it in the query. LEFT JOIN "Persons" AS "x.SimpleOwnedEntity0" ON "x0"."Id" = "x.SimpleOwnedEntity0"."Id"

• The alias "x0" is the parent table "Persons".
• The alias "x.SimpleOwnedEntity0" is also the parent table "Persons".
• "x0"."Id" and "x.SimpleOwnedEntity0"."Id" is the same id, so the LEFT JOIN compare the same id

If you have any questions please message me. For us it is important that the bug is fixed and if it is expected behavior to understand why this LEFT JOIN is needed and to have a long-term solution.

[smitpatel]

@altmann - Removing Joins with owned entity (due to them being same table) is task of Join Elimination. It is not really accurate at this point. (e.g. #11817). We have a task to refactor whole thing slightly differently so that joins are not created in first place.

As for the duplication, when generating collection include query (which has 2 queries), we copy over first query to do inner join in second query. The joins from first query is also copied over. But if you look at the projection inside the inner join subquery then doing left join is not going to change the result. Further we generate Inner join only when it is safe to do (no change of results of outer). Hence those joins are unnecessary and removing them makes shorter & faster SQL.

While there are 2 different ways to solve this one, the latter approach is better.

[sdanyliv]

Looks like Deja Vu for me.
In linq2db we also process association optimization later. Even better optimizator can remove duplicated or unnecessary joins if members are not in projection. Which sometimes cause complex query to produce simple select from without any joins or subselects - dream for ad-hoc reporting.
It is very good optimization for systems that have complex row level security and extensively use query decomposition. Usually in this design they do joins to filtered IQueryable recordsets which contain joins to the same tables with the same join keys, because they just check permissions and filter out not needed records. This approach is good for performance and security but it is an enemy for repository pattern.

Offtop
Impatiently waiting for c# 8 with proper pattern matching to simplify optimizator improvement.

[altmann]

@smitpatel Ok thanks for your answer. Luckily we found a workaround (see above) but I hope this is only temporary in our project. What do think, when or with which version we get a more optimized query?

[matteocontrini]

I'm seeing this issue of useless LEFT JOINs when using Where on an owned entity with EF Core 3.0. Could this be re-opened? Or should I create another issue?

[smitpatel]

@matteocontrini - Your issue is certainly not same as this one. Please file a new issue with detailed repro.

[matteocontrini]

Thanks. For reference: #18299