Entity Framework 4.1 Code First Multipleactiveresultsets

.net connection-string ef-code-first entity-framework sql-server

Question

I ask for your patience since this is my first EF project.

You grab an entity from the context, change its values, and then call context when updating an object like Department. SaveChanges. However, EF does not find that amusing if you edit Department.Employees.

I looked online and found the connection string setting Multipleactiveresultsets=true, however I want to know if:

  • Is this the best course of action?
  • What should I watch out for? Does this negatively impact performance?
1
14
7/21/2013 4:10:54 AM

Accepted Answer

Only if you wish to run many queries concurrently on the same connection is MARS required. If you act in a manner similar to 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 can you stop that?

  • Instead of lazy loading, use eager loadingcontext.Departments.Include(d => d.Employees)
  • Before employing lazy loading, materialize the whole result set for the department. It entails denying access to insider staff.
  • The aforementioned example will easily function if MARS is enabled.

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

Depending on the issue you're attempting to resolve Accessing their personnel collection will start a separate query for each department if you have several departments to handle. You have N departments, one query to retrieve them, and you will run an extra query for each department, totaling N+1 inquiries. This is known as the N+1 issue. This will be a performance killer for many departments.

Eager loading is also not a foolproof fix. It is negatively impact performance-able. There are occasions when it is sufficient to run separate queries to get each required department and person. If you have lazy loading disabled, it should automatically adjust your relations and populate the Employees property for you. By the way, I created a idea for Data UserVoice that has this functionality by default.

35
5/23/2017 11:48:35 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow