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.

Solution 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
  };
  

Solution 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).

Solution 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

Comments

[cnd]

try to close using after return

[Mat J]

Lazy Loading might be enabled for your context, which means Country is loaded from database using the parent context's connection when you first try to read its value by accessing that property.

[cnd]

@Mathew yes, other parts are already values because of ToList there but Country is not (even in model it's virtual where getter goes to db)

[Felichino]

thanks, FlyingFox, for editing my code. It is my first post.

[Panagiotis Kanavos]

Lazy loading problems aren't solved by leaving orphaned contexts. In fact, it won't even solve this problem as the garbage collector can dispose the context at any time, before there's time to load the Country property

[Panagiotis Kanavos]

Downvoters, it isn't polite to downvote incorrect answers from first posters. There are other answers to this question that aren't just wrong but dangerous as well

[Konrad Viltersten]

@PanagiotisKanavos While not a big fan in downvoting (and definitely not in favor of downvoting below -1 or -2), I disagree that it's impolite to downvote first-time posters. A bad answer is just as bad regardless of who posts it. And giving some kind of discount to people because it's their first try means that we're favoring lousy material if people are new to the site. I'd suggest, when it comes to first-timers, that we take more time to explain why the answer isn't good (just as you did).

[Konrad Viltersten]

@PanagiotisKanavos To clarify - I don't think we should downvote very heavily irregardless of the poster's credit/history. However, I understand that some users are frustrated when a newcomer joins in and throws in their 2 cents seriously believing that it makes them on pair with the savvy programmers. That's just arrogant and should be vastly punished by being beaten up with a stick. Figuratively speaking.

[Panagiotis Kanavos]

@KonradViltersten remember the prime directive - "Be Nice". Otherwise first-time posters become last-time posters

[Konrad Viltersten]

@PanagiotisKanavos I want to emphasize once again that I do agree with you on being nice as a means for making newcomers feel welcome and prone to stick around. I'm just saying that one can be nice and still point out a flaw, explaining in a comment why it's incorrect and how it should be improved. Not correcting/explaining, hence leading the responder to believe that the reply was okay, doesn't consists "being nice". Not to the responder and not to any future reader of the stuff.

[user2424495]

Also make sure you are using System.Data.Entity otherwise VS will complain about the lambda expression in the second option.

[Azimuth]

What else I can try if Include doesn't help?