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?
Yes it is possible and works great with EF Core using interceptors:
https://docs.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);
}
}