How to use EF Core with Azure SQL Shards for .NET 5+?

Question

We have a SaaS product with customer tenants, and we are trying to implement scalable database access, but it looks like the ElasticScale github project has been abandoned. Is there a new mechanism for this? How do you implement scalable, manageable database shards in a modern version of EF?

Answer 1

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

Answer 2

The package Microsoft.Azure.SqlDatabase.ElasticScale.Client is the official client library that allows ADO.NET developers to create applications that implement and use the pattern known as database sharding in Azure SQL Database. But it supports .NET Standard 2.0 and .NET Framework 4.5.0.

Elastic Database features are composed of Elastic Database client library, Elastic Database split-merge tool, Elastic Database jobs, Elastic Database query and Elastic transactions.

Refer Elastic Database client library with Entity Framework for more details and sample code.