14

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?
abatishchev
  • 98,240
  • 88
  • 296
  • 433
Raheel Khan
  • 14,205
  • 13
  • 80
  • 168

1 Answers1

35

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.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Thank you. I was hoping you would notice this post. Vote up for your suggestion on Data UserVoice as well. – Raheel Khan May 08 '12 at 01:10
  • For future reference also know that **in parallel is only at the code level**. From the MSDN documentation *MARS enables the interleaved execution of multiple requests within a single connection. That is, it allows a batch to run, and within its execution, it allows other requests to execute. Note, however, that MARS is defined in terms of interleaving, not in terms of parallel execution.* https://msdn.microsoft.com/en-us/library/ms131686.aspx – PedroC88 Feb 08 '16 at 20:57