Sharding Strategies using Entity Framework Core

.net .net-core asp.net-core entity-framework entity-framework-core

Question

I'm developing a new REST API using Asp.Net Core and Entity Framework Core. We will be porting over data from a legacy system that uses horizontal database partitioning (sharding). I'm trying to think of a good way to handle this in EF Core. Our previous sharding strategy involved a central Prime database, and multiple Customer databases. All queries included a CustomerId. We query the Prime database with the CustomerId to determine which Customer database contains the particular customer's data. The database schemas look something like this:

Prime Database

 dbo.Database  
        DatabaseId INTEGER  
        ConnectionString VARCHAR(200)  

    dbo.Customer  
        CustomerId BIGINT  
        DatabaseId INTEGER  

Customer Databases

dbo.Order  
    CustomerId BIGINT  
    OrderId INT  
    ...  

An example REST call for GETting an Order would be something like http://foo.com/api/Customers/{CustomerId}/Orders/{OrderId}

I need to have my CustomerDbContext use a dynamically determined Connection String with each REST request. Should I create new instances of the DbContext for each request? Or, can I change the connection string at run-time?

If I'm creating new DbContexts, how should I go about that? Most of the example code that I can find uses Dependency Injection from Startup.cs to create a singleton DbContext.

1
3
9/2/2016 6:45:50 PM

Popular Answer

Here's what I've come up with. It's still very rough, and I'd really appreciate any critiques that may be offered.

I added a "UseForNewCustomer BOOLEAN" to dbo.Database. I'm using Database Migrations to create new Shards on the fly.

ShardDbContextFactory

public class ShardDbContextFactory : IDbContextFactory<ShardDbContext>
{
    public ShardDbContext Create(DbContextFactoryOptions opts)
    {
        return this.Create("This-Connection-String-Isn't-Used");
    }

    public ShardDbContext Create(string connectionString)
    {
        var optsBldr = new DbContextOptionsBuilder<ShardDbContext>();
        //This is for PostGres. If using MS Sql Server, use 'UseSqlServer()'
        optsBldr.UseNpgsql(connectionString); 
        return new ShardDbContext(optsBldr.Options);
    }
}

ShardContextService.cs

public interface IShardContextService {
    ShardDbContext GetContextForCustomer(int customerId);
    void ActivateShard(string connectionString, string dbType);
}

public class ShardContextService : IShardContextService {
    private readonly PrimeDbContext _primeContext;
    public ShardContextService(SystemDbContext primeContext) {
        _primeContext = primeContext;
    }

    public CustomerDbContext GetContextForCustomer(int customerId)
    {
        Database shard = null;
        var customer = _primeContext.Customers
            .Include(m=>m.Database)
            .SingleOrDefault(c=>c.CustomerId == customerId);
        if (customer == null)
        {
            shard = _primeContext.Databases.Single(db=>db.UseForNewCustomer);

            if (shard == null) throw new System.Exception("Unable to determine shard: This is a new customer, and no shards are designated as useable for new customers.");

            _primeContext.Customers.Add(new Customer {
                CustomerId = customerId,
                DatabaseId = shard.DatabaseId
            });

            _primeContext.SaveChanges();
        }
        else
        {
            shard = customer.Database;
        }
        return (new ShardDbContextFactory()).Create(shard.ConnectionString)
    }

    public void ActivateShard(string connectionString)
    {
        using (var customerContext = (new ShardDbContextFactory()).Create(connectionString))
        {
            customerContext.Database.Migrate();
        }

        var previous = _primeContext.Databases.SingleOrDefault(d=>d.UseForNewCustomers);
        if (previous != null)
        {
            previous.UseForNewCustomers = false;
        }

        var existing = _primeContext.Databases.SingleOrDefault(d=>d.ConnectionString == connectionString);
        if (existing != null)
        {
            existing.UseForNewCustomers = true;
        }
        else
        {
            _primeContext.Databases.Add(new Database {
                ConnectionString = connectionString,
                UseForNewCustomers = true
            });
        }
        _primeContext.SaveChanges();
    }
}

Controller Action for creating a new Shard

[HttpPost]
public IActionResult Shard([FromBody] string connectionString) {
    try {
        _shardContextService.ActivateShard(connectionString);
        return Ok("Shard activated");
    } catch (System.Exception e) {
        return StatusCode(500, e);
    }
}

Controller Action for querying

[HttpGet]
[Route("/api/Customers/{customerId}/Orders/{orderId}")]
public virtual IActionResult GetOrdersForCustomer([FromRoute]long customerId, [FromRoute] long orderId)
{
    using (var ctx = _shardContextService.GetContextForCustomer(customerId))
    {
        var order = ctx.Orders.Where(o => o.CustomerId == customerId && o.OrderId = orderId).Single();
        if (order == null) return NotFound("Unable to find this order.");
        else return new ObjectResult(order);
    }
}
5
9/7/2016 2:03:42 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow