dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.75k stars 3.18k forks source link

Exception thrown for queries that perform filter cased on multiple contains queries for element of different types #3705

Closed nuclearpidgeon closed 8 years ago

nuclearpidgeon commented 8 years ago

I'm building an EF7/MVC5 application at the moment (running beta8 stable on SQLite) with a schema that involves a few join tables/models and am having issues navigating the joins in LINQ.

Quick schema overview:

public class Device {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    ...
    public virtual int? DeviceGroupId { get; set; } // can be null
    public virtual DeviceGroup DeviceGroup { get; set; } // can be null
    ...
}

public class DeviceGroup {
    public int Id { get; set; }
    public string Name { get; set; }
    ...
    public virtual ICollection<UserGroupAccess> UserGroupAccessList{ get; set; }
    public virtual ICollection<Device> Devices { get; set; }
    ...
}

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

    public int UserGroupId { get; set; }
    public virtual UserGroup UserGroup { get; set; }

    public int DeviceGroupId { get; set; }
    public virtual DeviceGroup DeviceGroup { get; set; }

    public UserGroupAccessFlags AccessPermissions { get; set; } //custom enum
}

public class UserGroup {
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<UserGroupMembership> UserMemberships { get; set; }
    public virtual ICollection<UserGroupAccess> UserGroupAccessList { get; set; }
}

public class UserGroupMembership {
    public int Id { get; set; }
    public int UserGroupId { get; set; }
    public virtual UserGroup UserGroup { get; set; }
    public string UserId { get; set; } //'foreign key' to seperate ApplicationIdentity database
}

I am trying to check results in the UserGroupAccess table based off information in the Device and UserGroupMembership tables, which involves basically joining all 5 of the tables. The following code is not working:

IQueryable<UserGroupAccess> accessPoliciesQuery = context.UserGroupAccessList
    .Where(uga =>
        (uga.DeviceGroup.Devices.Select(dev => dev.Id).Contains((int)id))
        &&
        (uga.UserGroup.UserMemberships.Select(um => um.UserId).Contains(currentUser.Id.ToString()))
    );
IList<UserGroupAccess> accessPolicies = accessPoliciesQuery.ToList();

It throws the following stack trace:

