Efcore: Selecting navigation properties results in System.InvalidOperationException: Operation is not valid due to the current state of the object

Created on 24 Jul 2019 · 12Comments · Source: dotnet/efcore

Selecting navigation properties into a named class results in the following exception

System.InvalidOperationException: Operation is not valid due to the current state of the object.
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.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.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   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 System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at Software.Controllers.ApplicationStatusController.Crash() in C:\software\src\Software\Controllers\ApplicationStatusController.cs:line 140
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
   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 where exception was thrown ---
   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 where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.ResponseCompression.ResponseCompressionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Steps to reproduce

_context.GlobalSettings.Select(gs => new GlobalSettings
            {
                DefaultCountryFkNavigation = gs.DefaultCountryFkNavigation
            })
            .FirstOrDefault();

Points of importance :

GlobalSettings is a scaffolded Model from EF
Selecting into an anonymous object new { } doesn't throw
Not selecting any navigation properties doesn't throw

Let me know if you need a full repro, but the above code is basically all you need.

Further technical details

EF Core version: 3.0.0-preview7.19365.7
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 x64
IDE: Visual Studio 2019 16.2.0 Preview 4.0

closed-duplicate customer-reported

Source

fschlaef

Most helpful comment

@smitpatel Full repro for the first issue; fails on current nightlies:

```C#
public class GlobalSettings
{
public int Id { get; set; }

public Country DefaultCountryFkNavigation {  get; set; }

}

public class Country
{
public int Id { get; set; }
}

public class BloggingContext : DbContext
{
public DbSet GlobalSettings { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

}

public class Program
{
public static void Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}

    using (var context = new BloggingContext())
    {
        context.GlobalSettings.Select(gs => new GlobalSettings
        {
            DefaultCountryFkNavigation = gs.DefaultCountryFkNavigation
        }).FirstOrDefault();
    }
}

}

Unhandled exception. System.InvalidOperationException: Operation is not valid due to the current state of the object.
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryTResult
at Program.Main() in C:StuffEFCoreNightliesEFCoreNightliesProgram.cs:line 54
```

ajcvickers on 26 Jul 2019

👍3

All 12 comments

I'm getting this error when executing using group by with navigation properties.

Role.cs

 public class Role:IsSoftDelete
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<UserRole> UserRoles { get; set; }
    }

UserRole.cs

  public class UserRole: IsSoftDelete
    {
        public int UserId { get; set; }
        public int RoleId { get; set; }

        public Role Role { get; set; }
        public User User { get; set; }

    }

// If I'm not grouping by g.Role.Name, It works 
var query = _dairyContext.UserRoles
                       .Include(f => f.Role)
                       .GroupBy(g =>
                       new
                       {
                           RoleId = g.RoleId,
                           RoleName = g.Role.Name,
                       }) 
                       .Select(s => new
                       { 
                           RoleId = s.Key.RoleId,
                           TotalUser = s.Sum(c => c.UserId), 
                           RoleName = s.Key.RoleName

                       });
                var result = await query.ToListAsync();

Log

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[3]
      An exception was thrown attempting to execute the error handler.
System.InvalidOperationException: Operation is not valid due to the current state of the object.
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.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__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Further technical details

EF Core version: 3.0.0-preview7.19365.7
Database Provider: Npgsql.EntityFrameworkCore.PostgreSQL
Operating system: Windows 10 x64

It was working fine with preview 5

iAmBipinPaul on 26 Jul 2019

I have a similar error here. The same query works v2.2 with only one query generated and executed.

smartcodinghub on 26 Jul 2019

I have a query that on preview6 was not throwing this exception and now on preview7 that is doing something really similar to the original issue (The original issue looks like a simpler example though).

Sniperhid on 26 Jul 2019

@smitpatel Full repro for the first issue; fails on current nightlies:

```C#
public class GlobalSettings
{
public int Id { get; set; }

public Country DefaultCountryFkNavigation {  get; set; }

}

public class Country
{
public int Id { get; set; }
}

public class BloggingContext : DbContext
{
public DbSet GlobalSettings { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

}

public class Program
{
public static void Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}

    using (var context = new BloggingContext())
    {
        context.GlobalSettings.Select(gs => new GlobalSettings
        {
            DefaultCountryFkNavigation = gs.DefaultCountryFkNavigation
        }).FirstOrDefault();
    }
}

}

ajcvickers on 26 Jul 2019

👍3

@smitpatel Likewise for the second one:
```C#
public class Role
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection UserRoles { get; set; }
}

public class User
{
public int Id { get; set; }
}

public class UserRole
{
public int UserId { get; set; }
public int RoleId { get; set; }

public Role Role { get; set; }
public User User { get; set; }

}

public class BloggingContext : DbContext
{
public DbSet UserRoles { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserRole>().HasKey(e => new { e.UserId, e.RoleId });
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

}

public class Program
{
public static async Task Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}

    using (var context = new BloggingContext())
    {
        var query = context.UserRoles
                               .Include(f => f.Role)
                               .GroupBy(g =>
                               new
                               {
                                   RoleId = g.RoleId,
                                   RoleName = g.Role.Name,
                               })
                               .Select(s => new
                               {
                                   RoleId = s.Key.RoleId,
                                   TotalUser = s.Sum(c => c.UserId),
                                   RoleName = s.Key.RoleName

                               });

        var result = await query.ToListAsync();
    }
}

}

Unhandled exception. System.InvalidOperationException: Operation is not valid due to the current state of the object.
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryTResult
at Program.Main() in C:StuffEFCoreNightliesEFCoreNightliesProgram.cs:line 81
at Program.

()
```

ajcvickers on 26 Jul 2019

Case 1:
```C#
[ConditionalTheory]
[MemberData(nameof(IsAsyncData))]
public virtual Task Navigation_inside_MemberInitExpression_is_expanded(bool isAsync)
{
return AssertQuery(
isAsync,
os => os.Where(o => o.OrderID < 10300).Select(o =>
new OrderDTO
{
Customer = o.Customer
}),
entryCount: 221);
}

    private class OrderDTO
    {
        public Customer Customer { get; set; }
    }

``The custom expression there isEntityReferenceExpression` from EE.
cc: @roji

smitpatel on 27 Jul 2019

Case 2:
C# [ConditionalTheory] [MemberData(nameof(IsAsyncData))] public virtual Task TestingSomething(bool isAsync) { return AssertQuery<Order>( isAsync, os => os.Include(o => o.Customer) .GroupBy(o => new { o.OrderID, o.Customer.City }) .Select(g => new { g.Key.OrderID, C = g.Sum(o => o.OrderID), g.Key.City }), entryCount: 221); }
Navigation inside KeySelector is not expanded from nav expansion hence translation fails.

smitpatel on 27 Jul 2019

Split off the first case (entity equality) to #16789, let's keep this to track the second (navigation).

roji on 27 Jul 2019

@smitpatel Should this be in 3.0?

ajcvickers on 29 Jul 2019

👍2

Duplicate of #15249

smitpatel on 30 Jul 2019

just hit this with 3.0 preview 7 and also 8. was ok in 6. My query is simple though no group by just context.DbSet.Include(d=>d.NavigationCollectionProperty)
using inmemory. Do you want details?