Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Global Query Filters in Entity Framework Core 2.0

DZone's Guide to

Global Query Filters in Entity Framework Core 2.0

With Entity Framework Core 2.0, we now have the ability to introduce global query filters into our code. Learn about what they are, and how and when to use them.

· Integration Zone ·
Free Resource

Your feedback matters—tell Capital One DevExchange what you would do with their Money Movement API.

Entity Framework Core 2.0 introduces global query filters that can be applied to entities when a model is created. It makes it easier to build multi-tenant applications and support soft deleting of entities. This blog post gives a deeper overview of 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 a sample solution EFCoreGlobalQueryFilters on ASP.NET Core 2 that demonstrates global query filters in a more complex context. It demonstrates some ideas about how to apply global query filters to domain entities automatically. SQL-script for creating a simple database and filling it with test data is also there.

What Do Global Query Filters Look Like?

This is how global query filters may look 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 entities of given types are queried.

What Do 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 not just be a 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, and
  • How to automate detecting of entities.

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

Defining Entities

Let’s start with defining some entities. They use a 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 entities.

Tenant Provider

Before talking about multi-tenancy, there must be some way for the web application to detect tenants related to the current request. It can be host header based detection, but it can also be something else. This post uses a 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 at this point that the database is already created and an application is configured to use it. Let’s start with a simple data context that already supports the 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 the base entity type is known. There’s one gotcha – the model is built on every request, but it is not a good idea to scan assemblies every time the model is created. So, type detection must support some kind of caching. These two methods go to the 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 a better idea if there is a separate service that returns entity types. In the 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 a real application, then it's better to go with a separate provider.

Now the 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 a list of entity types and no way to use convenient generic methods directly. In this point, a little tricking is needed. I found a solution from the 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 a generic method call to some generic method that exists in the 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. The SetGlobalQuery method is also a good place to put defining primary keys for entities, as they all inherit from the 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>

The 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 the web application is run, then the following table is shown:

Global query filters: results of global filters

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

Wrapping up

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

Here’s your opportunity to influence experimental APIs. Tell Capital One DevExchange what you think of their new Money Movement API.

Topics:
entity framework core ,data ,query ,filter ,.net ,integration

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}