EF Core overwrite mechanic to send commands to database-CodePudding

I want to use EF Core for my application but instead that EF Core should send a SQL command onto a datbase it should just simply send it to another application which will care about multi-tenancy and choosing the right database (tennant) for this command.

So is there a way to overwrite the main point in EF Core where the command is allready generated and WOULD be now send into the given database and instead of sending it into that, just do things?

CodePudding user response：

EF Core Interceptors allow you to do just that:

public class QueryCommandInterceptor : DbCommandInterceptor
{
    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        var resultReader = await SendCommandToAnotherAppAsync(command);

        return InterceptionResult<DbDataReader>.SuppressWithResult(resultReader);
    }

    private Task<DbDataReader> SendCommandToAnotherAppAsync(DbCommand command)
    {
        // TODO: Actual logic.
        Console.WriteLine(command.CommandText);

        return Task.FromResult<DbDataReader>(null!);
    }
}

Make sure to override other methods like ScalarExecuting, NonQueryExecuting, sync and async variants.

CodePudding user response：

Multitenancy is already supported in EF Core. The options are described in the Multi-tenancy page in the docs.

In this case, it appears a different database per tenant is used. Assuming there's a way/service that can return who the tenant is, a DbContext can use a different connection string for each tenant. Assuming there's a way/service to retrieve the current tenant name, eg ITenantService, the DbContext constructor can use it to load a different connection string per tenant. The entire process could be abstracted behind eg an ITenantConnectionService, but the docs show how this is done explicitly.

The context constructor will need the tenant name and access to configuration :

public ContactContext(
    DbContextOptions<ContactContext> opts,
    IConfiguration config,
    ITenantService service)
    : base(opts)
{
    _tenantService = service;
    _configuration = config;
}

Each time a new DbContext instance is created, the connection string is loaded from configuration based on the tenant's name:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var tenant = _tenantService.Tenant;
    var connectionStr = _configuration.GetConnectionString(tenant);
    optionsBuilder.UseSqlite(connectionStr);
}

This is enough to use a different database per tenant.

Scope and lifecycle

These aren't a concern when a DbContext is used as a Scoped or Transient service.

By default AddDbContext registers DbContexts as scoped services. The scope in a web application is a single request. Once a request is processed, any DbContext instances created are disposed.

Connection Pooling

Connection pooling isn't a concern either. First of all, a DbContext doesn't represent a database connection. It's a disconnected Unit-of-Work. A connection is only opened when the context needs to load data from the database or persist changes when SaveChanges is called. If SaveChanges isn't called, nothing gets persisted. No connections remain open between loading and saving.

Consistency is maintained using optimistic concurrency. When the data is saved EF Core checks to see if the rows being stored have changed since the time they were loaded. In the rare case that some other application/request changed the data, EF Core throws a DbConcurrencyException and the application has to decide what to do with the changed data.

This way, there's no need for a database transaction and thus no need for an open connection. When optimistic concurrency was introduced in ... the late 1990s, application scalability increased by several (as in more than 100, if not 1000) orders of magnitude.

Connection pooling is used to eliminate the cost of opening and closing connections. When DbConnection.Close() is called, the actual network connection isn't terminated. The connection is reset and placed in a pool of connections that can be reused.

That's a driver-level feature, not something controlled by EF Core. Connections are pooled by connection string and in the case of Windows Authentication, per user. This eliminates the chance of a connection meant for one user getting used by another one.

Connection pooling is controlled by ADO.NET and the database provider. Some drivers allow connection pooling while others don't. In those that do support it, it can be turned off using keywords in the connection string or driver settings.