How can I return IEnumarable data from function in GridView with Entity Framework?

Question

protected IEnumerable GetPersonalsData()
{
   // List<Personals> personel;
   using (FirmaEntities firmactx = new FirmaEntities())
   {
      var personeldata = (from p in firmactx.Personals select new { p.ID, p.Name, p.SurName });
      return personeldata.AsEnumerable();
   }
}

I want to send GetPersonelData() into GridView DataSource. Like this:

gwPersonel.DataSource = GetPersonelData(); 
gwPersonel.DataBind();

It monitored to me on : gwPersonel.DataBind(); this error:

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

Answer 1

Once the function returns, I think the context has disappeared so the enumeration can't be used. Just convert it to a list (use .ToList()) instead, this can be added to a DataSource.

Answer 2

When you return from the method, the object context is gone but the query has not been executed yet (deferred execution). You will either need to keep the object context around or ensure that the query executes and the results are available in GetPersonalData method by calling ToList().

Answer 3

The problem is that AsEnumerable does exactly that returns an IEnumerable. The query you have defined doesn't get executed at that point. Only when something that receives it attempts to enumerate it will the query actually be executed. In this case that something is outside of GetPersonalsData and the instance of FirmaEntities that the query depends on will have been disposed by that point. Hence the error.

Earlier thoughts here but not so relevant in this case I suspect

You might consider using ToList() instead of ToEnumerable() which is in most scenarios a better solution. Another alternative would be:-

protected IEnumerable GetPersonalsData()
{
    // List personel;
    using (FirmaEntities firmactx = new FirmaEntities())
    {
        foreach (var item in (from p in firmactx.Personals))
           select new { p.ID, p.Name, p.SurName });
        {
            yield return item;
        }
    }
}

This is similar to but not exactly the same as a call AsEnumerable. In this case each time the returned IEnumerable is enumerated (its GetEnumerator method is called) a fresh execution of the function occurs (when the IEnumerator.MoveNext method is called). This allows you to defer (or not end up actually) executing the query at all. The instance of firmactx won't be disposed until the enumeration is complete.

After thoughts

You are assigning to a grid view which you may want to page and/or filter sort. In that case you might be better off holding on to an instance of the context as a field in your UserControl. Using this instance of the context assign the personeldata query directly to the DataSource without using AsEnumerable.