When should I use MultipleActiveResultSets=True when working with ASP.NET Core 3.0 and SQL Server 2019+?

Question

Most applications I have programmed do not use MultipleActiveResultSets=True, but I have seen the option being enabled in a couple of them and in a few tutorials.

This SO question deals with the same topic, but it is very old and I believe that things have changed much in the mean time.

OP argues about executing some non-queries, while performing an ExecuteReader. In this case I believe it to be a bad design since it might be replaced with some batch-style operation, perhaps a stored procedure to minimize the number of round-trips.

When using Entity Framework with ASP.NET Core and receiving an exception related to the data context executing already something in the scope, I treat it as a bug and not thinking about enabling MARS.

Reading this MS Docs article I see that one should pay attention to various aspects such as options (ANSI_NULLS, DATE_FORMAT, LANGUAGE, TEXTSIZE), security context, current database, state variables (@@ERROR, @@ROWCOUNT, @@FETCH_STATUS, @@IDENTITY) when working with MARS enabled.

Also, 10+ years mean much more capable servers being able to hold much more connections if this is really needed (caching should help reduce this need).

So I am wondering if I ever have to consider enabling MARS when working with modern ASP.NET Core applications (3.0+).

Question: When should I use MultipleActiveResultSets=True when working with ASP.NET Core 3.0 and SQL Server 2019+?

Edit to address feedback

I am not interested in an exhaustive analysis, but a couple of appropriate contexts to justify using MARS or not.

A typical example in ASP.NET Core applications is to have database context as scoped (get a database connection from the connection pool per request, make changes, usually one transaction per request/scope). So far, I have treated errors related to multiple queries per connection as my own fault to avoid MARS, but I did so without understanding actually why.

Answer 1

Yes, MARS still have their place in modern data access frameworks because they provide the (efficient) solution of the following two major general querying problems - streaming (i.e. non buffering) (1) data with eager loaded related data collections and (2) lazy loaded related data of any type.

---

In both cases, executing a query is expected to provide IEnumerator<T> (or its async version) which is the object equivalent of data reader (or database forward only read only cursor). So each MoveNext{Async} should be mapped to ReadNext of the data reader and is expected provide one fully populated T, w/o buffering ahead all others. In order to achieve that, the underlying data reader must be kept open during the enumeration, and close when it is complete or aborted earlier (for instance, FirstOrDefault()) - one of the reasons IEnumerator<T> is IDisposable.

Now imagine what happens if you have lazy loading enabled. You get some entity and access some navigation property. This triggers lazy load operation, which of course needs to execute reader to get the data from the database. Since the outer reader is still open (active), w/o MARS this operation will simply fail with runtime exception. Not good. And there is nothing you or framework can do other than either buffer everything in advance (basically switching to snapshot mode) or not use lazy loading.

Let say you don't use lazy loading (it's not recommended anyway). But your entities contain related data collections and you want to eager load them. Relational database SQL provide flat result sets, i.e. does not support "nested collections" inside query result set. So how to stream such data?

There are basically two solutions.

First is based on single SQL query which contains all master + correlated table columns, and returns some sort of hybrid records where some fields apply to specific result and other are nulls. This approach is used by EF6 and EF Core 3.0+. While EF Core 1.x/2.x uses the other approach and EF Core 5.0 allows you to choose between the two. Why? Because when you have several sub collections, this tend to produce quite ineffective queries (both execution and processing the result set since it transfers a lot of unnecessary data).

Second is using separate queries - one for the main result set and one for each correlated sub collection. The idea is simple. Since usually both PKs and FKs are indexed, the database can effectively return them ordered by these columns using index (which is needed for join operations anyway), and then they can easily be merged client side by reading ahead (buffering) maximum one record.

Sounds good, isn't it? With one small, but important caveat - it needs MARS! Otherwise, it has to switch to buffering mode. Which totally defeats the idea of IEnumerator and in async version - the cancellation concept. You can see the effect of the latter in my answer to How can I abort a running EF Core Query using GetAsyncEnumerator?, and at the end the suggestion was to enable MARS.

For more info about EF Core querying, see Split queries and How Queries Work (and basically the whole Query data) sections of the official EF Core documentation.

Side note Separate connection isn't really an option, especially if one needs transaction levels like repeatable reads. MARS is providing the exact abstraction needed over connection. And AFAIK inside SP one can open simultaneously as many cursors as they want, hence not sure what's the exact problem with ADO connection layer and why MARS is considered optional feature which needs enabling, rather than just out of the box functionality. The ORM though could try utilizing separate connection behind the scenes when applicable. EF Core currently doesn't.

---

So to recap shortly, if you don't use lazy loading (likely) and don't have correlated collections (unlikely - one-to-many is quite common, and the correlated collection doesn't necessarily mean navigation property and Include - same applies to projections to list and similar members), then you don't need MARS. Otherwise better enable them - they are feature, so use it.