24

I recently upgraded EF 6.1.3 to 6.2.0 on one of our large projects, and it has broken a significant amount of our LINQ queries. Enabling MultipleActiveResultSets causes everything to work as normal again, but I'm struggling to understand the change. We have been using EF for years and gone through multiple major version changes without any issue. If I simply revert back to 6.1.3, everything works again as expected - in fact everything works even if I explicitly disable MARS in 6.1.3.

Let me give a few simplified examples. The first problem is with nested queries:

foreach(var row in dbSet.Where(<condition>))
    foreach(var innerRow in otherDbSet.Where(_ => _.Property == row.Property))

This works fine in 6.1.3, but in 6.2.0 throws a "There is already an open DataReader..." exception. I understand the nature of the exception, and I can solve this by calling ToList() on the outer query to push the results into memory first - what I don't understand is why I didn't have to do this in 6.1.3 (even with MARS disabled). It isn't always desirable to simply load the whole outer set into memory.

This also seems to impact lazy-loaded properties. For example, we build ComboBoxes from simple queries like this:

return db.Collection
    .Where(<condition>)
    .AsEnumerable()
    .Select(_ => new ListItem(_.Id, _.LazyNavigationProperty.Description))
    .ToList();

This works fine in 6.1.3, but again in 6.2.0 throws the "There is already an open DataReader..." exception. The fix is I now have to eager-load the navigation property.

Ultimately I don't have an explicit question, I'm just trying to understand why a minor version update seemingly caused major breaking changes in how queries are handled.

Moving forward, this impacts far too many queries for us to refactor. When I was researching the problem, I saw vague warnings about enabling MARS, but nobody really gave anything concrete. Is there a compelling reason not to enable it?

amnesia
  • 1,956
  • 2
  • 18
  • 36
  • I found this https://stackoverflow.com/questions/374444/disadvantages-of-mars-multiple-active-result-sets – Bradley Uffner Nov 09 '17 at 18:51
  • 1
    I recently encountered this problem, too. Had to add `.ToList()` to the offending queries only after going from 6.1x to 6.2 – Haymaker87 Sep 10 '18 at 15:56
  • Maybe you can get more help if you create an issue over EF's GitHub repo? Even better if you provide a small, reproducible application. – jpgrassi Apr 07 '19 at 19:18
  • @Haymaker87 that's a bug in your code, not EF. A query isn't executed until you either iterate over it or call `ToArray()`, `ToList()` or any of the other methods that actually execute the query. That particular error means what it means - you tried to execute *another* query while reading the results of the first one – Panagiotis Kanavos Jun 04 '19 at 11:18
  • @amnesia EF 6.2 didn't break anything, that's caused by a bug in the code. Somehow, somewhere the code is trying to execute one query while *still* reading another's results. That's a bad idea in the first place - keeping connections longer than needed increases locking, contention and blocking in the database, harming scalability. MARS *does* allow you to read multiple result sets but that doesn't mean the perf penalty goes away – Panagiotis Kanavos Jun 04 '19 at 11:20
  • @PanagiotisKanavos It's not a bug, and I understand exactly what is happening. Did you read any of the post? – amnesia Jun 11 '19 at 17:09

2 Answers2

4

you get this error because you're iterating through a result set while trying to open another result set (while the first one did not finish yet)-> sort of lazy loading (the first 'for each' iteration in your case) -> there are a lot of ways to solve this as you've already seen for yourself: using toList (drop to memory first), because it's no longer using the datareader to open the set.

it looks like it MIGHT be related to a bug fix in 6.2 (release notes: https://entityframework.net/ef-version-history) - looks like related to: "Bug: Retrying queries or SQL commands fails with "The SqlParameter is already contained by another SqlParameterCollection.")

Regarding enabling MARS: you can find special warning here:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

Yulian
  • 66
  • 6
  • This should be the accepted answer, also, to the Q owner, the way you have it is extremely inefficient. dbSet.include(x=>x.TheRelation).where.... would be better, but even this isn't great. – Seabizkit Jun 04 '19 at 12:21
0

Entity Framework is supposed to deliver a tiny abstraction on your database model.

Such work requires performing multiple queries under the hood. The engine might also require more queries necessary when compared to the same workload encoded by hand.

This is a physiological evolution in order to be able to handle all possible user requests. Simply upgrading to a different Entity Framework version, can introduce differences on the database workload emitted under the hood.

MARS is required as EF changed the way object retrieval is performed (particularly, within loops combined with lazy loading). Unfortunately, most of the times, you are required to use MARS when using Entity Framework.

Nowadays, using async/await usually requires MARS too.

You can find additional information about how related entities are loaded on MSDN Loading Related Objects and Enabling Multiple Active Result Sets. This interesting blog goes a little more deeper.

Yennefer
  • 5,704
  • 7
  • 31
  • 44