error   : [Microsoft.AspNet.Diagnostics.DeveloperExceptionPageMiddleware] An unhandled exception has occurred while executing the request
System.InvalidOperationException: The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'.
   at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.CreateJoinsForNavigations(QuerySourceReferenceExpression outerQuerySourceReferenceExpression, IEnumerable`1 navigations)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.<>c__DisplayClass12_0.<VisitMember>b__0(IEnumerable`1 ps, IQuerySource qs)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.BindMemberExpressionCore[TResult](MemberExpression memberExpression, IQuerySource querySource, Func`3 memberBinder)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.BindNavigationPathMemberExpression[TResult](MemberExpression memberExpression, Func`3 memberBinder)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.VisitMember(MemberExpression memberExpression)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.FromClauseBase.TransformExpressions(Func`2 transformation)
   at Remotion.Linq.QueryModel.TransformExpressions(Func`2 transformation)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.VisitSubQuery(SubQueryExpression subQueryExpression)
   at Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.WhereClause.TransformExpressions(Func`2 transformation)
   at Remotion.Linq.QueryModel.TransformExpressions(Func`2 transformation)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Data.Entity.Query.QueryCompiler.<>c__DisplayClass16_0`1.<CompileQuery>b__0()
   at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.Data.Entity.Query.QueryCompiler.CompileQuery[TResult](Expression query)
   at Microsoft.Data.Entity.Query.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Remotion.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at WebProject.Areas.DeviceManagement.Controllers.DevicesController.<View>d__15.MoveNext() in C:\Users\Stewart\Source\Repos\WebProject\src\WebProject\Areas\DeviceM
anagement\Controllers\DevicesController.cs:line 114

If I comment out the first part of the Where statement (i.e. only use the uga.UserGroup.UserMemberships... test) I get different errors as well

warning : [Microsoft.Data.Entity.Query.QueryCompilationContext] The LINQ expression 'join UserGroup uga.UserGroup in value(Microsoft.Data.Entity.Query.EntityQueryable`1[REAP.Entities.DeviceConfiguration.UserGroup]) on Property([uga], "UserGroupId") equals Property([uga.UserGroup], "Id")' could not be translated and will be evaluated locally.
warning : [Microsoft.Data.Entity.Query.QueryCompilationContext] The LINQ expression '(Property([uga.UserGroup], "UserGroupId") == Property([um], "Id"))' could not be translated and will be evaluated locally.
warning : [Microsoft.Data.Entity.Query.QueryCompilationContext] The LINQ expression 'Contains(__currentUser_Id_0.ToString())' could not be translated and will be evaluated locally.
warning : [Microsoft.Data.Entity.Query.QueryCompilationContext] The LINQ expression '{from UserGroupMembership um in value(Microsoft.Data.Entity.Query.EntityQueryable`1[REAP.Entities.DeviceConfiguration.UserGroupMembership]) join UserGroup uga.UserGroup in value(Microsoft.Data.Entity.Query.EntityQueryable`1[REAP.Entities.DeviceConfiguration.UserGroup]) on Property([uga], "UserGroupId") equals Property([uga.UserGroup], "Id") where (Property([uga.UserGroup], "UserGroupId") == Property([um], "Id")) select [um].UserId => Contains(__currentUser_Id_0.ToString())}' could not be translated and will be evaluated locally.
verbose : [Microsoft.AspNet.Diagnostics.Entity.DatabaseErrorPageMiddleware] System.InvalidOperationException occurred, checking if Entity Framework recorded this exception as resulting from a failed database operation.
verbose : [Microsoft.AspNet.Diagnostics.Entity.DatabaseErrorPageMiddleware] Entity Framework did not record any exceptions due to failed database operations. This means the current exception is not a failed Entity Framework database operation, or the current exception occurred from a DbContext that was not obtained fro
m request services.
error   : [Microsoft.AspNet.Diagnostics.DeveloperExceptionPageMiddleware] An unhandled exception has occurred while executing the request
System.InvalidOperationException: Query source has already been associated with an expression.
   at Remotion.Linq.Clauses.QuerySourceMapping.AddMapping(IQuerySource querySource, Expression expression)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.<>c__DisplayClass58_0.<VisitJoinClause>b__0()
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.OptimizeJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index, Action baseVisitAction, MethodInfo operatorToFlatten, Boolean outerJoin)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.Clauses.JoinClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQueryExpression subQueryExpression)
   at Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Data.Entity.Query.QueryCompiler.<>c__DisplayClass16_0`1.<CompileQuery>b__0()
   at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.Data.Entity.Query.QueryCompiler.CompileQuery[TResult](Expression query)
   at Microsoft.Data.Entity.Query.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Remotion.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at WebProject.Areas.DeviceManagement.Controllers.DevicesController.<View>d__15.MoveNext() in C:\Users\Stewart\Source\Repos\WebProject\src\WebProject\Areas\DeviceM
anagement\Controllers\DevicesController.cs:line 114

Additionally I tried commenting out the second part of the Where statement and mucking around with the first part - as soon as I try and do anything past the DeviceGroup<->Device join, I start getting those Nullable errors.

I'm pretty new to EF so not sure whether I'm doing something wrong here or whether this is a bug - I noticed in the roadmap that Property Navigation is still in progress but I figured it would be worth reporting anyway.

nuclearpidgeon commented 8 years ago

My workaround for the moment is writing the join manually - i.e.

IList<UserGroupAccess> accessPolicies = (
    from uga in dcc.UserGroupAccessList
    join dg in dcc.DeviceGroups on uga.DeviceGroupId equals dg.Id
    join d in dcc.Devices on dg.Id equals d.DeviceGroupId
    join ug in dcc.UserGroups on uga.UserGroupId equals ug.Id
    join ugm in dcc.UserGroupMembershipList on ug.Id equals ugm.UserGroupId
    where ugm.UserId == currentUser.Id && d.Id == id
    select uga)
    .ToList();
maumar commented 8 years ago

This issue is already fixed in the current bits, most likely fixed by 41ad57ad2f3755c5e35b51d939fab52b489ce738

However the repro uncovered another issue. When the repro is run, the following exception is thrown:

Unhandled Exception: System.InvalidOperationException: The binary operator AndAlso is not defined for the types 'System.Int32' and 'System.String'.
   at System.Linq.Expressions.Expression.AndAlso(Expression left, Expression right, MethodInfo method)
   at System.Linq.Expressions.Expression.AndAlso(Expression left, Expression right)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression expression) in D:\k\EntityFramework\src\EntityFramework.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 122
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression) in D:\k\EntityFramework\src\EntityFramework.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 76
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index) in D:\k\EntityFramework\src\EntityFramework.Relational\Query\RelationalQueryModelVisitor.cs:line 699
   at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\k\EntityFramework\src\EntityFramework.Core\Query\EntityQueryModelVisitor.cs:line 528
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\k\EntityFramework\src\EntityFramework.Relational\Query\RelationalQueryModelVisitor.cs:line 253
   at Microsoft.Data.Entity.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\k\EntityFramework\src\EntityFramework.MicrosoftSqlServer\Query\Internal\SqlServerQueryModelVisitor.cs:line 77
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) in D:\k\EntityFramework\src\EntityFramework.Core\Query\EntityQueryModelVisitor.cs:line 158
   at Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel queryModel) in D:\k\EntityFramework\src\EntityFramework.Core\Storage\Database.cs:line 67
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileQuery>b__0() in D:\k\EntityFramework\src\EntityFramework.Core\Query\Internal\QueryCompiler.cs:line 183
   at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in D:\k\EntityFramework\src\EntityFramework.Core\Query\Internal\CompiledQueryCache.cs:line 32
   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.CompileQuery[TResult](Expression query) in D:\k\EntityFramework\src\EntityFramework.Core\Query\Internal\QueryCompiler.cs:line 138
   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in D:\k\EntityFramework\src\EntityFramework.Core\Query\Internal\QueryCompiler.cs:line 84
   at Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in D:\k\EntityFramework\src\EntityFramework.Core\Query\Internal\EntityQueryProvider.cs:line 37
   at Remotion.Linq.QueryableBase`1.GetEnumerator()
