Database connection errors on EF

Question

I am very new to entity framework and I am having a problem with a web api based site (connected to mssql) that I am writing. I keep getting seemingly random errors (mostly seeming to be database related). These errors happen most often when the site is first published but they do sometimes happen when it has been hours since the last publish. A selection of the errors:

Invalid operation. The connection is closed.

There is already an open DataReader associated with this Command which must be closed first.

The connection was not closed. The connection's current state is connecting.

The context cannot be viewed while the model is being created

Underlying provider failed to open

My context looks like this:

public class Context : DbContext
{

    public Context() : base("name=DefaultConnection")
    {
    }

    public override int SaveChanges()
    {
        DateTime now = DateTime.Now;
        foreach (ObjectStateEntry entry in (this as IObjectContextAdapter).ObjectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Modified))
        {
            if (!entry.IsRelationship)
            {
                IHasUpdated updated = entry.Entity as IHasUpdated;
                if (updated != null)
                    updated.updated = now;
            }
        }
        return base.SaveChanges();
    }

    public DbSet<Branch> Branches { get; set; }
    public DbSet<Company> Companies { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UsefulLink> UsefulLinks { get; set; }
}

There are many more DbSets than this. Should I be creating a separate context for each?

One of my basic controllers:

 public class UsefulLinksController : ApiController
 {
    private Context db = new Context();

    [ResponseType(typeof(UsefulLinksWrapper))]
    public IHttpActionResult GetUsefulLinks([FromUri]UsefulLinkParams prams)
    {   
        UsefulLinksWrapper wrapper = new UsefulLinksWrapper();
        Meta meta = new Meta();
        IQueryable<UsefulLink> query = db.UsefulLinks;

    if (prams.sortBy == null)
        {
            prams.sortBy = "ID";
        }

        // Paging
        query = query.OrderBy(prams.sortBy + " " + prams.sortDirection).Skip(prams.offset - 1).Take(prams.limit);

        List<UsefulLink> data = query.ToList();

        meta.totalCount = query.Count();
        meta.offset = 1;
        meta.limit = prams.limit;
        wrapper.meta = meta;
        wrapper.data = data;

        return Ok(wrapper);

    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }

    private bool UsefulLinkExists(int id)
    {
        return db.UsefulLinks.Count(e => e.ID == id) > 0;
    }
}

I don't seem to see these errors when I run the site locally though there are two of us hitting it when it is published so perhaps the issue stems from multiple users?

What is the lifecycle of db context? It should be per request. — prnjanuario
Set MultipleActiveResultSets=True in the connection string and make sure not a single context instance is accessed by multiple threads (which may occur if you share context instances among controllers). — Gert Arnold
@GertArnold Unfortunately, the connection string addition is the first thing I tried and it hasn't seemed to make any difference. Mine is in a different case to yours though so i'll make sure that isn't the problem! — Chris Foot
You should try to narrow down your problem as much as possible. Try to find the smallest piece of code that still reproduces the issue. Ideally it is so small you can post it completely here - if you haven't found the root cause in the process of shrinking your code. Examples: Does it still happen if you only have one DbSet? Does it happen if you use the entities from a command line project rather than an MVC? ... — chiccodoro

Ayo I Ayo I · Accepted Answer · 2014-05-19T07:58:50

Chris, I notice in your controller you are sharing your db context with all of the methods in your controller class.

This is generally not a best practice in Entity Framework (see: EntityFramework 4 ObjectContext Lifetime). You should keep your context alive as briefly as possible. Leaving the context alive to share across multiple methods could result in many of the errors that you list above.

I would recommend trying to instantiate a new instance of the context, instead, wherever it is used and quickly disposing of it.

This should generally result in more stable behavior.

So the below:

class SomeClass
{
   private context = new Context(); //sharing your context with all methods
   public someMethod()
   {
      context.doSomething;
   }

   public someMethod2()
   {
      context.doSomething;
   }
}

should become:

class SomeClass
{

   public someMethod()
   {
      Context context = new Context(); //now your context is declared and disposed of within each method
      context.doSomething;
   }

   public someMethod2()
   {
      Context context = new Context(); //now your context is declared and disposed of within each method
      context.doSomething;
   }
}

Or even better, you can use a using construct to ensure that your context is properly disposed of:

class SomeClass
{
   public someMethod3()
   {
      using(Context context = new Context()) //now wrapping the context in a using to ensure it is disposed
      {
         context.doSomething;
      }
   }
}

I would recommend trying the above changes and seeing if your behavior becomes more stable.

Database connection errors on EF

2 Answers