How to use database sharding with EF Core and C#"

Question

I'm currently in the process of converting my 6 years old C# application to .NET Core v3 and EF Core (and also using Blazor). Most of it is working except for the Sharding part.
Our application creates a new database for each client. We use more or less this code for it: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-use-entity-framework-applications-visual-studio
I'm now trying to convert it to EF Core, but get stuck at this part:

        // C'tor to deploy schema and migrations to a new shard
        protected internal TenantContext(string connectionString)
            : base(SetInitializerForConnection(connectionString))
        {
        }

        // Only static methods are allowed in calls into base class c'tors
        private static string SetInitializerForConnection(string connnectionString)
        {
            // We want existence checks so that the schema can get deployed
            Database.SetInitializer<TenantContext<T>>(new CreateDatabaseIfNotExists<TenantContext<T>>());
            return connnectionString;
        }

        // C'tor for data dependent routing. This call will open a validated connection routed to the proper
        // shard by the shard map manager. Note that the base class c'tor call will fail for an open connection
        // if migrations need to be done and SQL credentials are used. This is the reason for the 
        // separation of c'tors into the DDR case (this c'tor) and the internal c'tor for new shards.
        public TenantContext(ShardMap shardMap, T shardingKey, string connectionStr)
            : base(CreateDDRConnection(shardMap, shardingKey, connectionStr), true /* contextOwnsConnection */)
        {
        }

        // Only static methods are allowed in calls into base class c'tors
        private static DbConnection CreateDDRConnection(ShardMap shardMap, T shardingKey, string connectionStr)
        {
            // No initialization
            Database.SetInitializer<TenantContext<T>>(null);

            // Ask shard map to broker a validated connection for the given key
            var conn = shardMap.OpenConnectionForKey<T>(shardingKey, connectionStr, ConnectionOptions.Validate);
            return conn;
        }

The above code doesn't compile because the Database object doesn't exist in this way in EF Core. I assume I can simplify it using TenantContext.Database.EnsureCreated(); somewhere. But I can't figure out how to modify the methods, which to remove, which to change (and how).

Of course, I've been searching for an example using sharding and EF Core but couldn't find it. Does anybody here has done this before in EF Core and is willing the share?

I'm specifically looking for what to put in startup.cs and how to create a new sharding/database when I create a new client.

Answer 1

In EF.Core just resolve the shard in OnConfiguring. EG

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var con = GetTenantConnection(this.tenantName);

    optionsBuilder.UseSqlServer(con,o => o.UseRelationalNulls());

    base.OnConfiguring(optionsBuilder);
}

Note that if you have a service or factory that returns open DbConnections, then you'll need to Close()/Dispose() them in the DbContext.Dispose(). If you get a connection string or a closed connection then DbContext will take care of closing the connection.

ASP.NET Core best-practices probably call for injecting an ITenantConfiguration service or somesuch in your DbContext. But the pattern is the same. Just save the injected service instance to a DbContext field and use it in OnConfiguring.

Answer 2

With the app that I'm working on, the desired shard is not discoverable until request time (for example, knowing what user is making the request, and then routing that user to their database). This meant that the OnConfiguring solution proposed above was not viable.

I worked around this by using IDbContextFactory<TContext>, and defining an extension on top of it, which sets the connection string based on whatever you want. I believe the database connection is created lazily in EF, and you are able to set the connection string up until the EF first needs to actually connect to the database.

In my case, it looked something like this:

var dbContext = _dbContextFactory.CreateDbContext();
var connectionString = $"DataSource={_sqlliteDirectory}/tenant_{tenant.TenantId}.db";

dbContext.Database.SetConnectionString(connectionString);

The downside is that it breaks the database abstraction (this code knows that my database is a local sqllite instance). An abstraction was not necessary in this layer of my app, but it is something very solvable if it's required.