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

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

Question

I'm quite new to EF. At the moment, I'm using EF to build an asp.net website, and sometimes issues concerning connections occur.

This item has been read http://cgeers.com/2009/02/21/entity-framework-objectcontext/. This is what 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;
        }
    }
}

then I employmyEntities 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.
    }

I sometimes get an exception, but sometimes it works well.

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

I'm not sure what the issue is. I've heard that I should add the dispose function, but how do I go about doing so?

In anticipation, many thanks for your assistance.

Martin

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

Popular Answer

You should enclose your queries with a to get rid of the object context.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(); 
    }    
} 

By doing so, the connection to the database will be appropriately closed and automatically disposed of, enabling it to rejoin the connection pool. I'd advise encasing all of your inquiries in ausing 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