The ObjectContext instance has been disposed and can no longer be used for operations that require a connection. in Reference table

Question

I have two tables Customers and Country and use ( Entity Framework with vs 2012 )

And the model class

 using System;
 using System.Collections.Generic;

 public partial class Customer
 {
     public int Id { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public string Address { get; set; }
     public string Email { get; set; }
     public string Phone { get; set; }
     public Nullable<int> CountrryId { get; set; }
     public string Note { get; set; }

     public virtual Country Country { get; set; }
 }

I try to build a select query for get all customers with Country Name. But I always get the below error message.

Answer 1

You are trying to access an association property Country after the data context has been disposed. Entity Framework, by default, loads association properties lazily. In other words, it makes another trip to the database when you try to access the association property for the first time. To make this trip to the database, Entity Framework must use a data context. In your case, it would be trying to use the data context created by jQGridDemoEntities db = new jQGridDemoEntities() which has unfortunately been disposed at this point in your code. The data context has been disposed because you've exited the using block.

You have three options to get around this problem:

• Access the association property when the data context is still alive. More concretely, move your code where you access the association property into your using block

• Eagerly load the association property as explained in the first link I specified

  customers = db.Customers.Include(c => c.Country).ToList()

• Explicitly select what you want to return from the database while the data context is still alive. And use that information to construct the json object your return.

  customers = db.Customers.Select(c => new
  {
      c.Id,
      c.FirstName,
      c.LastName,
      c.Address,
      c.Email,
      c.Phone,
      CountryName = c.Country.Name,
      c.Note
  };
  

Answer 2

You have lazy loading enabled. So when you try to load reference property there is no way to do it, because ObjectContext disposed right after using block.

There are two ways to fix it:

//1. Tell EF to load Country property immediately.
using(var db = new jQGridEntities())
{
    customers = db.Customers.Include(c => c.Country).ToList();
}

//2. Put return inside using block.
using(var db = new jQGridEntities())
{
    customers = db.Customers.ToList();
    return Json(/*your code*/);
}

Also you can disable lazy loading, but in that case you will get NullReferenceException, which can be also fixed using .Include(c => c.Country).

Answer 3

You disposing database by using block

Put your code into using block not outside.

When using using block element that in using(var element) being disposed when using block ends

Answer 4

there is one more solution, using ViewBag

using(var db = new jQGridEntities())
{
    var customers = db.Customers.Where(c=>c.Country!=null).ToList();
    ViewBag.customerlist= customers;
}