MultiShardConnection and Entity Framework

Question

Introduction. I have found one question and one issue related to the problem :

elastic-db-tools github issue. 2015

stackoverflow question. 2015

Actually I work with multi-tenant architecture. Using latest .net core, ef core and ElasticScale. Most of the time I work with only one tenant - usual case. ShardMapManager provide SqlConnection, and then I can create DbContext using it.

Also I have the following case - I need to request more than one shard at a time. Something like - looking for a Customer through all tenants.

ElasticScale has MultiShardConnection for that. I have to write usual ADO.NET queries to solve this problem. I don't like ADO.NET queries for real. The whole solution works with the ORM - EF, thus I want to use ORM everywhere! I was trying to find something like an adapter for the EF...

Okay. I wrote the following solution:

public sealed class TenantDataContextFactory : ITenantDataContextFactory
{
    private readonly Lazy<string> _cachedConnectionString;

    private readonly IShardingService _shardingService;
    private readonly IConfigurationManager _configurationManager;
    private readonly ILogger<TenantDataContextFactory> _logger;

    //sp_set_session_context required MSSQL SERVER 2016 or above!!!
    private const string SpSetSessionContextQuery = @"exec sp_set_session_context @key=N'TenantId', @value=@TenantId";

    public async Task<TenantDataContext> CreateAsync(Guid tenantId)
    {
        SqlConnection sqlConnection = null;
        try
        {
            sqlConnection = await _shardingService.ShardMap
                .OpenConnectionForKeyAsync(key: tenantId,
                                           connectionString: _cachedConnectionString.Value,
                                           options: ConnectionOptions.Validate)
                .ConfigureAwait(false);

            var cmd = sqlConnection.CreateCommand();

            cmd.CommandText = SpSetSessionContextQuery;
            cmd.Parameters.AddWithValue("@TenantId", tenantId);

            await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);

            return new TenantDataContext(tenantId, sqlConnection);
        }
        catch (Exception ex)
        {
            sqlConnection?.Dispose();

            _logger.LogCritical(ex, $"Create(). Create {nameof(TenantDataContext)} for {tenantId} was ended with an error!");
            throw;
        }
    }
}

public class MultiTenantConnectionFactory : IMultiTenantConnectionFactory
{
    private readonly IShardingService _shardingService;
    private readonly ITenantDataContextFactory _tenantDataContextFactory;

    public async Task<IReadOnlyCollection<TenantDataContext>> GetContexts()
    {
        var shards = _shardingService.RegisteredTenants;
        var connectionsTasks = shards.Select(x => _tenantDataContextFactory.CreateAsync(x));

        return await Task.WhenAll(connectionsTasks).ConfigureAwait(false);
    }
}


public sealed class MultiTenantRepository<T> : IMultiTenantRepository<T> where T : class, ITenantEntity
{
    private readonly IMultiTenantConnectionFactory _multiTenantConnectionFactory;

    public async Task<IList<T>> Find(Expression<Func<T, bool>> filter)
    {
        var dataContexts = await _multiTenantConnectionFactory.GetContexts().ConfigureAwait(false);

        var tasksList = dataContexts.Select(async x =>
        {
            await using (x)
            {
                return await x.Set<T>().Where(filter).ToListAsync().ConfigureAwait(false);
            }
        });

        var results = await Task.WhenAll(tasksList).ConfigureAwait(false);

        return results.SelectMany(x => x).ToList();
    }
}

But I don't like this solution! Managing connections, awaiting a lot of tasks... I think this solution is slow... Can anybody explain how should I work with the MultiShardConnection without ADO.NET?!

I'd like to test my solution and ADO.NET query performance. I think I will have to give up if I don't find another solution or use ADO.NET.

P.S. I know about ValueTask! I will change usual Task<T> to the ValueTask soon.