UPDATE
I found EnsureOpen
from this question, made my mainsearch not async as the following two methods rely on it. I would still appreciate advice on this but it does seem to work.
Question
I am using Dapper alongside EF6 for some queries that need to be fast and experienced some connection closing issues on load testing. I fixed this by passing the DbContext and using it's connection, this was a WepApi controller, and I use Ninject to create the context.
Now I want to async everything, and I am getting errors that the connection is closed. When I try to open the connection, I don't seem to close it properly. When I wrap it in a using block and create a new connection, I get
: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
My current simplified code is:
public class TestController : ApiController
{
private readonly TestEntities _context;
public TestController(TestEntities context)
{
_context = context;
}
public async Task<IEnumberable<TestViewModel>> Get()
{
var main = new MainSearch();
var mainData = async main.GetMainDataAsync(_context);
var ids = mainData.Select(o => o.Id).ToArray();
var extras1 = new Extras1();
var extras1Data = async extras1.GetExtras1Async();
var extras2 = new Extras2();
var extras2Data = async extras2.GetExtras2Async();
foreach(var data in mainData)
{
mainData.Extras1 = extras1[data.Id];
mainData.Extras2 = extras2[data.Id];
}
return mainData;
}
}
public class MainSearch
{
public async Task<List<MainViewModel>> GetMainDataAsync(TestEntities context)
{
using (var connection = new SqlConnection(context.Database.Connection.ConnectionString))
{
await connection.OpenAsync();
var builder = new SqlBuilder();
var query = builder.AddTemplate("SELECT Id FROM Main");
//removed filter and sorting for SO question
return await connection.QueryAsync<MainViewModel>(query.RawSql, param: query.Parameters);
}
}
public class Extras1
{
public async Task<List<Extra1ViewModel>> GetExtras1Async(TestEntities context, int[] ids)
{
using (var connection = new SqlConnection(context.Database.Connection.ConnectionString))
{
await connection.OpenAsync();
var result = connection.Query<Extra1ViewModel>("SELECT * FROM Extras1 WHERE ID IN @Ids", new {Ids = ids});
return result.ToLookup(o => o.MainId);
}
}
public class Extras2
{
public async Task<List<Extra2ViewModel>> GetExtras1Async(TestEntities context, int[] ids)
{
using (var connection = new SqlConnection(context.Database.Connection.ConnectionString))
{
await connection.OpenAsync();
var result = connection.Query<Extra1ViewModel>("SELECT * FROM Extras2 WHERE ID IN @Ids", new {Ids = ids});
return result.ToLookup(o => o.MainId);
}
}