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.

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

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.