2

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();
    }
}
Nil Pun
  • 17,035
  • 39
  • 172
  • 294
  • is there a specific reason to return `IQueryable` instead of `IEnumerable`? – RePierre Sep 25 '12 at 12:04
  • @RePierre, thanks for your reply. IT's existing code and can't be updated. What would be the difference, anyway? – Nil Pun Sep 25 '12 at 12:24
  • The difference is here http://stackoverflow.com/a/2876655/844006. I was asking because I think you'll have some problems if you wrap your entity container in a `using` statement and expose the result as `IQueryable`; someone might later do something like `GetEmployeeDetail().Where(e=>e.Id == id)` and there may be some problems executing that query because the data context object would be disposed. – RePierre Sep 25 '12 at 12:55
  • if someone did GetEmployeeDetail().Where(e=>e.Id == id) then the result would be available on in-memory. That's the whole purpose of ToList().AsQuerable() – Nil Pun Sep 25 '12 at 13:15

1 Answers1

2

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.
Tom Haigh
  • 57,217
  • 21
  • 114
  • 142
  • How would ToList() cause the performance and filtering issue. As the data will be stored in in-memory. – Nil Pun Sep 25 '12 at 13:16
  • It might do if it is causing 10s/100s/1000s of employees to be retrieved from the database on every call, which could be unnecessary if you are filtering the list later – Tom Haigh Sep 25 '12 at 14:19