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

Question

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

dougbu opened this issue 7 years ago · 36 comments

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

Answer 1 · 2017-02-24T23:59:26.000Z

/cc @maumar

Answer 2 · 2017-03-07T22:35:19.000Z

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.

Answer 3 · 2017-03-09T09:48:52.000Z

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();

Answer 4 · 2017-03-09T14:37:22.000Z

I have the same error in VS 2017. Temporary solution with automapper work with simple Count, but in my case i have count of group items, somth like this:
var rows = this.context.data
.Select(r => new
{
....... //no matter
Count = r.Entities.GroupBy( x=>x.name ).Count()
}).ToList();

In this case i have same error: '_outer_Id' with anonymous class too. In VS2015 all work fine.

Answer 5 · 2017-03-09T17:31:45.000Z

@maumar It looks like this could be a dupe of or related to #7811 and is probably also a regression from 1.1.0. As Doug originally reported it there was a query compiler error in 1.1.0 and hence we didn't think it was a regression, but from other comments it seems like there were cases that were working in 1.1.0. I'm putting this in 1.1.2 for now, but feel free to close as a dupe or set for re-triage as appropriate.

Answer 6 · 2017-03-09T18:51:13.000Z

I confirm what in 1.1.0 with VS 2017 all work correct

Answer 7 · 2017-03-10T16:42:11.000Z

I'm getting a similar error but I'm selecting an anonymous type. This broke after upgrade to VS 2017 and EF Core 1.1.1.

Answer 8 · 2017-03-14T19:42:08.000Z

Problem was caused by: b8fb258

Answer 9 · 2017-03-15T00:16:28.000Z

@joshmouch can you post your query and model? (entities and contents of OnModelCreating method on the DbContext)

Answer 10 · 2017-03-15T00:33:07.000Z

Fixed in 8dbcab1

Answer 11 · 2017-03-15T00:38:50.000Z

Also opened #7882 to track proper fix in the 2.0.0 that would not introduce unnecessary N+1 queries for those cases.

Answer 12 · 2017-03-15T16:56:35.000Z

@maumar When you say fixed, you mean it will be fixed with the next entity framework update? Do you have a date approximatively? Thanks

Answer 13 · 2017-03-15T18:00:28.000Z

@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).

Answer 14 · 2017-03-15T19:02:07.000Z

@maumar producing inefficient SQL when using DTO as like in your example will be fixed or we consider this as bad practice then? It is quite a surprise for me...

Answer 15 · 2017-03-15T20:13:11.000Z

@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.

Answer 16 · 2017-03-16T09:48:31.000Z

@maumar Thanks for great explanation.

Answer 17 · 2017-03-16T20:31:12.000Z

I'd just like to say that this is a pretty huge deal. I don't think it's enough to say "we'll release a fix in 2.0 sometime in the future."
Probably 50% of my queries are broken because of this, or what I think is the same bug I put in this ticket:
#7915.

Answer 18 · 2017-03-17T07:29:22.000Z

@joshmouch It's the open source project, we're not in a situation to push them to do anything. But you can always contribute and make 2.0 release sooner.

Answer 19 · 2017-03-17T07:50:26.000Z

@szykov My meaning is that I hope this could be made as part of a bug fix rather than a whole new release. This seems pretty big to me for people migrating from existing EF queries.

Answer 20 · 2017-03-17T09:15:07.000Z

@joshmouch as @maumar said they can't fix that bug without changing API. EF supports semantic versioning and breaking change such as changing API means a major change from point of semantic versioning. Because it ruins backward compatibility. Major change means increasing the first number of the version => 2.0.

http://semver.org

Answer 21 · 2017-03-17T14:36:47.000Z

@maumar I'm just guessing here, but is ticket #7915 the same issue? Is using the "let" statements causing the same sort of client evaluation to be performed as projecting to a DTO does? Will 1.1.2 allow "let" statements to be used again?

Answer 22 · 2017-03-22T18:21:31.000Z

I have following query with the same error message:

var test = _repository.AggregateQueryable
    .Select(x => x.Items.Any() 
       ? x.Items.GroupBy(y => y.Priority).Select(g => g.Key).ToArray() 
       : new int[0]);

What is the workaround for this?

