Exception When utilizing entity framework, the present state of the connection is broken.

asp.net c# entity-framework linq-to-entities

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 http://cgeers.com/2009/02/21/entity-framework-objectcontext/ 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

1
1
1/28/2012 11:45:25 AM

Popular Answer

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.

1
4/4/2012 10:36:48 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow