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