使用實體框架核心的分片策略

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

我正在使用Asp.Net Core和Entity Framework Core開發一個新的REST API。我們將移植使用水平數據庫分區(分片)的遺留系統中的數據。我試圖想出一個在EF Core中處理這個問題的好方法。我們之前的分片策略涉及中央Prime數據庫和多個Customer數據庫。所有查詢都包含CustomerId 。我們使用CustomerId查詢Prime數據庫,以確定哪個Customer數據庫包含特定客戶的數據。數據庫模式如下所示:

Prime數據庫

 dbo.Database  
        DatabaseId INTEGER  
        ConnectionString VARCHAR(200)  

    dbo.Customer  
        CustomerId BIGINT  
        DatabaseId INTEGER  

客戶數據庫

dbo.Order  
    CustomerId BIGINT  
    OrderId INT  
    ...  

用於獲取訂單的示例REST調用類似於http://foo.com/api/Customers/{CustomerId}/Orders/{OrderId}

我需要讓CustomerDbContext使用動態確定的連接字符串和每個REST請求。我應該為每個請求創建DbContext新實例嗎?或者,我可以在運行時更改連接字符串嗎?

如果我正在創建新的DbContexts,我應該怎麼做呢?我能找到的大多數示例代碼都使用Startup.cs中的Dependency Injection來創建單例DbContext

熱門答案

這就是我想出來的。它仍然非常粗糙,我真的很感激可能提出的批評。

我在dbo.Database中添加了“UseForNewCustomer BOOLEAN”。我正在使用Database Migrations來動態創建新的Shards。

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用於創建新碎片的操作

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

用於查詢的控制器操作

[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);
    }
}


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因