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:

Dispose of a connection using a using statement c#corner link
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)
        }
     }

You are disposing a (reference) copy of the `_mc`, meaning that the `_mc` is also disposed. The other thing is, you need to persist the items with `mc.myTable.Where(x=>x.id==lgid).ToList()` — Jeroen van Langen, Jun 21 '19 at 17:29

Eric J. · Accepted Answer · 2019-06-21T17:23:47.107

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

Which approach would you suggest for a single user that is making multiple calls very rapidly to update a database? (assuming it wasnt possible to batch update for this question). Would you still use the Dispose method? It seems like you would have a lot of un-closed connections. — Rilcon42, Jun 21 '19 at 18:31
Your controller instance goes out of scope after each HTTP request, so it's not clear to me how you're keeping the context you pass into its constructor from going out of scope. Having said that, instantiating a new DB context is very fast. I'd start with creating a new instance in your action and going with the `using` statement since that's fast, simple, and unlikely to fail. — Eric J., Jun 21 '19 at 18:33

How does a using statement help prevent ZombieCheck()

1 Answers1