Entity Framework is parameterizing my queries to leverage caching

Question

I'm working in .Net Core 2.1, creating an application which uses multitenancy. I'm applying default filters to my context. However, Entity Framework is not properly leveraging parametrized queries.

I have a configuration options being passed to my context to apply constraints which look like so:

public class ContextAuthorizationOptions : DbAuthorizationOptions<AstootContext>
{
    protected IUserAuthenticationManager _userManager;
    protected int _userId => this._userManager.GetUserId();

    public ContextAuthorizationOptions(IUserAuthenticationManager authenticationManager, IValidatorProvider validatorProvider) 
        : base(validatorProvider)
    {
        this._userManager = authenticationManager;

        ConstraintOptions.SetConstraint<Message>(x => x.Conversation.ConversationSubscriptions
                                         .Select(cs => cs.UserId)
                                         .Any(userId => userId == this._userId));
    }        
}

As you can see my query uses a property to store the userId value. My context takes in the constraint options ad applies them OnModels creating like so:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var constraintOptions = this._authorizationOptions.ConstraintOptions;
    constraintOptions.ApplyStaticConstraint(modelBuilder);
    base.OnModelCreating(modelBuilder);
}

My Model options look like so:

protected List<Action<ModelBuilder>> _constraints = new List<Action<ModelBuilder>>();

public void SetConstraint<T>(Expression<Func<T, bool>> constraint)
    where T: class
{
    this._constraints.Add(m => m.Entity<T>().HasQueryFilter(constraint));
}

public void ApplyStaticConstraint(ModelBuilder modelBuilder)
{
    foreach(var applyConstraint in this._constraints)
    {
        applyConstraint(modelBuilder);
    }
}

Since my filters are using properties I would expect this to generate a parameterized query yet when dumping to messages table to list it generates this SQL

SELECT [x].[Id], [x].[ConversationId], [x].[Created], [x].[MessageText], [x].[SenderUserId]
FROM [Messages] AS [x]
INNER JOIN [Conversations] AS [x.Conversation] ON [x].[ConversationId] = [x.Conversation].[Id]
WHERE EXISTS (
    SELECT 1
    FROM [ConversationSubscriptions] AS [cs]
    WHERE ([cs].[UserId] = 2005) AND ([x.Conversation].[Id] = [cs].[ConversationId]))

How can I modify my implementation so Entity Framework Core can leverage query caching?

Answer 1

By some reason that only EF Core designers can explain, the query filter expressions are treated differently than the other query expressions. In particular, all variables which are not rooted to the target db context are evaluated and converted to constants. Rooted term has evolved from simply direct field/property of the context to more relaxed rules explained in #10301: Query: QueryFilter with EntityTypeConfiguration are failing to inject current context values Design meeting notes:

Patterns of configuration which would capture context correctly and inject current instance values

• Defining filter in OnModelCreating
• Defining filter in EntityTypeConfiguration by passing context through constructor
• Defining filter using method (inside/outside DbContext or extension method) where context is passed as parameter. Any of above where context is wrapped inside another object type and that type is being passed around.

Apart from above we will parametrize any kind of call on DbContext i.e. property/field access, method call, going through multiple levels.

The bullet #3 ("Defining filter using method (inside/outside DbContext or extension method) where context is passed as parameter.") leads me to a relatively simple generic solution.

Add the following simple class:

public static class Filter
{
    public static T Variable<T>(this DbContext context, T value) => value;
}

Modify your options class like so:

protected List<Action<ModelBuilder, DbContext>> _constraints = new List<Action<ModelBuilder, DbContext>>();

public void SetConstraint<T>(Func<DbContext, Expression<Func<T, bool>>> constraint)
    where T : class
{
    this._constraints.Add((mb, c) => mb.Entity<T>().HasQueryFilter(constraint(c)));
}

public void ApplyStaticConstraint(ModelBuilder modelBuilder, DbContext context)
{
    foreach (var applyConstraint in this._constraints)
    {
        applyConstraint(modelBuilder, context);
    }
}

the SetConstraint call like so (note wrapping the this._userId into Variable method call):

ConstraintOptions.SetConstraint<Message>(c => x => x.Conversation.ConversationSubscriptions
    .Select(cs => cs.UserId)
    .Any(userId => userId == c.Variable(this._userId)));

and finally the ApplyStaticConstraint call:

constraintOptions.ApplyStaticConstraint(modelBuilder, this);

Now the query will use parameter instead of a constant value.