Entity Framework 4.0 - The underlying provider fai

Question

We have a web application with Entity Framework 4.0. Unfortunately, when the large volume of users hit the application the EF throws an error The underlying provider failed on Open

Below is the code snippet:

//DAL
public IQueryable<EmployeeEntity> GetEmployeeDetail()
{
    DatabaseEntities ent = new DatabaseEntities(this._connectionString);
    IQueryable<EmployeeEntity> result = from employee in ent.EmployeeEntity
                                           select employee;

    return result;
}

Please note the above code returns IQuerable.

1. Is anything wrong with above pattern that could cause the exception to occur?
2. When and how does Entity Framework determine to close / open db connection and also how long to retain?
3. On what scenario does above error occurs?
4. What's the maximum number of connection pool for EF and how do we configure?
5. Do we need to explicitely specify open and close
6. Is code below a best way to resolve above issue?

---

public IQueryable<EmployeeEntity> GetEmployeeDetail()
{
    using (DatabaseEntities ent = new DatabaseEntities(this._connectionString))
    {
        IQueryable<EmployeeEntity> result = from employee in ent.EmployeeEntity 
                                            select employee;                     
        return result.ToList().AsQuerable();
    }
}

Answer 1

The ToList() call will cause the query to run on the database immediately and as this is not filtered in any way will return every employee in your database. This is probably likely to cause you performance issues.

However you can't remove this in your case because if you return the IQueryable directly then the context will be disposed by the time you try and fetch results.

You could either:

• change the way it works so that the scope of ent does not end when the method returns and return the query without calling ToList(). You can then further filter the IQueryable before calling ToList().
• call ToList() within the method but filter/limit the query first (e.g. pass some parameters into the method to specify this) to reduce the number of rows coming back from the database.