search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.72k stars 3.17k forks source link

Wrong order by clause generated when sorting by tph property #29856

Open QuokkaQueryable opened 1 year ago

QuokkaQueryable commented 1 year ago

It seems that ordering by a property which is projected like below with type checks cannot be translated.

Example for EFCore:

public class UserContext : DbContext
{
    public DbSet<Parent> Parents { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer(@"data source=(local);Integrated Security=SSPI;initial catalog=User_Core;MultipleActiveResultSets=True;Encrypt=False");
}

public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Child Child { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ChildDerived1 : Child
{
    public string Derived1Info { get; set; }
}

public class ChildDerived2 : Child
{
    public string Derived2Info { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using var db = new UserContext();
        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();

        var parent1 = new Parent
        {
            Name = "a",
            Child = new ChildDerived1 { Derived1Info = "Info1", Name = "Derived1" }
        };
        db.Parents.Add(parent1);

        var parent2 = new Parent
        {
            Name = "b",
            Child = new ChildDerived2 { Derived2Info = "Info2", Name = "Derived2" }
        };
        db.Parents.Add(parent2);

        db.SaveChanges();

        var parents = db.Parents.Select(p => new
            {
                p.Name,
                DerivedInfo = p.Child is ChildDerived1
                    ? (p.Child as ChildDerived1).Derived1Info
                    : p.Child is ChildDerived2
                        ? (p.Child as ChildDerived2).Derived2Info
                        : "Access Denied"
            })
            .OrderByDescending(b => b.DerivedInfo)
            .ToList();
    }
}

Stacktrace from EFCore:

System.InvalidOperationException: The LINQ expression 'DbSet<Parent>()
    .LeftJoin(
        inner: DbSet<Child>(), 
        outerKeySelector: p => EF.Property<int?>(p, "ChildId"), 
        innerKeySelector: c => EF.Property<int?>(c, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<Parent, Child>(
            Outer = o, 
            Inner = i
        ))
    .OrderByDescending(p => (p.Inner is ChildDerived1) ? (p.Inner as ChildDerived1).Derived1Info : (p.Inner is ChildDerived2) ? (p.Inner as ChildDerived2).Derived2Info : "Access Denied")' 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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Query generated from EF6:

SELECT
    [Project1].[C1] AS [C1],
    [Project1].[Name] AS [Name],
    [Project1].[C2] AS [C2]
    FROM ( SELECT
        [Extent1].[Name] AS [Name],
        1 AS [C1],
        CASE WHEN ([Extent2].[Discriminator] = N'ChildDerived1') THEN CASE WHEN ([Extent2].[Discriminator] = N'ChildDerived1') THEN [Extent2].[Derived1Info] END WHEN ([Extent2].[Discriminator] = N'ChildD
erived2') THEN CASE WHEN ([Extent2].[Discriminator] = N'ChildDerived2') THEN [Extent2].[Derived2Info] END ELSE N'Access Denied' END AS [C2]
        FROM  [dbo].[Parents] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Children] AS [Extent2] ON ([Extent2].[Discriminator] IN (N'ChildDerived1',N'ChildDerived2',N'Child')) AND ([Extent1].[Child_Id] = [Extent2].[Id])
    )  AS [Project1]
    ORDER BY [Project1].[C2] DESC

EF Core version: 7.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.4.1

ajcvickers commented 1 year ago

@QuokkaQueryable EF Core doesn't discover and map derived types automatically. Once I map the derived types, then the query works:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ChildDerived1>();
    modelBuilder.Entity<ChildDerived2>();
}
QuokkaQueryable commented 1 year ago

My example above works with the configuration but that was already present in my project, just missing from the example. Here´s a better example, notice the DerivedInfoDto:

public class UserContext : DbContext
{
    public DbSet<Child> Children { get; set; }
    public DbSet<Parent> Parents { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(@"data source=(local);Integrated Security=SSPI;initial catalog=User_Core;MultipleActiveResultSets=True;Encrypt=False");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Child>();
        modelBuilder.Entity<ChildDerived1>();
        modelBuilder.Entity<ChildDerived2>();
    }
}

public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Child Child { get; set; }
}

public abstract class Child
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ChildDerived1 : Child
{
    public string Derived1Info { get; set; }
}

public class ChildDerived2 : Child
{
    public string Derived2Info { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using var db = new UserContext();
        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();

        var parent1 = new Parent
        {
            Name = "a",
            Child = new ChildDerived1 { Derived1Info = "Info1", Name = "Derived1" }
        };
        db.Parents.Add(parent1);

        var parent2 = new Parent
        {
            Name = "b",
            Child = new ChildDerived2 { Derived2Info = "Info2", Name = "Derived2" }
        };
        db.Parents.Add(parent2);

        db.SaveChanges();

        var children = db.Children.Select(c => new
            {
                DerivedInfo = c is ChildDerived1
                    ? new DerivedInfoDto { Info = (c as ChildDerived1).Derived1Info }
                    : c is ChildDerived2
                        ? new DerivedInfoDto { Info = (c as ChildDerived2).Derived2Info }
                        : null
            })
            .OrderByDescending(b => b.DerivedInfo.Info)
            .ToList();  
    }

