I have a project that has a Sql-Server database back end and Dapper as an ORM. I am attempting to use Dapper's QueryAsync() method to get some data. Not only that, but the call to my repo is coming from inside several tasks that are called with a Task.WhenAll (That is to say, each task involves getting data from that repo, so each task awaits my repo's method that wraps the QueryAsync() call).
The problem is that my SqlConnections never close even though I am using a using block. As a result, I have 100+ open connections to my database, and eventually start getting "max pool size reached" exceptions. The thing is, when I switch to just Query() instead of QueryAsync(), it works fine, but I'd like to be able to do this asynchronously.
Here's a code example. I tried to mimic the structure of the actual applicatoin as best as I could, which is why it looks more complex than it has to be.
Interface:
public interface IFooRepository<T> where T: FooBase
{
Task<IEnumerable<T>> Select(string account, DateTime? effectiveDate = null);
}
Implementation:
public class FooRepository : RepositoryBase, IFooRepository<SpecialFoo>
{
private readonly IWebApiClientRepository _accountRepository;
public FooRepository(IWebApiClientRepository repo)
{
_accountRepository = repo;
}
public async Task<IEnumerable<FuturePosition>> Select(string code, DateTime? effectiveDate = null)
{
effectiveDate = effectiveDate ?? DateTime.Today.Date;
var referenceData = await _accountRepository.GetCrossRefferenceData(code, effectiveDate.Value);
using (var connection = new SqlConnection("iamaconnectionstring")
{
connection.Open();
try
{
var res = await connection.QueryAsync<FuturePosition>(SqlQueryVariable + "AND t.code = @code;",
new
{
effectiveDate = effectiveDate.Value,
code = referenceData.Code
});
foreach (var item in res)
{
item.PropFromReference = referenceData.PropFromReference;
}
return res;
}
catch (Exception e)
{
//log
throw;
}
finally
{
connection.Close();
}
}
}
}
So now with the calling code, there are 2 layers. I'll start with the outer one. I think this is where the problem is. There are comments in the below.
Populator:
public class Populator : PopulatorBase
{
private IAccountRepository _acctRepository;
public override async Task<IEnumerable<PopulationResult>> ProcessAccount(DateTime? popDate = null)
{
//My attempt at throttling the async calls
//I was hoping this would force a max of 10 simultaneous connections.
//It did not work.
SemaphoreSlim ss = new SemaphoreSlim(10,10);
var accountsToProcess = _acctRepository.GetAllAccountsToProcess();
var accountNumbers = accountsToProcess.SelectMany(a => a.accountNumbers).ToList();
List<Task<ProcessResult>> trackedTasks = new List<Task<ProcessResult>>();
foreach (var item in accountNumbers)
{
await ss.WaitAsync();
trackedTasks.Add(ProcessAccount(item.AccountCode, popDate ?? DateTime.Today));
ss.Release();
}
//my gut tells me the issue is because of these tasks
var results = await Task.WhenAll(trackedTasks);
return results;
}
private async Task<ProcessResult>ProcessAccount(string accountCode, DateTime? popDate)
{
var createdItems = await _itemCreator.MakeExceptions(popDate, accountCode);
return Populate(accountCode, createdItems);
}
}
ItemCreator:
public class ItemCreator : ItemCreatorBase
{
private readonly IFooRepository<FuturePosition> _fooRepository;
private readonly IBarRepository<FuturePosition> _barRepository;
public RussellGlobeOpFutureExceptionCreator() )
{
//standard constructor stuff
}
public async Task<ItemCreationResult> MakeItems(DateTime? effectiveDate, string account)
{
DateTime reconDate = effectiveDate ?? DateTime.Today.Date;
//this uses the repository I outlined above
var foos = await _fooRepository.Select(account, effectiveDate);
//this repository uses a rest client, I doubt it's the problem
var bars = await _barRepository.Select(account, effectiveDate);
//just trying to make this example less lengthy
var foobars = MakeFoobars(foos, bars);
var result = new ItemCreationResult { EffectiveDate = effectiveDate, Items = foobars };
return result;
}
}
As far as what I've tried:
- Throttling with a SemaphoreSlim
- No throttling
- Using
connection.OpenAnync()in the repo - including/excluding a finally block (should be irrelevant with
using)
Its worthwhile to know that the foreach loop in the populator runs around 500 times. Essentially, there's a list of 500 accounts. For each one, it needs to do a long running populate task which involves pulling data from my Foo repo.
I honestly have no idea. I think it might have to do with awaiting my async db call from each task inside that list of tasks in the populator. Any insight into this issue would be super helpful.
return res.ToList()deferred execution with the IEnumerable. – Tableau