Global Query Filters for Interfaces

UPDATE: At the bottom is an update that works for EF Core 5.0.2 and above that doesn’t rely on internal interfaces.

This post describes how to apply an Entity Framework Core Global Query filter on all entity types that implement an interface using a strongly typed expression. And why you might want to do that in the first place.

One way to implement a multi-tenant application is to use a discriminator column (aka a tenant_id column on every table). This is a risky proposition. Every query must remember to filter by the tenant_id. One missed query and you expose data from one tenant to another. That’ll get you featured in the next Troy Hunt security fail keynote. You don’t want that.

There are other features that can impact every query. For example, to implement soft deletes, you might have a deleted column on every table. Every query needs to filter on that column.

This is where the Global Query Filter feature of EF Core 2.0 and above comes in handy. If you use NHibernate, you’ve had this feature for a long time.

Color Filters - by Carlos Ebert - CC BY 2.0

Here’s a quick example of a query filter in action. First, we start with the class that’s used in every example ever, the Post class. Someday we’ll be more creative and create an example other than creating a blog engine. Blog engines is so passé.

First, let’s assume we have a Post entity.

public class Post
{
  public int Id { get; set; }
  public string Content { get; set; }
  public bool IsDeleted { get; set; }
}

We add a query filter to the OnModelCreating method of a DbContext derived class.

protected override void OnModelCreating(ModelBuilder builder)
{
  modelBuilder.Entity<Post>()
      .HasQueryFilter(p => !p.IsDeleted);
  //... Probably more code
}

Now every time you query for blog posts, the query only includes posts with IsDeleted set to false.

But your blog engine is the talk of the town. It needs more than just posts, it needs comments and tags. Now your set of query filters look like this.

protected override void OnModelCreating(ModelBuilder builder)
{
  modelBuilder.Entity<Post>()
    .HasQueryFilter(p => !p.IsDeleted);
  modelBuilder.Entity<Comment>()
    .HasQueryFilter(p => !p.IsDeleted);
  modelBuilder.Entity<Tag>()
    .HasQueryFilter(p => !p.IsDeleted);
  //... Probably more code
}

Yuck! That’s starting to get repetitive. And if you add a new entity, you have to remember to add a query filter for that entity.

Will Interfaces Save Us?

But you, you are a smart developer. You see this problem and you think, “I know, I’ll solve it with an interface!”

public interface ISoftDeletable
{
  bool IsDeleted { get; set; }
}

Then you’ll make each of your entities implement this interface. And you’ll rewrite your query filter like so.

protected override void OnModelCreating(ModelBuilder builder)
{
  modelBuilder.Entity<ISoftDeletable>()
    .HasQueryFilter(p => !p.IsDeleted);
    //... Probably more code
}

And you’ll be wrong! This won’t work because EF infers the table to filter based on the type passed in. EF Core requires a query filter for each entity type. Well, it does for the average developer. But you read this blog, so you are above average and you won’t be bound by the limits of mere mortals.

Filtering by Interface

When you set a filter, EF looks at the expression provided and applies it to the entity. For example, in the above example, the expression is p => !p.IsDeleted where p has the type ISoftDeletable. All we have to do is find every type that implements ISoftDeletable and rewrite this expression for each type. Specifically, we need to change the parameter type of this expression for each entity type. Sounds easy right? So how do you rewrite an expression?

Fortunately I found a pretty good answer on StackOverflow on how to replace the parameter type in a lambda expression. I had to make some tweaks to use it for my needs, but here’s the code.

using System;
using System.Collections.ObjectModel;
using System.Linq;
using System.Linq.Expressions;

public static class ExpressionExtensions
{
  // Given an expression for a method that takes in a single parameter (and
  // returns a bool), this method converts the parameter type of the parameter
  // from TSource to TTarget.
  public static Expression<Func<TTarget, bool>> Convert<TSource, TTarget>(
    this Expression<Func<TSource, bool>> root)
  {
    var visitor = new ParameterTypeVisitor<TSource, TTarget>();
    return (Expression<Func<TTarget, bool>>)visitor.Visit(root);
  }
  
