Enhance InvalidOperationException message fo better cause understaning

[lucacestola]

While writing some complex queries it's easy to face an InvalidOperationException that says that the expression can't not be translated.

Often I find an obvious cause just by taking a better look at a misuse of an untranslatable function or a bad use of a group by. Sometimes, however, it is a little tougher and I start breaking the query into several steps until I find the point where it becomes untranslatable.

This time I ran into a strange situation where applying an OrderBy breaks the query translation and however I tried to simplify or make the query more straightforward, I was unable to get to work. The InvalidOperationException states that the whole Expression cannot be translated, but I guess the engine knows on which part of the expression it fails transalating.

It would be helpful if the exception could suggest which part of the expression caused the problem and, of course, why it couldn't. The exception should be more specific than a System.InvalidOperationException, with at least the specific Expression (or a list of them) where the problem occurred, but it would be better to also have the reason.

[smitpatel]

The InvalidOperationException states that the whole Expression cannot be translated, but I guess the engine knows on which part of the expression it fails transalating.

The very last queryable operator in that whole expression is throwing error. We print out whole expression so that you can correlated it with your query.

[lucacestola]

Ok it's clear and does make sense. Can you confirm that the descriptive part of the exception message (the one that states that the query "could not be translated") is a fixed message and doesn't contains specific indications?

In the example I've made, the problem appears just adding an OrderBy (for pagination pourpose) on a column of the select. It is strange that this can leads to query compilation error. I am inclined to think that there may be some limitation with EF in case of complex query, but of course I may be completely wrong.

Do you have any suggestion on how to face those kind of problems, starting from the information contained into InvalidOperationException?

[smitpatel]

@lucacestola - Can you share the example query? Words are not really great at communicating LINQ query especially the one which is causing strange errors.

[lucacestola]

I hope that the dump of the exception is enough because in code the query is composed by more steps.

