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?

Answer 1

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.

Answer 2

Since I do not know how your page uses the methods UsefulLinksController and in which order, I would say UsefulLinkExists is perhaps the culprit due to lazy loading

Lazy loading means delaying the loading of related data until you specifically request it

Which would explain why your "reader" remains "open".

Try:

return db.UsefulLinks.ToList().Count(e => e.ID == id) > 0;

In any case, you can disable lazy loading by default in the context constructor as such noted here:

public MyEntitiesContext() : base("name=MyEntitiesContext", "MyEntitiesContext")
{
    this.ContextOptions.LazyLoadingEnabled = false;
    OnContextCreated();
}

As far as I know, it applies to EF4 and up.