efcore: Invalid query produced when projecting DTO containing subquery that returns a single element

Describe what is not working as expected.

Using a simplified version of the #6534 code, get an InvalidOperationException instead of expected compilation failure (pre-1.1.1) or result (with 1.1.1). Main simplification is this one does not involve an optional (1 => 0,1) relationship.

Using the out-of-the-box application database created in a .NET Core web application with Individual User Accounts, add the following to HomeController:

        public async Task<List<Results>> Bug1()
        {
            var val = from c in _dbContext.Users
                      where c.EmailConfirmed
                      orderby c.Email
                      select new Results
                      {
                          Count = c.Claims.Count(x => x.Id <= 3),
                          Name = c.UserName,
                      };

            return await val.ToListAsync();
        }

        public class Results
        {
            public int Count { get; set; }

            public string Name { get; set; }
        }

Then, restore, build and run the application. Navigate to /home/bug and see the stack on the console.

If you are seeing an exception, include the full exceptions details (message and stack trace).

fail: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[1]
      An exception occurred in the database while iterating the results of a query.
      System.InvalidOperationException: No value provided for required parameter '_outer_Id'.
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__26.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<BufferlessMoveNext>d__9.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__6`2.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__8.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
System.InvalidOperationException: No value provided for required parameter '_outer_Id'.
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__26.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<BufferlessMoveNext>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__6`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[0]
      An unhandled exception has occurred: No value provided for required parameter '_outer_Id'.
