How does a using statement help prevent ZombieCheck()

Question

I get this error when one API call in my EF Core C# web API is hit rapidly.

Proj> System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
Proj>    at System.Data.SqlClient.SqlTransaction.ZombieCheck()

It seems like the solution is to:

1. Dispose of a connection using a using statement c#corner link
2. Change AddDbContext to AddDbContextPool based on this SO post

How does a using statement for a readonly context help prevent the error below? It seems counter intuitive not to call new MyContext()

public class MyController : Controller
{
    private readonly MyContext _mc;

    public GreenCardController(MyContext mc){_mc=mc;}

    [HttpGet("GetCompanies")]
    public IEnumerable<vwCompany> GetCompanies(int lgid)
    {
        using (MeSeeksContext mc = _mc){
          return mc.myTable.Where(x=>x.id==lgid)
        }
     }

Answer 1

You receive your database context from outside the controller via its constructor. If you want to use that pattern, make the outside caller responsible for disposing the context when done. Do not use the using keyword when following that strategy.

You specifically receive the error message because the IEnumerable you return is lazily evaluated (meaning it isn't evaluated until necessary). The calling code doesn't have an opportunity to iterate the IEnumerable until after mc has been disposed by virtue of exiting the scope of the using statement.

One solution is to have the method instantiate its own context and materialize the response before returning it. This works well when the total number of returned objects is fairly small and can be achieved by adding .ToList() after the call.

using (MeSeeksContext mc = new MeSeeksContext() ){
    return mc.myTable.Where(x=>x.id==lgid).ToList();
}

If you return a large number of items, you can correctly follow the pattern you have of receiving the controller instance from the caller (alternatively, you can allocate it as a field of the controller and ensure it is disposed when the controller instance is disposed). If you go that route, don't use the using statement in your method. Use _mc

return _mc.myTable.Where(x=>x.id==lgid);