3

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);
       }
 }
Community
  • 1
  • 1
PMC
  • 4,698
  • 3
  • 37
  • 57

0 Answers0