Global query filters in Entity Framework Core 2.0

Entity Framework Core 2.0 introduces global query filters that can be applied to entities when model is created. It makes it easier to build multi-tenant applications and support soft deleting of entities. This blog post gives deeper overview about how to use global query filters in real-life applications and how to apply global query filters to domain entities automatically.

Sample solution. I built sample solution EFCoreGlobalQueryFilters on ASP.NET Core 2 that demonstrates global query filters in more complex context. It demonstrates some ideas about how to apply global query filters to domain entities automatically. SQL-script for creating simple database and fill it with test data is also there.

How global query filters look like?

This is how global query filters may look like for soft delete. This override for OnModelCreating method of DbContext class.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Playlist>().HasKey(e => e.Id);
    modelBuilder.Entity<Playlist>().HasQueryFilter(e => !e.IsDeleted);
    modelBuilder.Entity<Song>().HasKey(e => e.Id);
    modelBuilder.Entity<Song>().HasQueryFilter(e => !e.IsDeleted);

    base.OnModelCreating(modelBuilder);
}

These filters are applied always when entites of given types are queried.

What real applications need?

The code above is simplified and doesn’t consider real-life scenarios. When considering mission critical applications that are part of the digital core or enterprises then there will be not just couple of classes. Although the architecture of applications is often complex. The goal of this post is to demonstrate the following:

  • how to support multi-tenancy,
  • how to support soft deleting of entities,
  • how to automate detecting of entitites.

Sample solution helps to get started with more complex scenarios but it doesn’t provide fully flexible and complex framework for this. There are just too many nyances involved when it comes to real-life applications and every application has usually it’s own set of solutions for different problems.

Defining entities

Let’s start with defining some entities. They use simple base class and it is expected that all entities extend from the base class.

public abstract class BaseEntity
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public bool IsDeleted { get; set; }
}

public class Playlist : BaseEntity
{
    public string Title { get; set; }

    public IList<Song> Songs { get; set; }
}

public class Song : BaseEntity
{
    public string Artist { get; set; }
    public string Title { get; set; }
    public string Location { get; set; }
}

Now there are some simple entities and it’s time to make next steps towards multi-tenancy and soft deleted entitites.

Tenant provider

Before talking about multi-tenancy there must be some way for web application to detect tenant related to current request. It can be host header based detection but it can also be something else. This post uses dummy provider to keep things simple.

public interface ITenantProvider
{
    Guid GetTenantId();
}

public class DummyTenantProvider : ITenantProvider
{
    public Guid GetTenantId()
    {
        return Guid.Parse("069b57ab-6ec7-479c-b6d4-a61ba3001c86");
    }
}

This provider must be registered in ConfigureServices method of Startup class.

Creating data context

I expect in this point that database is already created and application is configured to use it. Let’s start with simple data context that already supports tenant provider.

public class PlaylistContext : DbContext
{
    private Guid _tenantId;
    private readonly IEntityTypeProvider _entityTypeProvider;

    public virtual DbSet<Playlist> Playlists { get; set; }
    public virtual DbSet<Song> Songs { get; set; }

    public PlaylistContext(DbContextOptions<PlaylistContext> options,
                            ITenantProvider tenantProvider)
        : base(options)
    {
        _tenantId = tenantProvider.GetTenantId();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Playlist>().HasKey(e => e.Id);
        modelBuilder.Entity<Song>().HasKey(e => e.Id);

        base.OnModelCreating(modelBuilder);
    }      
}

With data context working and tenant ID available it’s time to make next step towards automatically created global query filters.

Detecting entity types

Before adding global query filters for all entity types, the entity types must be detected. It’s easy to read these types if base entity type is known. There’s one gotcha – model is built on every request but it is not good idea to scan assemblies every time when model is created. So, type detection must support some kind of caching. These two methods go to data context class.

private static IList<Type> _entityTypeCache;
private static IList<Type> GetEntityTypes()
{
    if(_entityTypeCache != null)
    {
        return _entityTypeCache.ToList();
    }

    _entityTypeCache = (from a in GetReferencingAssemblies()
                        from t in a.DefinedTypes
                        where t.BaseType == typeof(BaseEntity)
                        select t.AsType()).ToList();

    return _entityTypeCache;
}

private static IEnumerable<Assembly> GetReferencingAssemblies()
{
    var assemblies = new List<Assembly>();
    var dependencies = DependencyContext.Default.RuntimeLibraries;

    foreach (var library in dependencies)
    {
        try
        {
            var assembly = Assembly.Load(new AssemblyName(library.Name));
            assemblies.Add(assembly);
        }
        catch (FileNotFoundException)
        { }
    }
    return assemblies;
}