maumar commented 8 years ago

Fixed in current bits, producing the following sql:

            SELECT [uga].[Id], [uga].[AccessPermissions], [uga].[DeviceGroupId], [uga].[UserGroupId], [uga.DeviceGroup].[Id], [uga.UserGroup].[Id]
            FROM [UserGroupAccessList] AS [uga]
            INNER JOIN [UserGroup] AS [uga.UserGroup] ON [uga].[UserGroupId] = [uga.UserGroup].[Id]
            INNER JOIN [DeviceGroup] AS [uga.DeviceGroup] ON [uga].[DeviceGroupId] = [uga.DeviceGroup].[Id]
            WHERE @__id_0 IN (
                SELECT [dev].[Id]
                FROM [Device] AS [dev]
                WHERE [uga.DeviceGroup].[Id] = [dev].[DeviceGroupId]
            ) AND @__currentUserId_1 IN (
                SELECT [um].[UserId]
                FROM [UserGroupMembership] AS [um]
                WHERE [uga.UserGroup].[Id] = [um].[UserGroupId]
            )
divega commented 8 years ago

@maumar, was this fixed in RC2 or after we branched? (milestone should reflect that).

maumar commented 8 years ago

@divega it works in release also

divega commented 8 years ago

Cool! Moved to the rc2 milestone.

nuclearpidgeon commented 8 years ago

Thanks all for following this up!