NPoco exhausting connection pool on .NET Core 3.1

490 views Asked by At

When I call into my repository 10000 times, it either takes minutes (for a very simple keyed query, which does not take minutes to do on the database itself), or dies quickly with a connection pool exhaustion message. I know I am doing something wrong with some combination of disposing objects, creating objects, DI container lifespans, and so on. What am I doing wrong? I have tried a few permutations of .Singleton / .Scoped, a ThreadLocal cache of databases, etc.

Code is executing on Windows 10, framework is .NET Standard 2.1 (running on .NET Core 3.1), talking to SQL Server 2016.

My registration policy (Lamar):

    public NPocoRegistry()
    {
        For<IDatabase>()
            .Use(ctx => ctx.GetInstance<DatabaseFactory>().GetDatabase())
            .Scoped();

        For<DatabaseFactory>().Use(ctx =>
        {
            var configuration = ctx.GetInstance<IConfiguration>();

            Database CreateDatabase()
            {
                return new Database(configuration.GetConnectionString("EdgeDev"),
                    DatabaseType.SqlServer2012,
                    SqlClientFactory.Instance)
                {
                    KeepConnectionAlive = true
                };
            }

            var configs = FluentMappingConfiguration.Configure(ctx.GetAllInstances<IMap>().ToArray());

            return DatabaseFactory.Config(cfg => cfg
                .UsingDatabase(CreateDatabase)
                .WithFluentConfig(configs)
                .WithMapper(new BooleanMapper())
                .WithMapper(new BinaryStringMapper()));
        }).Singleton();

        Scan(scan =>
        {
            scan.TheCallingAssembly();
            scan.AddAllTypesOf<IMap>();
        });
    }

My base repository:

public abstract class BaseNPocoRepository<T>
{
    private readonly DatabaseFactory _dbFactory;
    private readonly ThreadLocal<IDatabase> _databaseLocal;

    protected BaseNPocoRepository(DatabaseFactory dbFactory)
    {
        _dbFactory = dbFactory;
        _databaseLocal = new ThreadLocal<IDatabase>(_dbFactory.GetDatabase);
    }

    protected virtual IDatabase GetDatabase() => _databaseLocal.Value;

    public virtual async Task CreateAsync(T item)
    {
        using var database = GetDatabase();
        await database
            .InsertAsync(item)
            .ConfigureAwait(false);
    }

    public virtual async Task UpdateAsync(T item)
    {
        using var database = GetDatabase();
        await database
            .UpdateAsync(item)
            .ConfigureAwait(false);
    }

    public virtual async Task DeleteAsync(T item)
    {
        using var database = GetDatabase();
        await database
            .DeleteAsync(item)
            .ConfigureAwait(false);
    }

    public virtual async Task<IEnumerable<T>> RetrieveManyAsync()
    {
        using var database = GetDatabase();
        return await database
            .Query<T>()
            .ToEnumerableAsync()
            .ConfigureAwait(false);
    }
}

A sample repository utilizing this pattern:

public class T_AccountRepository : BaseNPocoRepository<T_Account>
    , IRetrieveMany<T_Account>
    , IRetrieve<AccountId, T_Account>
{
    public T_AccountRepository(DatabaseFactory dbFactory) : base(dbFactory)
    {
    }

    public async Task<T_Account> RetrieveAsync(AccountId input)
    {
        using var database = GetDatabase();
        return await database.Query<T_Account>()
            .SingleAsync(x => x.AccountId == (int) input)
            .ConfigureAwait(false);
    }
}

How it's actually being called:

    static async Task Main(string[] args)
    {
        Console.WriteLine("Booting up . . .");

        var container = new Container(cfg =>
        {
            cfg.Scan(scan =>
            {
                scan.AssembliesFromApplicationBaseDirectory();
                scan.AssemblyContainingType<NPocoRegistry>();
                scan.LookForRegistries();
                scan.With(new AllInterfacesConvention());
            });
        });

        Console.WriteLine("Getting repository . . . ");
        var repo = container.GetInstance<AccountRepository>();


        Console.WriteLine("Starting benchmark . . .");
        var sw = Stopwatch.StartNew();

        for (int i = 0; i < 10000; i++)
        {
            await repo.RetrieveAsync(1253832471);
        }
        
        Console.WriteLine(sw.ElapsedMilliseconds + "ms");
    }
0

There are 0 answers