Documentation about using sharding with ASP.NET Core and EFCore

[pmeems]

For 5 years we have a .NET v4.6 application using EF6 and sharding. This is an internal application hosted by Azure used by a dozen users. When a user creates a new client, the application saves the client details in the client table in the Master database, next it creates a new shard/tenant database. All other data of this client will be saved in this new database. We use more or less this code example: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-use-entity-framework-applications-visual-studio

At this moment I'm rebuilding the application using .NET Core, Blazor, and EFCore. I managed to migrate most of the code pieces except for the sharding part. My code is available at https://bitbucket.org/pmeems/matblazorgooglegsuite/src/develop/

As I see it, the difference between my .NET Core application and my .NET classic application is how the tenant context is created. For .NET Core, I need to do something in startup.cs. I also asked about this at StackOverflow: https://stackoverflow.com/questions/61632457/how-to-use-database-sharding-with-ef-core-and-c David Browne (from Microsoft) is at least suggestion it should be possible but is only showing parts of what I should do.

We also use MultiShardConnection to search for employees in all client databases. It's not clear to me as well what the EFCore counterpart is.

What I'm looking for is a full example of how to implement sharding with .NET Core and EFCore. I can't find any examples. I did find other people asking similar questions and not finding any answers either.

I'm stuck with this for weeks now. Any guidance will be much appreciated.

[ajcvickers]

@pmeems We currently don't have any complete sample for sharding, and it isn't something that we have had any requests for over the last couple of years. I'm going to move this to the documentation repo and we will consider it alongside other documentation requests.

[pmeems]

Thanks @ajcvickers. I do hope this doesn't mean it will take forever to get the documentation because I'm really stuck at it. Please let me know if I can give some incentive to speed this up.

[JeremyLikness]

@pmeems I'm looking into this now. The configuration options should work fine but we'll see if we can create a small end-to-end example to reference or explain. I'll keep you posted.

[pmeems]

Thanks @JeremyLikness I've been emailing with Microsoft Support for weeks (10+) now and they give me bits and pieces but I still don't have an end-to-end example.

What I really need is an example where a new tenant database is created and registered as a shard when a user creates a new client. Next the needed tables should be created for this new tenant. And when the user goes to //mywebsite/client/2/employees the employees from the tenant database should be returned. So //mywebsite/client/3/employees returns the employees of another tenant database.

The difficult part for me to understand is how to open the correct tenant database when I only have the client ID. This client table is in the Master database.

In pseudo-code:

public void CreateClient()
{
  // Create new client object:
  var client = new Client {Name = "Foo"};
  // Save client to master database
  MasterContext.Clients.Add(client);
  MasterContext.Save();
  // TODO: Create new tenant database
  // TODO: Register as shard
  // TODO: Created tables using EF-CodeFirst
}

public List<Employee> GetEmployees(int clientId)
{
  // TODO: Get tenant context using clientId:
  var tenantContext = GetTenantContext(clientId);
  // Return employees:
  return tenantContext.Employees.GetAll()
}

public TenantContext GetTenantContext(int clientId)
{
  // TODO: Not sure what to do here
  // TODO: Also not sure what to put in Startup.cs
}

public List<Employee> SearchEmployee(string surName)
{
  // TODO: Use MultiShardConnection to search in all tenant databases for this employee.
}

[xuejmnet]

@pmeems i’m created lib for this problem，sharding-core this lib can auto sharding table and data base like sharding-jdbc in java,it's a plugin for efcore

[xuejmnet]

@pmeems i think ShardingCoreMultiTenantSys this demo u want. see this

            await _identityDbContext.AddAsync(sysUser);
            await _identityDbContext.AddAsync(sysUserTenantConfig);
            await _identityDbContext.SaveChangesAsync();
            //after register dynamic create data source and table and that will support sharding
            DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(new SqlShardingConfiguration(shardingTenantOptions));