Can I use a separate query plan cache per session?

Question

I have a multi-tenant ASP.NET application, and our database is set up with soft deletes. Initially, we handled the restriction of data directly at the query level, e.g:

var foos = context.Foos.Where(foo => !foo.Deleted && foo.TenantId = currentTenantId).ToList();

As you can imagine, this bloats all of the queries in our data access layer, and makes the API very vulnerable if one forgets to add the correct filter conditions. We have decided to apply global filtering to the context with Z.EntityFramework.Plus.EF6:

public class FooDataContextFactory
{
    public FooDataContext CreateContext()
    {
        var context = new FooDataContext();

        context.Filter<Foo>(collection => collection.Where(foo=> !foo.Deleted));

        var principal = Thread.CurrentPrincipal as ClaimsPrincipal;
        if (principal.HasClaim(claim => claim.Type == "TenantId"))
        {
            var currentTenantId = int.Parse(principal.FindFirst("TenantId").Value);
            context.Filter<Foo>(collection => collection.Where(foo => foo.TenantId == currentTenantId));
        }

        return context;
    }
}

This works perfectly for a single user. However, when you switch tenant, we have issues with the filter expression being saved in the query plan cache. This is a known issue with Entity Framework Plus, and since it doesn't appear to be resolved, I need to find a workaround.

The most immediate solution I can think of is to associate the lifetime of the query plan cache to the current session, and when the user logs out or switches tenant, the cache is destroyed. Is this possible, and if so, how can I achieve this?

Answer 1

I had this exact same problem and tried to work with Z.EntityFramework.Plus.EF6 with the same issues. I found that the zzzprojects team also has EntityFramework.DynamicFilters which works much better for this purpose. The query that is cached is parameterized and the value is injected at runtime using the selector function you provide.

using System.Data.Entity;
using EntityFramework.DynamicFilters;

public class Program
{   
    public class CustomContext : DbContext
    {
        private int _tenantId;

        public int GetTenantId()
        {
            return _tenantId;
        }

        // Call this function to set the tenant once authentication is complete.
        // Alternatively, you could pass tenantId in when constructing CustomContext if you already know it
        // or pass in a function that returns the tenant to the constructor and call it here.
        public void SetTenantId(int tenantId)
        {
            _tenantId = tenantId;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Filter applies to any model that implements ITenantRestrictedObject
            modelBuilder.Filter(
                "TenantFilter",
                (ITenantRestrictedObject t, int tenantId) => t.TenantId == tenantId,
                (CustomContext ctx) => ctx.GetTenantId(), // Might could replace this with a property accessor... I haven't tried it
                opt => opt.ApplyToChildProperties(false)
            );
        }
    }

    public interface ITenantRestrictedObject
    {
        int TenantId { get; }
    }
}