Multipleactiveresultsets in Entity Framework 4.1 Code First

Question

This is my first EF project so bear with me please.

When updating an entity such as Department, you pull it from the context, update its values and call context.SaveChanges. However, if you update Department.Employees, EF does not find that funny.

I searched and came up with the option of setting Multipleactiveresultsets=true in the connection string but want to know if:

• Is this the recommended way?
• Does this adversely affect performance / what should I look out for?

Answer 1

Enabling MARS is only necessary if you want to execute multiple queries on the same connection in parallel. This happens if you do something like this:

/* Foreach uses an iterator over the resultset of your query, but the query is not fetched
   immediately, instead the iterator internally triggers fetching for single
   processed record from opened data reader. Because of that the query and the reader
   are active until the iteration is over. */
foreach (var department in context.Departments.Where(...))
{
    /* The first query is still active on the connection but now you are executing
       lazy loading of all related employees =>. You are executing a second query and,
       without MARS, you will get an exception. */
    var employee = department.Employees.FirstOrDefault(...);
}

How to avoid that?

• Use eager loading instead of lazy loading: context.Departments.Include(d => d.Employees)
• Materialize whole department's result set prior to using lazy loading. It means not accessing employees inside of the loop.
• Enable MARS and the mentioned example will simply work

Is this the recommended way? Does this adversely affect performance / what should I look out for?

It depends on the problem you are trying to solve. If you have multiple departments to process, accessing their employees collection will trigger a separate query for each department. That is called N+1 problem - you have N departments and one query to fetch them and for each department you will execute one additional query => N+1 queries. For a huge number of departments this will be a performance killer.

Eager loading is not a bullet proof solution either. It can affect performance as well. Sometimes you simply need to execute separate queries to fetch all necessary departments and separate queries to fetch all necessary employees. If you have lazy loading turned off it should fix your relations and fill Employees property correctly for you. Btw, I made a suggestion on Data UserVoice to support this feature out of the box.