Entity Framework's model caching makes it useless with large amounts of database schemas

Question

I work with a SaaS product that has a somewhat large user base. Up until now our approach to isolating customer data has been to have customer specific databases. This has worked great with Entity Framework 6 as all we need to do is pass a customer specific connection string to DbContext and everything works perfectly.

For reasons irrelevant to this question, we need to move away from this one database per customer model. From data isolation perspective, having one database schema per customer instead of one database per customer seemed like a good idea. After doing some tests, it seems it is pretty much unusable when we are talking about large numbers of different schemas.

Here's a simplified example on how we currently use DbContext:

public class CustomDbContext : DbContext

    public CustomDbContext(IConnectionStringProvider provider)
        : base(provider.ConnectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new SomeEntityMap());
        modelBuilder.Configurations.Add(new SomeOtherEntityMap());
    }
}

And here is an example on how we thought it could work:

public class CustomDbContext : DbContext, IDbModelCacheKeyProvider

    public CustomDbContext(IConnectionStringProvider provider)
        : base(provider.ConnectionString)
    {
        CacheKey = provider.Schema;
    }

    public string CacheKey { get; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(CacheKey);
        modelBuilder.Configurations.Add(new SomeEntityMap());
        modelBuilder.Configurations.Add(new SomeOtherEntityMap());
    }
}

Microsoft has been kind enough to allow a way around default caching of database model. Using the schema name as cache key forces Entity Framework to create a new model for each schema. In theory this works. In practice, not really. I created a test app that makes requests to a service that causes the DbContext to be instantiated. It randomizes the CacheKey from a group of 5000 keys, so basically when the app is first started, pretty much each request causes OnModelCreating() to be called. After a few hundred requests the IIS Worker process had eaten all available memory (was using around 9 GB), CPU usage was close to 100% and the service pretty much stalled.

I've looked at Entity Framework source codes and was hopeful that using an empty string with model builder's HasDefaultSchema() would make EF to use the database user's default schema. We could then cache just one model and have the schema "defined in connection string" by setting a default schema to each customer's database credentials. However, EF throws an exception if the schema is an empty string.

So the question is, has anyone stumbled into the same problem and if so, how did you solve it? If the solution is to just fork Entity Framework, I would appreciate any insight on how extensive the required changes are.

Rockfish

The problem with "" schema in SqlServer provider is that the provider uses "CodeFirstDatabase" as schema when generating SQL commands rather than schema-less table names. I don't see a good solution. Eventually you could try adding db command interceptor which replaces "[CodeFirstDatabase]." with "" inside DbCommand.CommandText – Cole

Yes, I noticed something related to CodeFirstDatabase in the exception we got when using empty schema name. I'll dig around a bit and see if it would be feasible to intercept the command. – Rockfish

Answer 1

Thank you Ivan Stoev for pointing me to the right direction. An interceptor was absolutely the easiest way to overcome this problem. Tested with 1000 consecutive requests and there is no noticeable effect on execution times when using the interceptor. This approach won't work with EF Migrations without additional work, but as we don't use it it's not a problem.

EDIT: Did some fixes to example

Here is an example on what seems to do the trick:

public class CustomDbContext : DbContext
{
    static CustomDbContext()
    {
        Database.SetInitializer<CustomDbContext>(null);
        DbInterception.Add(new SchemaInterceptor());
    }

    public CustomDbContext(IConnectionStringProvider provider)
        : base(provider.ConnectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("RemoveThisDefaultSchema");
        modelBuilder.Configurations.Add(new SomeEntityMap());
        modelBuilder.Configurations.Add(new SomeOtherEntityMap());
    }
}

public class SchemaInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("[RemoveThisDefaultSchema].", string.Empty);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("[RemoveThisDefaultSchema].", string.Empty);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("[RemoveThisDefaultSchema].", string.Empty);
    }
}

Rockfish