Warning! Architecture-wise it could be better idea if there is separate service that returns entity types. In code above it is possible to use entity types variable directly and what’s even worse – it is possible to call GetReferencingAssemblies method. If you write real application then better go with separate provider.

Now data context knows entity types and it’s possible to write some code to get query filters applied to all entities.

Applying query filters to all entities

It sounds like something easy to do but it’s not. There’s list of entity types and no way to use convenient generic methods directly. In this point a little tricking is needed. I found solution from CodeDump page EF-Core 2.0 Filter all queries (trying to achieve soft delete). The code there is not usable as it is as the data context here has instance level dependency to ITenantProvider. But the point remains the same: let’s create generic method call to some generic method that exists in data context.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var type in GetEntityTypes())
    {

        var method = SetGlobalQueryMethod.MakeGenericMethod(type);
        method.Invoke(this, new object[] { modelBuilder });
    }

    base.OnModelCreating(modelBuilder);
}

static readonly MethodInfo SetGlobalQueryMethod = typeof(PlaylistContext).GetMethods(BindingFlags.Public | BindingFlags.Instance)
                                                        .Single(t => t.IsGenericMethod && t.Name == "SetGlobalQuery");

public void SetGlobalQuery<T>(ModelBuilder builder) where T : BaseEntity
{
    builder.Entity<T>().HasKey(e => e.Id);
    //Debug.WriteLine("Adding global query for: " + typeof(T));
    builder.Entity<T>().HasQueryFilter(e => e.TenantId == _tenantId && !e.IsDeleted);
}

It’s not easy and intuitive code. Even I stare my eyes out when looking at this code. Even when I look at it hundred times it still looks crazy and awkward. SetGlobalQuery method is also good place to put defining primary key for entities as they all inherit from same base entity class.

Test drive

To try out how global query filters work it’s possible to use HomeController of sample application.

public class HomeController : Controller
{
    private readonly PlaylistContext _context;

    public HomeController(PlaylistContext context)
    {
        _context = context;
    }

    public IActionResult Index()
    {
        var playlists = _context.Playlists.OrderBy(p => p.Title);

        return View(playlists);
    }
}

I modified default view to display all playlists that query returns.

@model IEnumerable<Playlist>