    private class DerivedInfoDto
    {
        public string InfoType { get; set; }
        public string Info { get; set; }
    }
}

This would result in this query with EF6:

SELECT
    [Project1].[C1] AS [C1],
    [Project1].[C2] AS [C2],
    [Project1].[C3] AS [C3]
    FROM ( SELECT
        1 AS [C1],
        CASE WHEN ([Extent1].[Discriminator] = N'ChildDerived1') THEN 1 WHEN ([Extent1].[Discriminator] = N'ChildDerived2') THEN 1 END AS [C2],
        CASE WHEN ([Extent1].[Discriminator] = N'ChildDerived1') THEN CASE WHEN ([Extent1].[Discriminator] = N'ChildDerived1') THEN [Extent1].[Derived1Info] END WHEN ([Extent1].[Discriminator] = N'ChildD
erived2') THEN [Extent1].[Derived2Info] END AS [C3]
        FROM [dbo].[Children] AS [Extent1]
        WHERE [Extent1].[Discriminator] IN (N'ChildDerived1',N'ChildDerived2',N'Child')
    )  AS [Project1]
    ORDER BY [Project1].[C3] DESC

Stacktrace:

The LINQ expression 'DbSet<Child>()
    .OrderByDescending(c => (c is ChildDerived1) ? new DerivedInfoDto{ Info = (c as ChildDerived1).Derived1Info }
     : (c is ChildDerived2) ? new DerivedInfoDto{ Info = (c as ChildDerived2).Derived2Info }
     : null.Info)' 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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
ajcvickers commented 1 year ago

@QuokkaQueryable This can be rewritten as:

var children = db.Children.Select(c => new
    {
        DerivedInfo = new DerivedInfoDto { Info = c is ChildDerived1
            ? ((ChildDerived1)c).Derived1Info
            : ((ChildDerived2)c).Derived2Info }
    })
    .OrderByDescending(b => b.DerivedInfo.Info)
    .ToList();

Which translates to:

SELECT CASE
    WHEN [c].[Discriminator] = N'ChildDerived1' THEN [c].[Derived1Info]
    ELSE [c].[Derived2Info]
END AS [Info]
FROM [Children] AS [c]
ORDER BY CASE
    WHEN [c].[Discriminator] = N'ChildDerived1' THEN [c].[Derived1Info]
    ELSE [c].[Derived2Info]
END DESC
QuokkaQueryable commented 1 year ago

@ajcvickers

Thanks, this would work for this example but it does not solve the problem entirely. It gets ugly quickly with the following considerations:

  1. Dto has more than just one property, maybe 3-5
  2. Navigation properties from these types (here ChildDerived1 and ChildDerived2) get used in many places
  3. Instead of two entity types (here ChildDerived1 and ChildDerived2) there are dozens of types which would result in the same dto

There might not be more than lets say 4 navigation properties to consider in one projection, but this might still result in something like this:

public abstract class Child
{
    public int Id { get; set; }
    public string Name { get; set; }

    public SomeNav1 SomeNav1 { get; set; }
    public SomeNav2 SomeNav2 { get; set; }
    public SomeNav3 SomeNav3 { get; set; }
    public SomeNav4 SomeNav4 { get; set; }
}

public class ChildDerived1 : Child
{
    public string Derived1Info { get; set; }
}

public class ChildDerived2 : Child
{
    public string Derived2Info { get; set; }
}

public class SomeNav1
{
    public int Id { get; set; }
    public string Info1 { get; set; }
    public string Info2 { get; set; }
    public string Info3 { get; set; }
}

public class SomeNav2
{
    public int Id { get; set; }
    public string Info1 { get; set; }
    public string Info2 { get; set; }
    public string Info3 { get; set; }
}

public class SomeNav3
{
    public int Id { get; set; }
    public string Info1 { get; set; }
    public string Info2 { get; set; }
    public string Info3 { get; set; }
}

public class SomeNav4
{
    public int Id { get; set; }
    public string Info1 { get; set; }
    public string Info2 { get; set; }
    public string Info3 { get; set; }
}

private class DerivedInfoDto
{
    public string Info1 { get; set; }
    public string Info2 { get; set; }
    public string Info3 { get; set; }
}

var children = db.Children.Select(c => new
{
    DerivedInfo = new DerivedInfoDto
    {
        Info1 = c.SomeNav1 != null
            ? c.SomeNav1.Info1
            : c.SomeNav2 != null
                ? c.SomeNav2.Info1
                : c.SomeNav3 != null
                    ? c.SomeNav3.Info1
                    : c.SomeNav4 != null
                        ? c.SomeNav4.Info1
                        : null,
        Info2 = c.SomeNav1 != null
            ? c.SomeNav1.Info2
            : c.SomeNav2 != null
                ? c.SomeNav2.Info2
                : c.SomeNav3 != null
                    ? c.SomeNav3.Info2
                    : c.SomeNav4 != null
                        ? c.SomeNav4.Info2
                        : null,
        Info3 = c.SomeNav1 != null
            ? c.SomeNav1.Info3
            : c.SomeNav2 != null
                ? c.SomeNav2.Info3
                : c.SomeNav3 != null
                    ? c.SomeNav3.Info3
                    : c.SomeNav4 != null
                        ? c.SomeNav4.Info3
                        : null
    }
})
.OrderByDescending(b => b.DerivedInfo.Info1)
.ToList();

Also the individual expressions would become convoluted because the schema is not as clean cut as in the example above. Imagine for example that each navigation type here would have several properties which would have to be combined for the dto. Previously in EF6 this would not be a problem, because I could just reuse the projection expressions for the entire dto. Then it would be something like this:

var children = db.Children.Select(c => new
{
    DerivedInfo = c.SomeNav1 != null
                    ? SomeNav1Expression.Invoke(c)
                    : c.SomeNav2 != null
                        ?  SomeNav2Expression.Invoke(c)
                        : c.SomeNav3 != null
                            ?  SomeNav3Expression.Invoke(c)
                            : c.SomeNav4 != null
                                ?  SomeNav4Expression.Invoke(c)
                                : null
    }
})
.OrderByDescending(b => b.DerivedInfo.Info1)
.ToList();

I would then have been able to reuse these expression inside other projection expressions. But if I have to set each property as in your suggestion then I cannot reuse the expressions. Is there something else you would suggest for cases like this in EFCore?

ajcvickers commented 1 year ago

Note from triage: we will consider translating the requested pattern.