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.79k stars 3.19k forks source link

[Regression] Projection causes InvalidOperationException "No coercion operator is defined between types" #12456

Closed Thorarin closed 5 years ago

Thorarin commented 6 years ago

I've been working on upgrading an application that was built on .NET Core 2.0 and EF Core 2.0 to version 2.1.1 of both. I've run into a specific query which works fine on EF Core 2.0, but is now failing.

The LINQ query in question:

var result = await db.Activities
    .Select(a => new
    {
        Activity = a,
        CompetitionSeason = db.CompetitionSeasons
            .First(s => s.StartDate <= a.DateTime && a.DateTime < s.EndDate)
    })
    .Select(a => new
    {
        Activity = a.Activity,
        CompetitionSeasonId = a.CompetitionSeason.Id,
        Points = a.Activity.Points ?? a.Activity.ActivityType.Points
            .Where(p => p.CompetitionSeason == a.CompetitionSeason)
            .Select(p => p.Points).SingleOrDefault()
    })
    .ToListAsync();

The issue seems to arise in the second projection being done. An InvalidOperationException is thrown:

System.InvalidOperationException
  HResult=0x80131509
  Message=No coercion operator is defined between types 'System.Int32' and 'EFCoreInvalidOperationException.Models.CompetitionSeason'.
  Source=System.Linq.Expressions
  StackTrace:
   at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at System.Linq.Expressions.Expression.Convert(Expression expression, Type type, MethodInfo method)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.WhereClause.TransformExpressions(Func`2 transformation)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection`1 nodes, String callerName)
   at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.SelectClause.TransformExpressions(Func`2 transformation)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel, Boolean asyncQuery)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.InMemory.Storage.Internal.InMemoryDatabase.CompileAsyncQuery[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
   at System.Linq.AsyncEnumerable.<Aggregate_>d__6`3.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at EFCoreInvalidOperationException.Program.<Main>d__0.MoveNext() in D:\Projects\EFCoreInvalidOperationException\EFCoreInvalidOperationException\Program.cs:line 17
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at EFCoreInvalidOperationException.Program.<Main>()

If I remove either the CompetitionSeasonId = or the Points = part of the projection, the query works fine in both EF Core versions. With both of them, it only works in EF Core 2.0.

Changing the Points = part of the query to this instead seems to work around the problem:

Points = a.Activity.Points ?? a.Activity.ActivityType.Points
    .Where(p => p.CompetitionSeason.Id == a.CompetitionSeason.Id)
    .Select(p => p.Points).SingleOrDefault()

Therefore, it seems to have something to do with accessing competitionSeason.Id but then also doing a comparison using the competitionSeason object itself.

Steps to reproduce

Further technical details

EF Core version: 2.1.1 Database Provider: any (I think, at least Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.InMemory) Operating system: Windows 10 (1803) IDE: Visual Studio 2017 15.7.4

sdanyliv commented 6 years ago

@Thorarin, you have created really challenging query for EF, but possibly solveabe. LINQ gives freedom of expressing your needs but often it is complicated to translate to the SQL. Just for academical interest, which SQL were produced by version 2.0?

smitpatel commented 6 years ago

Simplified repro:

public class Activity
    {
        public int Id { get; set; }
        public DateTime DateTime { get; set; }
        public List<Point> Points { get; set; }
    }

    public class CompetitionSeason
    {
        public int Id { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
    }

    public class Point
    {
        public int Id { get; set; }
        public CompetitionSeason CompetitionSeason { get; set; }
        public int? Points { get; set; }
    }

// Query
                (from a in db.Activities
                 let cs = db.CompetitionSeasons
                            .First(s => s.StartDate <= a.DateTime && a.DateTime < s.EndDate)
                 select new
                 {
                     cs.Id,
                     Points = a.Points.Where(p => p.CompetitionSeason == cs)
                 }
                 ).ToList();
ajcvickers commented 6 years ago

@maumar to de-dupe.

maumar commented 6 years ago

Problem is in nav reweite. We have optimization when navigation is compared to another, and the navigations were converted to navigation joins, we instead compare their keys. However we don't check that both sides of the comparison are converted to navigation joins - we do the conversion separately.

In the repro code, left side is a simple navigation that can be converted, but the right side is a subquery . In this case we should have left the query as is.

maumar commented 5 years ago

in new pipeline the scenario throws during translation:

Operation is not valid due to the current state of the object.

   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.SqlExpressions.SubSelectExpression.Verify(SelectExpression selectExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\SqlExpressions\SubSelectExpression.cs:line 24
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.SqlExpressions.SubSelectExpression..ctor(SelectExpression subquery) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\SqlExpressions\SubSelectExpression.cs:line 13
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalSqlTranslatingExpressionVisitor.cs:line 192
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalSqlTranslatingExpressionVisitor.cs:line 45
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalProjectionBindingExpressionVisitor.cs:line 118
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalProjectionBindingExpressionVisitor.cs:line 199
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalProjectionBindingExpressionVisitor.cs:line 130
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalProjectionBindingExpressionVisitor.cs:line 43
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 559
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in D:\git\EntityFrameworkCore\src\EFCore\Query\Pipeline\QueryableMethodTranslatingExpressionVisitor.cs:line 332
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) in D:\git\EntityFrameworkCore\src\EFCore\Query\Pipeline\QueryCompilationContext2.cs:line 62
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) in D:\git\EntityFrameworkCore\src\EFCore\Storage\Database.cs:line 72
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\QueryCompiler.cs:line 108
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\QueryCompiler.cs:line 97
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler) in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\CompiledQueryCache.cs:line 84
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\CompiledQueryCache.cs:line 59
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\QueryCompiler.cs:line 93
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\EntityQueryProvider.cs:line 79
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator() in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\EntityQueryable`.cs:line 94
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
maumar commented 5 years ago

blocked by #15611

maumar commented 5 years ago

blocking issue has been fixed, however this is still broken. Query we currently generate is:

SELECT (
    SELECT TOP(1) [c].[Id]
    FROM [CompetitionSeasons] AS [c]
    WHERE ([c].[StartDate] <= [a].[DateTime]) AND ([a].[DateTime] < [c].[EndDate])), [a].[Id], [t].[Id], [t].[Activity123Id], [t].[CompetitionSeasonId], [t].[Points]
FROM [Activities] AS [a]
LEFT JOIN (
    SELECT [p].[Id], [p].[Activity123Id], [p].[CompetitionSeasonId], [p].[Points]
    FROM [Points] AS [p]
    LEFT JOIN [CompetitionSeasons] AS [c0] ON [p].[CompetitionSeasonId] = [c0].[Id]
    WHERE (([c0].[Id] = (
        SELECT TOP(1) [c1].[Id]
        FROM [CompetitionSeasons] AS [c1]
        WHERE ([c1].[StartDate] <= [a].[DateTime]) AND ([a].[DateTime] < [c1].[EndDate]))) AND ([c0].[Id] IS NOT NULL AND (
        SELECT TOP(1) [c1].[Id]
        FROM [CompetitionSeasons] AS [c1]
        WHERE ([c1].[StartDate] <= [a].[DateTime]) AND ([a].[DateTime] < [c1].[EndDate])) IS NOT NULL)) OR ([c0].[Id] IS NULL AND (
        SELECT TOP(1) [c1].[Id]
        FROM [CompetitionSeasons] AS [c1]
        WHERE ([c1].[StartDate] <= [a].[DateTime]) AND ([a].[DateTime] < [c1].[EndDate])) IS NULL)
) AS [t] ON [a].[Id] = [t].[Activity123Id]
ORDER BY [a].[Id], [t].[Id]

exception:

The multi-part identifier "a.DateTime" could not be bound.
The multi-part identifier "a.DateTime" could not be bound.
The multi-part identifier "a.DateTime" could not be bound.
The multi-part identifier "a.DateTime" could not be bound.
The multi-part identifier "a.DateTime" could not be bound.
The multi-part identifier "a.DateTime" could not be bound.
smitpatel commented 5 years ago

Filed #17112 to last report

smitpatel commented 5 years ago

@ajcvickers - I am not sure how to track this as with underlying pipeline change, meaning of whole issue has changed.

ajcvickers commented 5 years ago

@smitpatel Fine to stay on the backlog as a bug in translation of the originally reported query.