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.