Exception The connection's current state is broken when using entity framework

Question

I'am pretty new to EF... currently I am developing a website in asp.net with EF and I get sometimes exceptions about connection.

I've read this article https://github.com/geersch/EntityFrameworkObjectContext According to this I've programmed:

public static class ObjectContextPerHttpRequest
{
    public static tradeEntities Context
    {
        get
        {
            string objectContextKey = HttpContext.Current.GetHashCode().ToString("x");
            if (!HttpContext.Current.Items.Contains(objectContextKey))
            {
                HttpContext.Current.Items.Add(objectContextKey, new tradeEntities());
            }
            return HttpContext.Current.Items[objectContextKey] as tradeEntities;
        }
    }
}

and then I use myEntities et = p.ObjectContextPerHttpRequest.Context;

That somewhere on my website I need to get products... In order to do this I use this:

    public List<tProducts> returnProductsFromSubcategory(int subcategoryID)
    {
        var prod = from p in et.tProducts
                   from c in et.tCompany
                   where (c.companyID == p.fk_companyID && c.enable == true)
                   where (p.subCategoryID == subcategoryID && p.enable == true)
                   select p;

        //et.Connection.Close();

        return prod.ToList(); //Here comes an Exception The connection's current state is broken.
    }

Sometimes it works ok, but sometimes I get an exception.

System.InvalidOperationExceptionExecution of the command requires an open and available connection. The connection's current state is broken.

I am not sure what is wrong. I've read that I should implement dispose function, but where to add it and how can I do it?

Many thanks in advance for your help.

Martin

You must dispose ObjectContext in each request processing where you used it once you don't need to query database anymore. That is a reason why this approach with static context accessor is not very useful. — Ladislav Mrnka
Ups... it seems my question has been forgotten... I would be very grateful for any hints how can I program dispose action. Thanks. — Martin R

RePierre RePierre · Accepted Answer · 2012-04-04T10:36:48

To dispose of the object context you should wrap your queries in a using statement.

public List<tProducts> returnProductsFromSubcategory(int subcategoryID) 
{ 
    using(var et = new tradeEntities())
    {
        var prod = from p in et.tProducts 
                   from c in et.tCompany 
                   where (c.companyID == p.fk_companyID && c.enable == true) 
                   where (p.subCategoryID == subcategoryID && p.enable == true) 
                   select p; 
        return prod.ToList(); 
    }    
}

This will automatically dispose the object context and properly close the connection to the database, allowing it to return to the connection pool. I would suggest wrapping all your queries in a using block.

Exception The connection's current state is broken when using entity framework

2 Answers