  class ParameterTypeVisitor<TSource, TTarget> : ExpressionVisitor
  {
    private ReadOnlyCollection<ParameterExpression> _parameters;

    protected override Expression VisitParameter(ParameterExpression node)
    {
      return _parameters?.FirstOrDefault(p => p.Name == node.Name)
        ?? (node.Type == typeof(TSource) ? Expression.Parameter(typeof(TTarget),  node.Name): node);
    }

    protected override Expression VisitLambda<T>(Expression<T> node)
    {
      _parameters = VisitAndConvert<ParameterExpression>(node.Parameters, "VisitLambda";
      return Expression.Lambda(Visit(node.Body), _parameters);
    }
  }
}

The Convert method accepts an Expression<Func<TSource, bool>>. The Func<TSource, bool> describes a method that receives an argument of type TSource and returns a bool. The same signature as a query filter. It then returns an Expression<Func<TTarget, bool>>.

This is useful to take our Expression<Func<ISoftDeletable, bool>> and convert it to Expression<Func<Post, bool>> and Expression<Func<Comment, bool>> and so on. Let’s write some code to do that.

public static class ModelBuilderExtensions
{
  static void SetQueryFilter<TEntity, TEntityInterface>(
    this ModelBuilder builder,
    Expression<Func<TEntityInterface, bool>> filterExpression)
      where TEntityInterface : class
      where TEntity : class, TEntityInterface
  {
    var concreteExpression = filterExpression
      .Convert<TEntityInterface, TEntity>();
    builder.Entity<TEntity>()
      .HasQueryFilter(concreteExpression);
  }

  // More code to follow...
}

So what this method does is take in our interface based expression and convert it to an expression for the entity type. We could in theory call it like this.

builder.SetQueryFilter<Post, ISoftDeletable>(p => p.IsDeleted);

But we have one problem, here’s the code to retrieve every entity type.

foreach (var type in builder.Model.GetEntityTypes()
  .Select(t => t.ClrType)
  .Where(t => typeof(TEntityInterface).IsAssignableFrom(t)))
{
    // What do we do? This method requires a type known at compile time.
    builder.SetQueryFilter<type, ISoftDeletable>(p => p.IsDeleted)
}

Do you see the problem?

Yes, this won’t compile because SetQueryFilter is a generic method. It expects a known type at compile time. We can’t pass in type as part of the generic signature.

Reflection to the rescue!

static readonly MethodInfo SetQueryFilterMethod = typeof(ModelBuilderExtensions)
  .GetMethods(BindingFlags.NonPublic | BindingFlags.Static)
  .Single(t => t.IsGenericMethod && t.Name == nameof(SetQueryFilter));

We set up a MethodInfo instance so we can invoke SetQueryFilter dynamically. Here’s a helper method to do that.

static void SetEntityQueryFilter<TEntityInterface>(
  this ModelBuilder builder,
  Type entityType,
  Expression<Func<TEntityInterface, bool>> filterExpression)
  {
    SetQueryFilterMethod
      .MakeGenericMethod(entityType, typeof(TEntityInterface))
      .Invoke(null, new object[] { builder, filterExpression });
  }

This method now lets us pass in an entity type at runtime. So we can do this:

var type = typeof(Post);
builder.SetEntityQueryFilter<ISoftDeletable>(type, p => p.IsDeleted);

Now we just need one more method to apply this filter on every entity type that implements our interface.

public static void SetQueryFilterOnAllEntities<TEntityInterface>(
  this ModelBuilder builder,
  Expression<Func<TEntityInterface, bool>> filterExpression)
{
  foreach (var type in builder.Model.GetEntityTypes()
    .Where(t => t.BaseType == null)
    .Select(t => t.ClrType)
    .Where(t => typeof(TEntityInterface).IsAssignableFrom(t)))
  {
    builder.SetEntityQueryFilter<TEntityInterface>(
      type,
      filterExpression);
  }
}

Note the .Where(t => t.BaseType == null) clause here. Query filters may only be applied to the root entity type of an inheritance hierarchy. This clause ensures we don’t try to apply a filter on a non-root type.

And going back to our DbContext derived class, we can invoke this method like so:

protected override void OnModelCreating(ModelBuilder builder)
{
  modelBuilder.SetQueryFilterOnAllEntities<ISoftDeletable>(p => !p.IsDeleted);
  //... Probably more code
}

If you’re interested in seeing the full source code all together, check out this gist.

UPDATE: Aug 19, 2019 There was a subtle bug in the original implementation of this method. If you ran the method twice for different interfaces, and an entity implemented more than one interface, only the last query filter would be applied. For details on why that’s the case, read this EF Core issue. Here’s my comment on the issue noting a scenario where the current behavior is surprising.

Fortunately, @YZahringer posted a workaround that I incorporated into my implementation.

UPDATE: Mar 23, 2021 Thanks to this comment on GitHub by magiak, I now have an implementation that works for EF Core 5.0.2 and above. Just replace AddQueryFilter with AppendQueryFilter below. I updated the gist with the full implementation.

public static void AppendQueryFilter<T>(
    this EntityTypeBuilder<T> entityTypeBuilder, Expression<Func<T, bool>> expression)
    where T : class
{
    var parameterType = Expression.Parameter(entityTypeBuilder.Metadata.ClrType);

    var expressionFilter = ReplacingExpressionVisitor.Replace(
        expression.Parameters.Single(), parameterType, expression.Body);

    if (entityTypeBuilder.Metadata.GetQueryFilter() != null)
    {
        var currentQueryFilter = entityTypeBuilder.Metadata.GetQueryFilter();
        var currentExpressionFilter = ReplacingExpressionVisitor.Replace(
            currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body);
        expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter);
    }

