Is it possible to inject DbContext with sharding info when using the Azure SQL Elastic Database Client in ASP.NET Core?

Question

I would like to use the Azure SQL Elastic Database Client library to manage SQL Server sharding in my ASP.NET Core application.

DbContext is currently injected into my services.

I understand I need to add a constructor to my DbContext class that takes the arguments required for data-dependent routing (i.e. shard map and sharding key).

How is it possible to set these values when injecting the context?

What does your DI configuration code look like? Worst case, you can use a factory method to register the DbContext. — Panagiotis Kanavos, Jan 15 '21 at 10:34
I'm using the standard `services.AddDbContext(options => options.UseSqlServer(Configuration.GetConnectionString("MyDB")));` in startup, and my goal is to change as little as possible in my existing services — Assaf S., Jan 15 '21 at 10:41
No. I asked about the Elastic Database Client which is a Microsoft library used for managing SQL Server Sharding. I will update the question. — Assaf S., Jan 15 '21 at 10:44
If you want answers, you have to be specific. At this point in time, effectively, they do. Even Microsoft employees would ask you what you mean. Experienced devs would tell you that SQL Server has no sharding, that the attempts on Azure SQL made around 2012 eventually failed because client-side sharding was always simpler and faster to use than server-side sharding. And the docs that come out of a google search for [Elastic Database Client LIbrary](https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-use-entity-framework-applications-visual-studio) refer to EF — Panagiotis Kanavos, Jan 15 '21 at 10:49
Both the docs and samples are made for .NET Old. There's no support for DI in the [the client's source](https://github.com/Azure/elastic-db-tools/tree/master/Src/ElasticScale.Client). You'll have to either write your own DbContext factory and/or extension methods. One option would be to register a type that produces the necessary connection string, and use it as a constructor dependency. You have to do the exact opposite of what the documentation shows — Panagiotis Kanavos, Jan 15 '21 at 10:55
Or you could create a [database interceptor](https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors#database-interception) that implements IDbConnectionInterceptor and update the connection string? Or you could create the correct connection string in the DbContext factory method — Panagiotis Kanavos, Jan 15 '21 at 11:03
I see [people have been asking for EF Core support for years](https://github.com/Azure/elastic-db-tools/issues/118) and the only outcome was to recompile for .NET Standard. One of the commenters created a [helper class](https://gist.github.com/bbqchickenrobot/404df8ea7eca3b9c78895ffced08e41a) that produces DbContextOptions instances for the Elastic client library — Panagiotis Kanavos, Jan 15 '21 at 11:07
I implemented a small proof of concept of sharding using Entity Framework. It's basically a repository that receives two repositories in it's contructor. One of the repositories allows to fetch information about the shards, like their Id and database connection. The other Repository is a regular IRepository repository with the data you want to shard. When you want to insert/get/update/delete you just have to get the shard information from the first repository and then use the connection string to update the context of the second repository. — Andre, Mar 09 '22 at 21:00
To update the connection string on the second repository just use something like this: yourRepositoryContext.Database.SetConnectionString(selectedShard.ConnectionString); — Andre, Mar 09 '22 at 21:01
One thing worth mentioning is that I used the Decorator pattern on the main repository (the one that receives the other two repositories in the constructor). So it essentialy implements the same methods as the IRepository, but before it calls any of the methods it has to determine which of the shards to call. Another thing is that for normal CRUD operations sharding is straightforward, but if you want to do a bit of querying like find the first 30 youngest users on your application it can get complicated as it requires searching in parallel. — Andre, Mar 09 '22 at 21:06

Meeting Attender · Answer 1 · 2022-08-09T13:17:16.810

Yes it is possible and works great with EF Core using interceptors:
https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;

namespace <blah>;

public class RowLevelSecuritySqlInterceptor : DbCommandInterceptor, IRowLevelSecuritySqlInterceptor
{
    public Guid? TenantId { get; set; }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        SetSessionContext(command);

        return base.ReaderExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new ())
    {
        SetSessionContext(command);

        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        SetSessionContext(command);

        return base.NonQueryExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = new ())
    {
        SetSessionContext(command);

        return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        SetSessionContext(command);

        return base.ScalarExecuting(command, eventData, result);
    }
    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result,
        CancellationToken cancellationToken = new ())
    {
        SetSessionContext(command);

        return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    private void SetSessionContext(IDbCommand command)
    {
        var tenantId = TenantId is null ? "null" : $"'{TenantId.Value}'";
        command.CommandText = $"EXEC sp_set_session_context @key=N'TenantId', @value={tenantId};" + command.CommandText;
    }
}

You can create an interface for DI:

using Microsoft.EntityFrameworkCore.Diagnostics;

namespace <blah>;

public interface IRowLevelSecuritySqlInterceptor : IDbCommandInterceptor
{
    Guid? TenantId { get; set; }
}

And inject it to the DI container:

services.TryAddTransient<IRowLevelSecuritySqlInterceptor, RowLevelSecuritySqlInterceptor>();

And your DbContext may look something like:

public partial class MyDbContext
{
    private readonly IRowLevelSecuritySqlInterceptor _rowLevelSecuritySqlInterceptor;
    ...

    public AccountServiceDbContext(
        ...,
        IRowLevelSecuritySqlInterceptor rowLevelSecuritySqlInterceptor) : base(options)
    {
        ...,
        _rowLevelSecuritySqlInterceptor = rowLevelSecuritySqlInterceptor;
    }

    ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(...)
            .AddInterceptors(_rowLevelSecuritySqlInterceptor, ... and other interceptors);
    }
}

Is it possible to inject DbContext with sharding info when using the Azure SQL Elastic Database Client in ASP.NET Core?

1 Answers1