efcore6 produces wrong SELECT SQL when Where/OrderBy clauses are applied to Owned properties followed by .Take()

[DevTrevi]

Hi everyone,

I found a bug in efcore6 that now produces an invalid SELECT statement when filtering/sorting on owned types stored on other tables.
In particular, the bug happens when more than one owned type is linked to an entity and the where clause is applied NOT to the last owned property, followed by a .Take() clause.

Can you please check this behavior? Thanks in advance!

Example

A Company entity that can be either a Customer, a Supplier or both.
Additional Customer data and Supplier data are stored in separate tables with a 1..1 relationship with the Company table.
These tables are configured as Owned properties of Customer:

/// <summary>
/// Entity representing a company. 
/// A company can be either a customer, a supplier, or both.
/// If the company is a cutomer and/or a supplier, additional data describing customer/supplier is stored in
/// owned types CustomerData and SupplierData, stored in two separate tables with a 1..1 relationship with Company
/// </summary>
public class Company
{
    public int Id { get; set; }

    public string? Name { get; set; }
    public CustomerData? CustomerData { get; set; }
    public SupplierData? SupplierData { get; set; }
}

/// <summary>
/// Owned 1 of Company in 1..1 relationship, on another table
/// </summary>
public class CustomerData
{
    public int Id { get; set; }
    public string? AdditionalCustomerData { get; set; }

}

 /// <summary>
 /// Owned 2 of Company in 1..1 relationship, on another table
 /// </summary>
 public class SupplierData
 {
     public int Id { get; set; }
     public string? AdditionalSupplierData { get; set; }

 }

Mapping:

internal class CompanyMapping : IEntityTypeConfiguration<Company>
{
    public void Configure(EntityTypeBuilder<Company> builder)
    {
        builder.ToTable("Companies");

        builder.HasKey(r => r.Id);
        builder.Property(r=>r.Id).UseIdentityColumn();

        builder.OwnsOne(r => r.CustomerData,
            b =>
            {
                b.ToTable("CustomerData");

                b.HasKey(r => r.Id);

                b.WithOwner().HasForeignKey(r => r.Id).HasPrincipalKey(r => r.Id);
            });

        builder.OwnsOne(r => r.SupplierData,
            b =>
            {
                b.ToTable("SupplierData");

                b.HasKey(r => r.Id);

                b.WithOwner().HasForeignKey(r => r.Id).HasPrincipalKey(r => r.Id);
            });
    }
}

Executing the following query

var ctx = GetDbContext();

var q = ctx.Companies
        .Where(r => r.CustomerData != null)
        .Take(10);

var results = await q.ToArrayAsync();

will produce the following wrong SQL when using efcore6:

-- sql generated by efcore6 (wrong)
-- Columns of the first owned type are NOT included in the inner select, so they are not available in the outer select
-- A left join is also missing from the outer select
DECLARE @__p_0 int = 10;

SELECT[t].[Id], [t].[Name], [c0].[Id], [c0].[AdditionalCustomerData], [s].[Id], [s].[AdditionalSupplierData]
FROM(
    SELECT TOP(@__p_0)[c].[Id], [c].[Name]
    FROM[Companies] AS[c]
    LEFT JOIN[CustomerData] AS[c0] ON[c].[Id] = [c0].[Id]
    WHERE[c0].[Id] IS NOT NULL
) AS[t]
LEFT JOIN[SupplierData] AS[s] ON[t].[Id] = [s].[Id]

-- Error: The multi-part identifier "c0.AdditionalCustomerData" could not be bound.

the same query works as expected in efcore5, producing the following valid SQL:

-- sql generated by efcore5 (working)
DECLARE @__p_0 int = 10;

SELECT[t].[Id], [t].[Name], [t].[Id0], [t].[AdditionalCustomerData], [s0].[Id], [s0].[AdditionalSupplierData]
FROM(
    SELECT TOP(@__p_0)[c].[Id], [c].[Name], [c0].[Id] AS[Id0], [c0].[AdditionalCustomerData]
    FROM[Companies] AS[c]
    LEFT JOIN[CustomerData] AS[c0] ON[c].[Id] = [c0].[Id]
    WHERE[c0].[Id] IS NOT NULL
) AS[t]
LEFT JOIN[SupplierData] AS[s] ON[t].[Id] = [s].[Id]
LEFT JOIN[SupplierData] AS[s0] ON[t].[Id] = [s0].[Id]

Attached solution

EfCore6Bugs.zip

The solution contains projects targeting .net5.0 with efcore 5.0.12 and .net6.0 with efcore 6.0.0.
The first project contains the DbContext, the second one is a simple unit test.

On test initialization, an EfCore6Bugs database will be created on (LocalDb)\MSSQLLocalDB (please customize the connection string in TestDataContext.cs as needed) applying migrations for the DbContext.
Unit tests should all be successful for .net5.0 (efcore5), while EfCore6ClausesOnFirstOwnedFails will fail for .net6.0 (efcore6)

[smitpatel]

Unrelated note - the SQL generated in 5.0 caused unnecessary join with SupplierData

[smitpatel]

Potential root cause, we didn't update references to CustomerData owned entity after pushing down into subquery caused by Take(10)

Expanding other navigations causes initial SelectExpression to change which causes earlier expanded owned navigations to be incorrect. This won't be an easy fix.

[smitpatel]

Due to fix being on riskier side, we are fixing this issue in 7.0 release (& nightly builds). We will determine to patch this in future if we get more customers hitting this.

[ChristopherHaws]

We have now hit this issue several times in our app.

[ajcvickers]

Now two customer reports; re-opening to re-discuss patching.

[ajcvickers]

Note from triage: @smitpatel Can you open a PR against release/6.0 and we will take this back to Tactics.

[jwyoung9]

Just upgraded an app to 6.0 and am also hitting this issue.

[tuggernuts]

Happy New Year. I just ran into a similar issue (in 6.0.1) which I'm guessing is related.

In short, an entity with 2 owned types - one of which is in an external table - will have the same error as OP when using Take().

I have a repo here.

If this is unrelated - and a bug - please let me know if you want me to open a separate issue.

[ajcvickers]

@cdrfenix Please open a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[rofenix2]

My bad the query was well generated, i just didnt realize it till i checked in the execution plan.
I will delete my old post, thanks for your time.