Configure MARS in SqlServer

[smitpatel]

Since we have split queries again, SqlServer may fail without MARS.
We could add DbOption configuration for users to tell us if they have MARS on or not.

[ErikEJ]

Is MARS not already specified in the connection string?

[smitpatel]

Yes, it is. I guess we can just evaluate the connection string and determine too. I don't know how we did in 2.x.
Mainly in query we need flag that if MARS is on or not so we can appropriately buffer for split query scenario so exception is not thrown.

[smitpatel]

efcore/src/EFCore.SqlServer/Storage/Internal/SqlServerConnection.cs

Lines 62 to 65 in 01da710

	public override bool IsMultipleActiveResultSetsEnabled
	=> (bool)(_multipleActiveResultSetsEnabled
	?? (_multipleActiveResultSetsEnabled
	= new SqlConnectionStringBuilder(ConnectionString).MultipleActiveResultSets));

[ajcvickers]

Note from triage: we will try bringing back the 2.2 code for this. We should also decide whether or not to assume that a provider can do MARS.

[roji]

For other providers implementing preview6, the current implementation of query splitting assumes MARS capabilities by default, and so will fail if the provider doesn't support them. To make EF Core buffer instead, create your own subclass of RelationalQueryCompilationContext (defining a factory that returns it, and registering that factory with EF Core), with the following:

public override bool IsBuffering => base.IsBuffering || IsSplitQuery;

Since MARS once again is an exception in the provider landscape (although it happens to be supported by Sqlite and SQL Server when enabled), I really think it would be better to not require all providers to do this - discoverability is going to be difficult, and the buffering behavior will always work across all providers, where MARS is strictly-speaking an optimization only. It seems pretty trivial for RelationalQCC to expose a hook that allows Sqlite and SQL Server to opt in, rather than forcing everyone else to opt out.

/cc @lauxjpn

[lauxjpn]

Since MARS once again is an exception in the provider landscape (although it happens to be supported by Sqlite and SQL Server when enabled), I really think it would be better to not require all providers to do this - discoverability is going to be difficult, and the buffering behavior will always work across all providers, where MARS is strictly-speaking an optimization only. It seems pretty trivial for RelationalQCC to expose a hook that allows Sqlite and SQL Server to opt in, rather than forcing everyone else to opt out.

I second that. EF Core should default to the most common/reasonable approach across the provider landscape, not the SQL Server implementation.