<div class="row">
    <div class="col-lg-8">
        <h2>Playlists</h2>

        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Playlist</th>
                    <th>Tenant ID</th>
                    <th>Is deleted</th>
                </tr>
            </thead>
            <tbody>
                @foreach(var playlist in Model)
                {
                    <tr>
                        <td>@playlist.Title</td>
                        <td>@playlist.TenantId</td>
                        <td>@playlist.IsDeleted</td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</div>

Web application is now ready for running. Here is the sample data I’m using. Let’s remember that tenant ID used by sample application is 069b57ab-6ec7-479c-b6d4-a61ba3001c86.

Global query filters: data in playlists table

When web application is run then the following table is shown.

Global query filters: results of global filters

When comparing these two tables then it is easy to notice that global query filters work and give expected results.

Wrapping up

Global query filters are nice addition to Entity Framework Core 2.0 and until there are not many enitites then it’s possible to go with simple examples given in documentation. Fore more complex scenarios some tricky code is needed to apply global query filters automatically. Hopefully there will be some better solution for this in future but currently the solution given here makes excellent work too.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    22 thoughts on “Global query filters in Entity Framework Core 2.0

    • August 21, 2017 at 9:03 am
      Permalink

      Hi Gunnar,
      I read your blog post but don’t understand the need for that complexity. I use following pattern in an Offline Sync (TableController) app.
      1. Get the tenent Id from the HttpUser
      2. Get an IQueryable instance from EF DbContext like. dbcontext.Table1
      3. Apply a Filter on that IQueryable instance like: dbcontext.Table1.Where(r => r.tenent == httpuserTenent && !r.Deleted )

      best
      Eric

    • August 21, 2017 at 10:03 am
      Permalink

      Hi Eric,
      Thanks for question. Your solution is to apply filter on specific query. My solution is to avoid this situation as developers may easily forget that these conditions must be added to all queries they make. Solution proposed here applies the same rules for all domain entities and developers doesn’t need to worry about the global rules.

    • August 22, 2017 at 4:52 pm
      Permalink

      Hi Gunner,
      But your code only works well when the TenentId exists in each table of the database schema (DDL), which is not always the case.

    • August 23, 2017 at 9:13 am
      Permalink

      Hi Eric,
      Databases behind multi-tenant systems often have tenant ID available in every table and it’s part of primary key. Just to have the one last safety net to avoid situations where data from one tenant gets mistakenly related to data from another tenant.

      It is usually not so much related with front-end applications but with background services that use multiple threads to process data from different tenants. I have seen mistakes made in these services and results are sad. It’s very hard to detect issues that happen only once per million run. And it’s not easy to clean up the mess when users of one customers get notifications about things in another tenant where they have no access. It’s not the perfect solution, of course, and we may have long discussion about its pros and cons but this is how things are often done. At least after first painful fails.

      One thing that comes to my mind in code level is using base class for data contexts and override SaveChanges and SaveChangesAsync methods. Before any changes there is check for tenant ID-s and if they are not all the same then all changes are rolled back and exception is thrown.

    • October 31, 2017 at 11:09 am
      Permalink

      I have a situation. I need to check each query that goes into the database, and replace some characters with other characters. I mean, I look at the query as a piece of text, and I replace say “(” with “paranthesis::”.

      I could do that in EF6 with interceptors. I would get the query before going into the database, and I would change the characters, and then assign it back to be sent along the pipeline to the database.

      I’m trying to achieve the same thing with EF Core 2.0’s Global Filters, and I think that they should be able to do that. But I can’t find how.

      Is it possible?

    • May 26, 2018 at 5:15 pm
      Permalink

      Hi Gunnar,

      Great stuff! Even as a ‘casual programmer’, I can follow your logic.
      I’m trying to replicate your example but I get stuck on how to register ITenantProvider in ConfigureServcies. Without registering I get an error in my context stating ‘Inconsistent accessibility’. Furthermore I get an ‘could not be found’-error on IEntityTypeProvider. Normally a suggestion for an assembly reference is done in VS, but not this time. Sorry to bother you with these undoubtedly mundane questions, but I would greatly appreciate your support.

    • May 28, 2018 at 9:22 am
      Permalink

      As mentioned OnModelCreating is executed once, what can be workaround to use with values from Session/Claims to get the tenanid? Any suggestions?

      Thanks

    • June 15, 2018 at 12:27 pm
      Permalink

      Hi Gunnar,

      Many Kudos for your solutions, but I still cannot get access to User.Name through the IHttpContextAccessor. I would like to check if an authenticated user has a TenantId and use that TenantId as a filter. I’ve posted the question on SO as well, but maybe you could take a look as well. https://stackoverflow.com/questions/50871200/cannot-get-user-name-from-httpcontext-in-net-core-di-does-not-solve-it

      Many thanks!

      https://stackoverflow.com/questions/50871200/cannot-get-user-name-from-httpcontext-in-net-core-di-does-not-solve-it

    • January 23, 2019 at 2:20 am
      Permalink

      I have the same challenge as Charles and Ammar. TenantId is set per user (ASPNET Identity) and so I need to apply queryfilter based on the user’s tenant.

    • June 12, 2019 at 6:15 am
      Permalink

      Thanks for the article. Is there a way to put include in a global query filter?

      I have a class Member. members are technically Users with role “member”

      dbContext.members.all() should run:

      select * from users inner join user_roles on users.id = user_roles.user_id where user_roles.name = ‘member’.

      can I do this using some sort of query filter in dbcontext file?

      Thanks

    • November 27, 2019 at 1:59 pm
      Permalink

      thanks a lot ,this helps a lot

    • Pingback:مقدمة إلى Multitenancy أو ما يعرف بـ Multi-Tenants – ASP.Net

    • February 18, 2020 at 9:39 am
      Permalink

      Thanks for information and link to bug tracker!

    • May 13, 2020 at 10:48 am
      Permalink

      hi gunnar , you have written a awsome series here. im trying them one by one. in the mean time what do u think
      1) follow this article and write similar type of Multitenant service on my own app?
      2) did you find any library that is free, open source but in the mean time – light and something which i can inject into my own app. im using aspentcore and postgresql database.
      Thank you very much. again would like to thanks for such an awesome series of artcile.

    • May 13, 2020 at 11:28 am
      Permalink

      Hi Dave,

      Sure you can use my articles to build your own multitenant service or app. This is what I wrote these posts :)

      I have not heard about lightweight multitenant libraries for .NET Core. There are frameworks that support it and that come with all kind of building blocks but I can’t consider these as something lightweight.

    • October 23, 2020 at 9:50 pm
      Permalink

      Hi Gunnar,
      I am trying to implement a global Query filter. I am using “ITenantEntity” Interface instead of an abstract class “BaseEntity”, because I want to be able to set [Display(Name=””)] for the Id for some Entities that are different.

      I changed GetEntityTypes() and replaced
      “where t.BaseType == typeof(BaseEntity)”
      with
      “where t.GetInterface(“ITenantEntity”) != null”

      Thanks for the blog series.

      But I get a System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types.

    • October 27, 2020 at 7:24 am
      Permalink

      Try to use full type name with GetInterface(). It seems like at least namespace is required (if ITenantEntity is not located in another assembly).

    Leave a Reply

    Your email address will not be published. Required fields are marked *