System.InvalidOperationException: The LINQ expression 'DbSet<Transaction>()
	.Where(t => !(t.SoftDelete))
	.Where(t => False || (Nullable<Guid>)t.HeadquarterId == __currentHeadquarterId_0)
	.Where(t => (int)t.ScopeId != 2)
	.Where(t => t.ValueDate.Year == __prevYear_1)
	.Where(t => t.JointDeclarationId == null)
	.Join(
		inner: DbSet<Transaction>()
			.Where(t0 => !(t0.SoftDelete))
			.Where(t0 => False || (Nullable<Guid>)t0.HeadquarterId == __currentHeadquarterId_0)
			.Where(t0 => (int)t0.ScopeId != 2)
			.GroupBy(
				keySelector: t0 => t0.ContributorId, 
				elementSelector: t0 => t0)
			.Where(e => e
				.Sum(t1 => t1.Amount) > ___parameters_Threshold2_2)
			.Select(e => e.Key), 
		outerKeySelector: t => t.ContributorId, 
		innerKeySelector: e0 => e0, 
		resultSelector: (t, e0) => new TransparentIdentifier<Transaction, Guid>(
			Outer = t, 
			Inner = e0
		))
	.Select(ti => ti.Outer)
	.Union(DbSet<Transaction>()
		.Where(t1 => !(t1.SoftDelete))
		.Where(t1 => False || (Nullable<Guid>)t1.HeadquarterId == __currentHeadquarterId_0)
		.Where(t1 => (int)t1.ScopeId != 2)
		.Where(t1 => t1.ValueDate.Year == __year_3)
		.Where(t1 => t1.JointDeclarationId == null)
		.Join(
			inner: DbSet<Transaction>()
				.Where(t2 => !(t2.SoftDelete))
				.Where(t2 => False || (Nullable<Guid>)t2.HeadquarterId == __currentHeadquarterId_0)
				.Where(t2 => (int)t2.ScopeId != 2)
				.Where(t2 => t2.ValueDate.Year == __year_3)
				.GroupBy(
					keySelector: t2 => t2.ContributorId, 
					elementSelector: t2 => t2)
				.Where(e1 => e1
					.Sum(t1 => t1.Amount) > ___parameters_Threshold2_2)
				.Select(e1 => e1.Key), 
			outerKeySelector: t1 => t1.ContributorId, 
			innerKeySelector: e2 => e2, 
			resultSelector: (t1, e2) => new TransparentIdentifier<Transaction, Guid>(
				Outer = t1, 
				Inner = e2
			))
		.Select(ti0 => ti0.Outer))
	.Join(
		inner: DbSet<BasePerson>()
			.Where(b => !(b.SoftDelete)), 
		outerKeySelector: e3 => EF.Property<Nullable<Guid>>(e3, "ContributorId"), 
		innerKeySelector: b => EF.Property<Nullable<Guid>>(b, "Id"), 
		resultSelector: (o, i) => new TransparentIdentifier<Transaction, BasePerson>(
			Outer = o, 
			Inner = i
		))
	.GroupBy(
		keySelector: e3 => new { 
			Id = e3.Inner.Id, 
			DisplayName = e3.Inner.DisplayName, 
			FiscalCode = e3.Inner.FiscalCode, 
			Type = e3.Inner.Type, 
			Year = e3.Outer.ValueDate.Year, 
			Month = e3.Outer.ValueDate.Month, 
			x = __p_4, 
			HasDeclaration = e3.Outer.JointDeclarationId != null
		 }, 
		elementSelector: e3 => e3.Outer)
	.Select(e4 => new JointDeclarationPendingViewModel{ 
		Id = e4.Key.Id, 
		ContributorDisplayName = e4.Key.DisplayName, 
		ContributorFiscalCode = e4.Key.FiscalCode, 
		Type = e4.Key.Type, 
		DueDate = e4.Key.Year == __year_5 ? e4
			.Min(t => t.ValueDate) + __timeSpan_6 : __dueDateForLastYearTransactions_7, 
		DueDateAlert = __Functions_8
			.DateDiffDay(
				startDate: e4
					.Min(t => t.ValueDate), 
				endDate: __today_9) <= __dueDateDaysAlert_10, 
		Amount = e4
			.Sum(t => t.JointDeclarationId == null ? t.Amount : 0) 
	}
	)
	.OrderBy(e5 => e5.Id)' 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 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.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryDebugView.get_Query()
   at DCSP.DebugExtensions.ToQueryTree[T](IQueryable`1 source) in C:\DCSP\DebugExtensions.cs:line 13
   at DCSP.Code.Services.QueryService.QueryPendingJointDeclaration(IQueryable`1 transactions, DateTime today) in C:\DCSP\Code\Services\QueryService.cs:line 65
   at DCSP.Controllers.JointDeclarationController.Index(FilteredResult`2 viewModel) in C:\DCSP\Controllers\JointDeclarationController.cs:line 80
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Microsoft.EntityFrameworkCore.Infrastructure: Debug: 'DatabaseContext' disposed.

The same query without the last OrderBy is translated correctly.

[smitpatel]

• The selector after GroupBy is not purely in terms of aggregate. This is causing client eval of Select. This happens (even though GroupBy was before) because the grouping is not selected in projection. All the reference to grouping are either key access or aggregate over it.
• The OrderBy fails because we fail to find translation for it. This happens due to client projection appearing before.
• Above point may make a justification that whenever we fail to translate, we should throw better error indicating that earlier Select was client eval but this may not be accurate or better in long term since we want to enable translation even if the projection is client eval'ed as long as we can find suitable translation.
• While the OrderBy looks simple but to figure out why it couldn't be translated based on the query before especially after we allow point 3 would be even harder. Plus, reading query and see what is wrong is much simpler than trying to identify the pattern in code inside expression tree.

[lucacestola]

I agree that analyzing complex patterns is costly in terms of effort and also that the developer needs to understand how things work now compared to older versions of EF. I just started converting old project to .net5 so it's easy to be lazy and avoid to study a lot before getting hands on code 😄.

With your indications it was easy to understand that the cause was an addition of a datetime with a timespan. Converting it with an DateTime.AddDays fixed the client eval.

At this point I'm trying to figure out how to better face those kind of situation and even if experience and knowledge is the best tool, I think that having some more advice, could help developer to better learn.

I see that it's not longer possible to use ConfigureWarnings to configure exception on client evaluation and I guess that this is due to the rethink of client eval itself that is now focused on avoid client eval on potential performance leaks situations, that now always lead to an exception. And I'm happy with that!

Anyway, precisely because of the complex patterns, it would be good if the developer could state (on query basis?) when client eval is not expected and so avoid unwanted situations. I know that having specific method for debug purpose may be an unwanted API design, but I hope this discussion may help addressing some initial difficulties at the best.

[smitpatel]

Good discovery on DateTime + TimeSpan. I did not figure out myself exact cause of client eval. This sort of client eval - in projection - was allowed in EF6 also. It is in all versions of EF Core too. Since it is expected to perform client eval in projection (because at times you are just creating DTO objects which is also client eval in one way), we never warned for those. Even if you had enabled exception on client eval in previous version of EF Core, such Select would not give error. Essentially in previous version it would throw error in same way. So I am not sure, if there is any good way to allow users to know the Select did client eval which doesn't cause unnecessary warnings/errors. May be a debug method as you suggested, we can look into it but most likely it would throw for almost every select which is not single entity being projected out.

For the particular issue of DateTime + TimeSpan,
We currently make this kind of operation return not translated here

efcore/src/EFCore.SqlServer/Query/Internal/SqlServerSqlTranslatingExpressionVisitor.cs

Lines 91 to 98 in e6d926f

	return !(base.VisitBinary(binaryExpression) is SqlExpression visitedExpression)
	? QueryCompilationContext.NotTranslatedExpression
	: visitedExpression is SqlBinaryExpression sqlBinary
	&& _arithmeticOperatorTypes.Contains(sqlBinary.OperatorType)
	&& (_dateTimeDataTypes.Contains(GetProviderType(sqlBinary.Left))
	|| _dateTimeDataTypes.Contains(GetProviderType(sqlBinary.Right)))
	? QueryCompilationContext.NotTranslatedExpression
	: visitedExpression;

We should add detailed errors. Though it won't help this case since the client eval is in Projection and error throwing place is different.