CODEWITHSUNDEEP
c#asp.netentity-frameworklinq-to-entities
Martin R
Martin R

Reputation: 113

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

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

Upvotes: 1

Views: 7216

Answers (2)

dmigo
dmigo

Reputation: 3029

You've got to dispose your context. You can either use the 'using' operator, or call 'Dispose' method.
Here the problem is discussed in a more detailed way.

Upvotes: 0

RePierre
RePierre

Reputation: 9566

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.

Upvotes: 1

Related Questions