    var lambdaExpression = Expression.Lambda(expressionFilter, parameterType);
    entityTypeBuilder.HasQueryFilter(lambdaExpression);
}

Comments

10 responses

Cristovão Morgado • July 30th, 2019
Quick question: Does this supports .FromSQL ? https://docs.microsoft.com/en-us/ef/core/querying/raw-sql
haacked • July 30th, 2019
@Cristovão Morgado I don’t know. I’m just using the built-in Global Query Filter feature of EF Core. My guess is if your FromSql query is composable, then it will work. But if it’s not composable, then it won’t. For example, if you call a stored procedure using FromSql I don’t see how it could work.

Why don’t you try it out and report back!
Alexandre Jobin • July 31st, 2019
How can you bypass the global filter and include SoftDeleted entities to a specific query?
Haacked • July 31st, 2019
@Alexandre The article I linked to about Global query filters describes how they work: https://docs.microsoft.com/en-us/ef/core/querying/filters

You call theIgnoreQueryFiltersmethod.
amoerie • August 1st, 2019
Great stuff! While frowned upon, the soft delete pattern has saved my skin more than once in my career!

This reminds me of when I had to tackle a bit of a similar problem a few years back, where I wanted to automagically enhance queries made by Entity Framework. I documented the process here, if you’re curious: https://amoerie.wordpress.com/2013/03/09/replacing-method-calls-with-expressions-at-runtime-with-linqkit-and-the-entity-framework/

It’s good to see that EF core has improved in this area by providing some better interception points, but the fact that you still need expression trees for non trivial tasks is unfortunate…

Thanks for sharing!
heroes • November 5th, 2019
How can I do a global query filters with MongoDb .net Driver. It’s not have DbContext to override OnModelCreating. Am I missing something? Thanks
pi • November 18th, 2019
Very good! What if the field names of the entities and the interface do not match? public partial class Post : ISoftDeletable { … public bool IsDeleted { get; set; } }

public partial class Book : ISoftDeletable { … public bool IsArchived { get; set; } bool ISoftDeletable.IsDeleted { get => IsArchived; set => IsArchived = value; } }

Result query for Book will be incorrect. Because Book has no field ‘IsDeleted’.

Any ideas? Is it possible?
Carlos Ebert • April 29th, 2021
This photo is by Carlos Ebert. Give credit!
haacked • May 2nd, 2021
Thanks! I had credit in the alt tag, so if you highlight the image you see it. I’ll make it more prominent.
Rasoul • August 25th, 2021
Thank you for your useful post. Is there any way to send a dynamic value to Global query filter? I mean apply new value to global filter in each scope.