Entity Framework is an amazing ORM. It has differences with other ORMs like Hibernate or Doctrine, but at the same time feels more comfortable. Two key differences with other ORMs are LINQ as its query language (LINQ it’s a compiled language, if you like to rename stuff you know how many errors this feature can prevent) and being able to use foreign key values without having to join with the related entity.
But, it has a poor proxy pattern. Navigation properties, the Entity Framework name for relationships between entities, can be accessed with lazy loading like in other ORMs, but in Entity Framework you must load the entire result set in a list before traversing it or an exception will show up saying “There is already an open DataReader associated with this Connection”. This happens because Entity Framework attempts to start a new data reader while it is already reading the data reader for the previous collection.
While it’s not a big deal when you have an upfront database design that won’t ever change, this usually breaks code when a new relationship is added to an entity that already exists if you need to use this new relationship in data transfer objects or in every place where you are retrieving data from this entity. So, what can we do to make our codebase more reliable and prevent production errors? SQL Server has a feature called Multiple Active Result Sets (MARS). This feature allows the client code to open more than one DataReader at the same time. It clearly fixes it, while I don’t recommend using it in development because this error is actually telling us that we are probably doing something wrong. In development, this exception can help you to prevent performance problems lead by lazy loading with big datasets. When retrieving data from a navigation property, there is a 99% chance that what you actually wanted to do was to load your data eagerly. So, if you don’t have MARS enabled in Debug Mode, these issues will show up with the so-called exception. But if you don’t want your production code to fail unexpectedly after a database update, then enable MARS only in Release. The only needed change is to change your Connection String like this.
Data Source=MSSQL; Initial Catalog=AdventureWorks; Integrated Security=SSPI; MultipleActiveResultSets=True
Hope this helps!