I have two expressions that I'm using for a global query filter on my entity project.

I have the following compile time one:

Expression<Func<RandomTable, bool>> test = c => AuthorisedUsers.Any(b => b.RandomTableId == c.RandomTableId && b.UserId == UserId);

AuthorisedUsers is a List which contains a simple structure of a userid and randomtableid to ensure people who are accessing RandomTable have been authorised in the authorisedusers table which is a 1-m table. A user can have access to many RandomTables

The above expression creates the following SQL which is perfect

SELECT *
FROM [RandomTable] AS [h]
WHERE EXISTS (
    SELECT 1
    FROM [AuthorisedUsers] AS [p0]
    WHERE ([p].[ID] = [p0].[RandomTableId]) AND ([p0].[UserId] = @__userId_0))

But then I have created the same expression at runtime using a lambdaexpression but for every table in my dbcontext. However the same expression then creates this sql instead:

SELECT *
FROM [RandomTable] AS [h]
WHERE EXISTS (
 SELECT 1               <-- Why are we querying this to then query it again below?
    FROM [AuthorisedUsers] AS [p] <-- 
    WHERE EXISTS (
        SELECT 1
        FROM [AuthorisedUsers] AS [p0]
        WHERE ([p0].[RandomTableId] = [p].[RandomTableId]) AND ([p0].[UserId] = @__ef_filter__GetUserId_1)) AND (([p].[RandomTableId] = [h].[RandomTableId]) AND ([p].[UserId] = @__ef_filter__GetUserId_0)))

As we can see on the above query it seems to pointlessly query the AuthorisedUsers to then query it again and then apply the correct where clause. I can't figure out why the lambda expression used by entity would translate into a unneeded sub select.

If I examine the expression of both these expressions in the debugger(debug view for both expressions) we can see their identical except for one minor difference but I don't believe it would have this effect? I have put 2** next to the underlying differences that I can see but aside from that it seems to be it should 100% generate the exact same SQL?

Appreciate any help on this!

---------------------BAD SQL Debug Results--------------------

Debug Evaluation:

{x => value(DbContext).AuthorisedUsers.Any(b => ((b.RandomTableId == x.RandomTableId) AndAlso (b.UserId == value(DbContext).UserId)))}  **System.Linq.Expressions.LambdaExpression {System.Linq.Expressions.Expression1<System.Func<Entities.RandomTable, bool>>}**

DebugView:

 .Lambda #Lambda1<System.Func`2[Entities.RandomTable,System.Boolean]>(Entities.RandomTable$x)
    {
        .Call System.Linq.Queryable.Any(
            .Constant<DbContext>(DbContext).AuthorisedUsers,
            '(.Lambda #Lambda2<System.Func`2[Entities.AuthorisedUsers,System.Boolean]>))
    }

.Lambda #Lambda2<System.Func`2[Entities.AuthorisedUsers,System.Boolean]>(Entities.AuthorisedUsers$b)
{
    $b.RandomTableId == $x.RandomTableId && $b.UserId == .Constant<DbContext>(DbContext).UserId
}

---------------------Good SQL Debug Results--------------------

Debug Evaluation:

 {c => value(DbContext).AuthorisedUsers.Any(b => ((b.RandomTableId == c.RandomTableId) AndAlso (b.UserId == value(DbContext).UserId)))} **System.Linq.Expressions.Expression<System.Func<Entities.RandomTable, bool>> {System.Linq.Expressions.Expression1<System.Func<Entities.RandomTable, bool>>}**

DebugView:

 .Lambda #Lambda1<System.Func`2[Entities.RandomTable,System.Boolean]>(Entities.RandomTable $c)
    {
        .Call System.Linq.Queryable.Any(
            .Constant<DbContext>(DbContext).AuthorisedUsers,
            '(.Lambda #Lambda2<System.Func`2[Entities.AuthorisedUsers,System.Boolean]>))
    }
    
    .Lambda #Lambda2<System.Func`2[Entities.AuthorisedUsers,System.Boolean]>(Entities.AuthorisedUsers $b)
    {
        $b.RandomTableId == $c.RandomTableId && $b.UserId == .Constant<DbContext>(DbContext).UserId
    }

Expressions are applied like so in dbcontext:

      foreach (Microsoft.EntityFrameworkCore.Metadata.IMutableEntityType entityType in modelBuilder.Model.GetEntityTypes().Where(et => et.BaseType == null))
            {
//Done like this to compare the same expression(hand built vs runtime) //in debugger
       if (entityType.ClrType == typeof(RandomTable))
                    {
                        entityType.SetQueryFilter(test);
                    }
                    else
                    {
                    entityType.SetQueryFilter(testruntimeversion);
                    }

}

If we use the expression visualizer debug tool it translates BOTH expressions to the same how does this make any sense?!

(Entities.RandomTable c) => ProviderAccess.Any<Entities.AuthorisedUsers>(

    // Quoted to induce a closure:
    (Entities.AuthorisedUsers _) => (b.RandomTableId == c.RandomTableId) && (b.UserId == UserId))

Bad expression creation:

 //The below Expression Tree is equiv to the lambda:
                Expression<Func<RandomTable, bool>> test = c => AuthorisedUsers.Any(b => b.RandomTableId == c.RandomTableId && b.UserId == GetUserId);

            ParameterExpression b = Expression.Parameter(typeof(AuthorisedUsers), "b");
            ParameterExpression x = Expression.Parameter(entityType.ClrType, "x");

            MethodInfo anymethod = typeof(Queryable).GetMethods().Single(mi => mi.Name == "Any" && mi.GetParameters().Length == 2).MakeGenericMethod(typeof(AuthorisedUsers));
            MemberExpression localUserId = Expression.MakeMemberAccess(Expression.Constant(this), typeof(DbContext).GetProperty(nameof(GetUserId), BindingFlags.Instance | BindingFlags.Public));

            MemberExpression getAuthorisedUsers = Expression.MakeMemberAccess(
                        Expression.Constant(this), typeof(DbContext).GetProperty(nameof(AuthorisedUsers)));

            MemberExpression randomtableid = Expression.MakeMemberAccess(b, typeof(AuthorisedUsers).GetProperty("RandomTableId"));

            MemberExpression userid = Expression.MakeMemberAccess(b, typeof(AuthorisedUsers).GetProperty("UserId"));

            BinaryExpression randomTableIdmatch = Expression.Equal(randomtableid,
                                     Expression.Convert(Expression.MakeMemberAccess(x, entityType.ClrType.GetProperty(propertyName)), typeof(int)));

            LambdaExpression g = Expression.Lambda(Expression.Call(anymethod, getAuthorisedUsers,
                                   Expression.Quote(
                                       Expression.Lambda(Expression.AndAlso(randomTableIdmatch,
                                       Expression.Equal(userid, localUserId)
                                   ), b)
                           )
                       ),
                       x
                   );

entityType.SetQueryFilter(g);

CodePudding user response：

Your bad expression tree uses LambdaExpression which is the base type for Expression<TDelegate> (used by your good expression tree). LambdaExpression represents a lambda expression which isn't strongly typed, unlike Expression<TDelegate>.

If you want your constructed expression tree to have Expression<TDelegate> you should use one of the overloads of Expression.Lambda which takes a delegate type.

NB. The difference is not visible in DebugView, but see here for DebugView syntax