I'm using EF Core 2.0.1. I'm trying to get the last order data along with a user info to populate UserDto as follows,
(from u in DbContext.User.Where(u => u.UserId == userId)
let last = u.Orders.LastOrDefault()
select new UserDto
{
Id = u.Id,
Name = u.Name,
LastOrderId = (last == null ? null : last.Id),
LastOrderDate = (last == null ? null : last.Date)
}
This throws following exception. How can I fix this?
at System.Linq.Expressions.Expression.Condition(Expression test, Expression ifTrue, Expression ifFalse, Type type)
at System.Linq.Expressions.ConditionalExpression.Update(Expression test, Expression ifTrue, Expression ifFalse)
at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression expression)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression expression)
at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
at Remotion.Linq.Clauses.SelectClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel)
at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass24_0`1.<CompileAsyncQuery>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
at System.Linq.AsyncEnumerable.<Aggregate_>d__6`3.MoveNext()
Formally there is nothing wrong with your query. But although EF Core tries to translate Last
/ LastOrDefault
methods (EF6 simply throws NotSupportedException
), apparently it still has issues doing so.
The problem is that Last
/ LastOrDefault
is not quite defined for database originating sequences which are unordered by nature.
Shortly, avoid Last
/ LastOrDefault
. Use OrderByDescending
+ First
/ FirstOrDefault
instead.
The following works:
let last = u.Orders.OrderByDescending(o => o.Date).FirstOrDefault()
Just use the ordering column(s) which is appropriate for your data logic for determining what is supposed to be "first", "last" etc.
Actually this also works:
let last = u.Orders.OrderBy(o => o.Date).LastOrDefault()
but EF Core will try to convert it to the aforementioned construct, thus is more prone to EF Core implementation bugs than the explicit way.