Answer 23 · 2017-03-26T17:26:36.000Z

Seems like I also met same error when trying to get some aggregated values in subselect

var results = _context.Persons.Select(s => new PersonVM
{
   PersonId = s.PersonId,
   StaffCount = _context.PersonStuff.Include(i => i.Person).Where(w => w.Person.PersonId == s.PersonId).Count(),
   StaffExist = _context.PersonStuff.Include(i => i.Person).Any(w => w.Person.PersonId == s.PersonId),
});

with models

    public class PersonVM
    {
        [Key]
        [Display(Name = "Id")]
        public int PersonId { get; set; }

        public int StaffCount { get; set; }

        public bool StaffExist { get; set; }
    }

    public class Person
    {
        [Key]
        [Display(Name = "Id")]
        public int PersonId { get; set; }

    }

    public class PersonStuff
    {
        [Key]
        [Display(Name = "Id")]
        public int PersonStuffId { get; set; }

        public Person Person { get; set; }
    }

test project if needed
http://static.realgrad.ru/WebApplication2.zip

Answer 24 · 2017-03-26T17:58:47.000Z

@maumar can you please explain if error I have is related to this error as suggested workaround seems like does not work in one case

_context.Persons.Include(i => i.Level)
.Select(s => new {
PersonId = s.PersonId,
FullName = s.FullName,
DOB = s.DOB,
cnt = _context.CorporatePersons.Include(i => i.Person).Where(a => a.Person.PersonId == s.PersonId).Count(),
});

Error is
System.InvalidCastException: 'Unable to cast object of type '<_GroupJoin>d__264[CETDB.Data.CorporatePerson,CETDB.Data.Person,System.Nullable1[System.Int32],Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[CETDB.Data.CorporatePerson,System.Collections.Generic.IEnumerable1[CETDB.Data.Person]]]' to type 'System.Int32'.'

also

_context.Persons.Include(i => i.Level)
.Select(s => new {
PersonId = s.PersonId,
FullName = s.FullName,
DOB = s.DOB,
isCorporate = _context.CorporatePersons.Include(i=>i.Person).Any(a=>a.Person.PersonId == s.PersonId),
});

generates
InvalidOperationException: Sequence contains no elements

Answer 25 · 2017-03-27T18:01:29.000Z

@dvdobrovolskiy the scenario from your first post is indeed the same problem. The proposed workaround should resolve it though (tested with the model you provided). The second issue is different - it's a compile time error, where the issue in question throws at runtime (due to incorrect SQL being generated). Could you file a new issue for this? Please also provide the model used, as it seems to be different (more complex) than the one you provided.

Answer 26 · 2017-03-27T18:49:41.000Z

New issue #8005

PS
Workaround did help. But First posts is about new project I created to demonstrate issue and I tried to recreate same conditions as my current project. But in my current project (second post and new issue) Workaround do not help me

Answer 27 · 2017-03-28T00:11:55.000Z

@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)

Answer 28 · 2017-03-28T02:58:20.000Z

I agree that this is a pretty serious bug - especially considering VS 2017 is a one-way upgrade. Can we grab a nightly build that includes this fix anywhere?

Answer 29 · 2017-03-29T20:53:43.000Z

@mb2140 we are planning to provide a feed with the patch fixes, however cureently we don't know when that will be available. For the time being you have to clone the patch repo (https://github.com/aspnet/EntityFramework/tree/rel/1.1.2) and build it manually.

Answer 30 · 2017-04-12T17:16:04.000Z

This patch fix is approved. Please follow the normal code review / pull request process and make sure you make the change in the correct branch.

Answer 31 · 2017-04-19T18:57:34.000Z

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!

Answer 32 · 2017-04-23T19:38:51.000Z

Patch did`nt helped with #8233
Still have grouping issue

Answer 33 · 2017-04-23T19:44:23.000Z

@dvdobrovolskiy - The probable fix for #8233 is not in patch and will be released in 2.0.0-preview1

Answer 34 · 2017-04-23T19:50:50.000Z

Year, may the 10th. Waiting. Thanks!

Answer 35 · 2017-04-24T05:53:42.000Z

@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

Answer 36 · 2017-04-24T15:47:42.000Z

@andershermansen that's great, thank you so much for trying it out!