Projection filtering on second-level reference could not be translated

[clemvnt]

Hi,

I encounter an issue when I make a query where a filter (or sort) is apply on a property of a reference of reference (second level).

The filter is apply on a projection (the three levels are transformed into DTO).

The entities are the following :

• A customer has a non-nullable reference to a company.
• A company has a nullable reference to a country.

The query is the following :

context.Customers
        .Select(m => new CustomerDto()
        {
            Id = m.Id,
            CompanyId = m.CompanyId,
            Company = m.Company != null ? new CompanyDto()
            {
                Id = m.Company.Id,
                CompanyName = m.Company.CompanyName,
                CountryId = m.Company.CountryId,
                Country = new CountryDto()
                {
                    Id = m.Company.Country.Id,
                    CountryName = m.Company.Country.CountryName,
                },
            } : null,
        })
        .Where(m => m.Company!.Country!.CountryName == "COUNTRY") // It fails
        .ToArray();

A few investigations :

• A filter on a property of the first-level reference works. (e.g. m => m.Company!.CompanyName == "COMPANY")
• When the first-level reference is non-nullable, it works.
• When I change the type of reference properties of DTO classes from interface to implementation, the filter works : public CountryDto? Country { get; set; } instead of public ICountryDto? Country { get; set; }
• The issue may be related to the following issue but it's closed : Projection filtering (Select().Where()) could not be translated on 2nd level #20826

Reproduction : https://github.com/clemvnt/efcore-projection-nested-reference

Stack trace

System.InvalidOperationException: The LINQ expression 'DbSet<Customer>()
    .LeftJoin(
        inner: DbSet<Company>(),
        outerKeySelector: c => EF.Property<string>(c, "CompanyId"),
        innerKeySelector: c0 => EF.Property<string>(c0, "Id"),
        resultSelector: (o, i) => new TransparentIdentifier<Customer, Company>(
            Outer = o,
            Inner = i
        ))
    .LeftJoin(
        inner: DbSet<Country>(),
        outerKeySelector: c => EF.Property<string>(c.Inner, "CountryId"),
        innerKeySelector: c1 => EF.Property<string>(c1, "Id"),
        resultSelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<Customer, Company>, Country>(
            Outer = o,
            Inner = i
        ))
    .Where(c => ((ICountryDto)c.Outer.Inner != null ? new CountryDto{
        Id = c.Inner.Id,
        CountryName = c.Inner.CountryName
    }
     : null).CountryName == "COUNTRY")' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0&)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at Program.<Main>$(String[] args) in C:\Users\cbernard\source\repos\EFCore.ProjectionOnNestedReference\Program.cs:line 10

Include provider and version information

EF Core version: 7.0.11
Database provider: Tested with Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Sqlite
Target framework: NET 8.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.7.1

[maumar]

This is the predicate we generate after nav expansion:

    .Where(c => (c.Outer.Inner != null ? new CompanyDto{ 
        Id = c.Outer.Inner.Id, 
        CompanyName = c.Outer.Inner.CompanyName, 
        CountryId = c.Outer.Inner.CountryId, 
        Country = new CountryDto{ 
            Id = c.Inner.Id, 
            CountryName = c.Inner.CountryName 
        }
    }
     : null).Country.CountryName == "COUNTRY")

which then gets simplified to:

    .Where(c => ((ICountryDto)c.Outer.Inner != null ? new CountryDto{ 
        Id = c.Inner.Id, 
        CountryName = c.Inner.CountryName 
    }
     : null).CountryName == "COUNTRY")

what we do is we see member access (Country) over conditional null check, and we simplify it by just leaving the member we are accessing and discarding the rest.
We should have done that recursively, since we have another member access (CoutryName) over conditional null check, but the convert to interface type is what's making us fail to recognize the pattern.

We inject the convert because outer_conditional.Coutry is typed as ICountryDto, but the result we get from first round of optimization is typed as CoutryDto.

This is what we would/should have gotten, which is perfectly trasnlatable and indeed what we produce for non-interface case:

.Where(c => (c.Outer.Inner != null ? c.Inner.CountryName : null) == "COUNTRY")

@clemvnt the issue you linked is indeed connected and fixes the case without interface (which was previously also broken)