Using EF Core HasQueryFilter on navigation properties

Question

I'm trying to apply a filter to my queries for multi-tenancy but it doesn't allow me to apply the filter when the property is part of a navigation property:

modelBuilder.Entity().HasQueryFilter(lvl => lvl.SchoolYear.TenantId == _tenantProvider.TenantId);

Here, Level is the property I want filtered but the property to use for filtering which is TenantId is is inside Level.SchoolYear.

If its a top-level property it works fine. How can I apply a global filter when the property I need for filtering is a navigation property?

user_861716 · Answer

As said, these model level filters are not (yet) implemented for navigation properties. Which is sad, because people may not be aware of this restriction and count on it in a multi-tenant architecture. Everybody knows that making errors in a multi-tenant application may be the death of your company, so it's pretty vital to do this right.

Once you've chosen for tenant separation by TenantId (and not the safer schema-per-tenant or database-per-tenant), and knowing this current restriction in EF-core, you may want to build in a safeguard that at least you'll never mix up tenant data when saving changes.

This safeguard could be implemented by defining an interface:

public interface IHasTenantId
{
    int TenantId { get; }   
}

... to be implemented by each entity having a TenantId.

Then in an override of SaveChanges in the DbContext subclass there can be a check if all TenantIds in a change set are identical:

public override int SaveChanges()
{
    var distinctTenantIdsCount = this.ChangeTracker.Entries()
                                     .Select(e => e.Entity.TenantId)
                                     .Distinct().Count();
    if(distinctTenantIdsCount > 1)
    {
        // Throw an exception and handle it.
    }
    return base.SaveChanges();
}

user_5202563 · Answer

You can't. It's a documented limitation of the current (as of EF Core 2.0.1) Global Query Filters implementation:

Filters cannot contain references to navigation properties.

It's tracked by #8881: Allow to use navigations in model level entity filters enhancement request, but unfortunately has no concrete milestone assigned, which means we don't know if and when it will be fixed.

Using EF Core HasQueryFilter on navigation properties

2 Answers