Why doesn't Web API Controller with LINQ To SQL and "using" statement work?

Question

I like to wrap my data access in using statements to make myself feel good about garbage collection. I am running Visual Studio 2013 Preview and targeting .NET 4.5. I have an ApiController called WordsController as such:

public class WordsController : ApiController
{
    // GET api/<controller>
    public IEnumerable<Keyword> Get()
    {
        using (TestDataContext dc = new TestDataContext())
        {
            return dc.Keywords;
        }
    }
}

I get an error telling me that the datacontext has been disposed before accessing the data.

Changing the code to this works:

public class WordsController : ApiController
{
    // GET api/<controller>
    public IEnumerable<Keyword> Get()
    {
        TestDataContext dc = new TestDataContext();

        return dc.Keywords;
    }
}

Why does this work when not using the DataContext?

Answer 1

return dc.Keywords

is not actually executing the query/materialising the results there and then, so by the time the results are actually being enumerated (and hence it tries to run the query), the context has already been disposed.

Instead try:

using (TestDataContext dc = new TestDataContext())
        {
            return dc.Keywords.ToArray(); // execute the query and materialise NOW
        }

Just to add the proper term and reference here: Deferred Execution

Answer 2

AdaTheDev's answer is spot on about why it's not working. The scope of your 'using' statement does not include the return. An alternate to the proposed answers is to make your DbContext 'TestDataContext' a private field in the API controller, and make sure you dispose it when you are done. The format for that would look like:

public class WordsController : ApiController
{
     private TestDataContext db = new TestDataContext();

     // GET api/<controller>
     public IEnumerable<Keyword> Get()
     {
         return db.Keywords;
     }

     ... other API methods

    public override void Dispose()
    {
         db.Dispose();
         base.Dispose();
    }
}

The benefit of this method is that you can use the same context over and over again in the same APIController and only instantiating it once; for a heavy DataContext this can be quite useful.

Answer 3

Use this:

    public IEnumerable<Keyword> Get()
    {
        List<Keyword> data;
        using (TestDataContext dc = new TestDataContext())
        {
            data = dc.Keywords.ToList();
        } 
        // here you can do some operations with 'data'
        return data;
    }

Answer 4

I think that what you're returning is a deferred query rather than the actual list of keywords so when your Action returns and the query is materialized, the data context has already been disposed.

  public class WordsController : ApiController
    {
        // GET api/<controller>
        public IEnumerable<Keyword> Get()
        {
            using (TestDataContext dc = new TestDataContext())
            {
                return dc.Keywords;  //deferred execution
            }

        }
    } //too late to materialize the query.  The context has been disposed

I think YD1M's answer will fix your problem. One quick suggestion would be to create a simple service layer and put your data access logic there then have your controller access that service rather then accessing the datacontext directly. This won't necessary fix your problem but it would remove the database dependency from the controller and thus clean up your design a bit.