EF Core Filters

Entity Framework core supports Global Query Filter which are a great way to enforce part of a query globally through your app.

Unfortunately, it comes with this note:

It is currently not possible to define multiple query filters on the same entity - only the last one will be applied. However, you can define a single filter with multiple conditions using the logical AND operator (&& in C#).

So, because you have to combine your filters into 1 big filter, this means that turning filters off using .IgnoreQueryFilters() turns off all filters. Which is a little dangerous, if you ask me.

Controlling filters 1 by 1

Wouldn't it be nice if you could turn off filters, one by one, or adjust them at run time. Well, you can, kinda. You just have to remember, that we can't really turn the filters off, so we have to adjust our filter to get the behaviour we want.

Multi-tenanted

We'll start with a simple tenanting filter. This is common across a lot of Saas apps:

In this example, we are building an Ordering system for multiple tenants.

///... order map
builder.HasQueryFilter(o => o.TenantId == DbContext.TenantId);

All good so far. How your TenantId is set on the db context, is beyond the scope of this post, but it will likely be setup early in your request pipeline.

At runtime, we can control this TenantId and override it. Your reasons for doing this are your own, I'm not going to judge you!

A typical query might look like this:

var latestOrders = await _dbContext.Orders
                                   .OrderByDescending(o => o.CreatedOn)
                                   .Take(10)
                                   .ToListAsync();                          

We can control the tenant Id at runtime, in the controller, and scope it with a using block:

using(_dbContext.OverrideTenantIdFilter(newTenantId))
{
    var latestOrders = await _dbContext.Orders
                                       .OrderByDescending(o => o.CreatedOn)
                                       .Take(10)
                                       .ToListAsync();       
}

This will allow us to change the filter that gets used to build the query, but only during our scope. After wards it'll return to it's previous value. Very handy.

We need a couple of things to do this. A method on our DbContext and a new class.

First up, we'll create a new class to handle this for us:

public class TenantIdFilterOverrider : IDisposable
{
    private readonly AppDbContext _dbContext;
    private readonly Guid? _previousTenantIdOverride;

    public TenantIdFilterOverrider(AppDbContext dbContext, Guid overrideId)
    {
        _dbContext = dbContext;

        _previousTenantIdOverride = _dbContext.TenantIdOverride;

        _dbContext.TenantIdOverride = overrideId;
    }

    public void Dispose()
    {
        _dbContext.TenantIdOverride = _previousTenantIdOverride;
    }
}

Then best part about these filters, is the use of the Dispose() method, to return the context back to its previous state when we are done with it.

Next we'll add a method to our DbContext that let's us use this:

public TenantIdFilterOverrider OverrideTenantIdFilter(Guid tenantId)
{
    return new TenantIdFilterOverrider(this, tenantId);
}

We'll take a quick look at how the TenantId property works:

internal Guid? TenantIdOverride { get; set; }

internal Guid TenantId
{
    get
    {
        var tenantId = TenantIdOverride ?? _initialTenantId;

        return tenantId.Value;
    }
}

This allows control over the tenant Id in a scoped way.

Turning a filter off

The above example is great, when you always have a value as part of your filter, but sometimes you might want to turn a filter off.

For example, we may have a second filter as part of our orders filter. Imagine we have a flag against the database to control visibilty of deleted orders:

builder.HasQueryFilter(o => o.TenantId == DbContext.TenantId
                            && DbContext.DeletedFilter.Contains(o.IsDeleted));

Now, this is written in a slightly perculiar way. Remember that I said you cannot really turn the filters off? Well, in order to get a performant query, we need to support 3 states:

• Deleted (1)
• NotDeleted (0)
• Either (nothing)

So, on our DbContext we have a new property, DeletedFilter:

internal IEnumerable<bool> OrderModeFilter
{
    get
    {
        if (DisableDeletedFilter)
        {
            return new []{ false, true };
        }

        return new [] { true };
    }
}

When DisableDeletedFilter is true, this will produce SQL that is something akin to:

select * from Orders
where TenantId = '<GUID>'
and IsDeleted in (0, 1)

or when the filter is not disabled:

select * from Orders
where TenantId = '<GUID>'
and IsDeleted in (1)

This allows SQL to optimise the query in a performant way, without us having to disable all of our filters, and remember to add back on the TenantId filter!

So, just to complete this part, I'll show you how we disable that filter too:

//DbContext.cs
internal bool DisableDeletedFilter { get; set; }

public DeletedFilterDisabler IgnoreDeletedFilter()
{
    return new DeletedFilterDisabler(this);
}

// DeletedFilterDisabler.cs
public class TestModeFilterDisabler : IDisposable
{
    private readonly AppDbContext _dbContext;
    private readonly bool _previousValue;

    public TestModeFilterDisabler(AppDbContext dbContext)
    {
        _dbContext = dbContext;
        _previousValue = _dbContext.DisableTestModeFilter;
        _dbContext.DisableTestModeFilter = true;
    }

    public void Dispose()
    {
        _dbContext.DisableTestModeFilter = _previousValue;
    }
}

using(_dbContext.IgnoreDeletedFilter())
{
    var latestOrders = await _dbContext.Orders
                                       .OrderByDescending(o => o.CreatedOn)
                                       .Take(10)
                                       .ToListAsync();     
}

This works really well with dependency injection, meaning a scoped DbContext may have it's tenant, or filters controlled early on in the pipeline, and the lower levels don't need to care about what filters they are running.