System.InvalidOperationException: No value provided for required parameter '_outer_Id'.
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__26.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<BufferlessMoveNext>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__6`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__129`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at EntityFramework_6534.Controllers.HomeController.<Bug1>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ObjectMethodExecutor.<CastToObject>d__40`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__25.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextResourceFilter>d__22.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ResourceExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeAsync>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.<Invoke>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.<Invoke>d__6.MoveNext()

Steps to reproduce

See description above.

Further technical details

EF Core version:

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.1" PrivateAssets="All" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="1.1.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" Version="1.1.1" PrivateAssets="All" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="1.1.0" PrivateAssets="All" />

Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 IDE: Visual Studio 2017 RC

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 7
Comments: 36 (15 by maintainers)

Commits related to this issue

#126 updated solution file to VS2017 RTM, No upgrade the tooling becuase the blocking issue https://github.com/aspnet/EntityFramework/issues/7714 — committed to simplcommerce/SimplCommerce by thiennn 7 years ago
Fix to #7714 - Invalid query produced when projecting DTO containing subquery that returns a single element Problem was that in the fix for #7033 we force client evaluation on queries that try to bin... — committed to dotnet/efcore by maumar 7 years ago
Improved fix to #7714 - Invalid query produced when projecting DTO containing subquery that returns a single element Problem was that in the fix for #7033 we force client evaluation on queries that t... — committed to dotnet/efcore by maumar 7 years ago
Improved fix to #7714 - Invalid query produced when projecting DTO containing subquery that returns a single element Problem was that in the fix for #7033 we force client evaluation on queries that t... — committed to dotnet/efcore by maumar 7 years ago
#126 updated solution file to VS2017 RTM, No upgrade the tooling becuase the blocking issue https://github.com/aspnet/EntityFramework/issues/7714 — committed to elsashop/SimplCommerce by bruslega 7 years ago

Most upvoted comments

Tried to use vs 2017. Migrated projects, all compiled, but when try to get data from database I’ve got System.InvalidOperationException: ‘No value provided for required parameter ‘_outer_Id’.’

Select Example:

var user = await _context.Users.Where(x => x.Id == id).Select(x => new User
            {
                Id = x.Id,
                Field1Count = x.Fields.Count(y => y.Type == FieldType.Type1)
...}.FirstOrDefaultAsync();

For queries without getting data from navigation properties all is good. All EFcore versions is latest stable 1.1.1(0)(Like mentioned above). VS 2017 Release. In VS 2015 have no problem. If move this Count query to other query then both of them executed without problems.

Update: I’ve just realize that’s a huge step back, if for 1 entity I can made some additional queries, but I have similar queries that return list, I now I should made additional request for every entity, that a huge overhead.

+11

parys on Mar 8, 2017

The same thing for me:

.Select(r => new SimpleClass
{
  Id = r.Id,
  Name = r.Name,
  Count= r.Entries.Count <-----
}).ToList();

Error: No value provided for required parameter ‘_outer_Id’.

If I change the code to create an anonymous class like that:

.Select(r => new
{
  Id = r.Id,
  Name = r.Name,
  Count= r.Entries.Count
}).ToList();

Works as usual.

The Error appeared after upgrading to VS 2017.

UPDATE: If someone wants a workaround before it gets fixed. Here’s one with using automapper:

var rows = this.context.data
	.Select(r => new
	{
		Id = r.Id,
		Name = r.Name,
		Description = r.Description,
		Count = r.Entries.Count
	}).ToList();

var config = new MapperConfiguration(cfg => cfg.CreateMissingTypeMaps = true);
var mapper = config.CreateMapper();

return categories.Select(mapper.Map<SimpleClass>).ToList();

szykov on Mar 9, 2017

@paddyfink the fix will be available with the next patch release (1.1.2). Unfortunately we don’t have any dates yet. If possible, you should use the workaround, i.e. don’t use the DTO, but use anonymous type instead (and then project into DTO in memory):

rather than:

customers.Select(c => new MyDTO { Count = c.Orders.Count() }).ToList();

do:

customers.Select(c => new { Count = c.Orders.Count() }).ToList().Select(r => new MyDTO { Count = r.Count });

If this workaround works for you, you should use it even after the fix is in, as the queries with DTO we will produce inefficient SQL (rather than simply throwing).

maumar on Mar 15, 2017

@szykov @joshmouch good news, we did some extra digging around this issue and were able to come up with a different, more functional approach. Once the patch is live, you should be able to use DTOs without drawbacks, just like in 1.1.0. Query from #7915 will also be better, although there will still be N+1 due to other issues (discussed in the bug itself)

maumar on Mar 28, 2017

@Eilon My project was hit by this bug with 1.1.1 so I have have kept it at EfCore 1.1.0. I tested the preview package of 1.1.2 now (1.1.2-rtm-201704210746), it works great! 😃 No more exception

andershermansen on Apr 24, 2017

@szykov when the fix is in, the first query from my example will produce inefficient sql (N+1 queries, i.e. a query to fetch customers and then one additional query per customer to calculate their order count), rather than throwing an exception.

The second example (workaround) is currently producing efficient query and will continue to do so after the patch.

Using DTO is not a bad practice per se, however the behavior is a limitation of EF 1.1.x As part of the fix for #7033 we started doing client evaluation for queries having a particular pattern. The queries affected by this bug look quite similar but they don’t require client evaluation. However, the code that is making the decision whether client evaluation is needed or not doesn’t have the information necessary to distinguish them. We can’t provide this information without introducing a breaking change (to providers API), which we can’t do in the patch release. So the next best thing is to err on the side of caution and force client evaluation for those queries, even though they don’t really need it.

Basically, our options are either: translate queries from 7033 (incorrect) AND translate queries from this bug (correct) - 1.1.0 behavior or client evaluate queries from 7033 (correct) AND client evaluate queries from this bug (correct but inefficient) - proposed 1.1.2 behavior

This is properly fixed in 2.0.0, producing efficient sql regardless of whether DTO is used or not.

maumar on Mar 15, 2017

Preview builds of this patch fix should be available on the following feeds:

https://dotnet.myget.org/gallery/aspnet-1-0-5-may2017-patch-public (for fixes in the 1.0.x release)
- Feed URL to add to NuGet.config: https://dotnet.myget.org/F/aspnet-1-0-5-may2017-patch-public/api/v3/index.json
https://dotnet.myget.org/gallery/aspnet-1-1-2-may2017-patch-public (for fixes in the 1.1.x release)
- Feed URL to add to NuGet.config: https://dotnet.myget.org/F/aspnet-1-1-2-may2017-patch-public/api/v3/index.json

If you have a chance, please try out these preview builds and let us know if you have any feedback!

Eilon on Apr 19, 2017