Upgrade to Microsoft.Data.SqlClient v2.1.0

[cheenamalhotra]

Currently EF Core 5.0 references v2.0.1 of Microsoft.Data.SqlClient that can cause some issues we recently addressed in v2.1.0:
Release Notes

Most important issues for EF users that were solved with v2.1.0:

• Microsoft.Data.SqlClient.SNI.pdb files present in Native SNI runtime NuGet packages, that are now removed.
  Microsoft.Data.SqlClient.SNI.pdb not found with V2.0.0 SqlClient#652 (comment) - fixed by Update SNI to latest v2.1.1 for fixing pdb issues in .NET Core SqlClient#764
• InvalidOperationException and NotSupportedException errors due to WriteAsync collisions (important fix as impact was high) InvalidOperationException when writing a lot of packets asynchronously and using managed SNI implementation SqlClient#786 (comment) - fixed by Fixes "InvalidOperationException" errors by performing async operations in SemaphoreSlim SqlClient#796

and many more bug fixes :)

Please update dependency on Microsoft.Data.SqlCLient to v2.1.0 when appropriate.

[jjxtra]

The short summary is:

• Ef core 5.0.0 package for sql server is referencing the microsoft sql client nuget version 2.0.1, it needs to reference 2.1.0.
• The issue can be avoided by manually adding microsoft sql client package 2.1.0 into the project, forcing the ef core sql server package to reference the newer version.
• But it took me several days to figure this out, hopefully a 5.0.1 hotfix can come out to reference the newer package and save other people lost time.
• I think only self contained builds are impacted, haven't tested otherwise since I have no use for non-self contained builds at the moment.

[ajcvickers]

@cheenamalhotra Done automatically already for 6.0: #23434

We generally don't update to a new point release in a patch, but we will consider it if the issues fixed are significant.

[olljanat]

@cheenamalhotra @ajcvickers How about EF Core 3.1? Will it switch to SqlClient 2.x on some point or will those fixes to be ported to SqlClient 1.x?

[ErikEJ]

@olljanat You can just update to use 2.1.0 with EF Core 3.x: https://erikej.github.io/efcore/sqlclient/2020/03/22/update_mds.html

[olljanat]

@ErikEJ but are those versions compatible? I assume that there is some breaking change which why version number was raised from 1.x to 2.x?

[ErikEJ]

Yes, no breaking changes for EF Core

[ajcvickers]

Discussed in triage. We will hold off on updating the dependency in 5.0.x for now to allow further feedback on the SqlClient 2.1, since it is newly released. We will then make a call as to whether or not to take this to Tactics.

[ajcvickers]

We discussed this again and decided not to bump to a new point release in out patch release. This is not in the spirit of semantic versioning. The correct thing to do is for people to explicitly specify the higher package version. EF Core 6.0 will depend on the latest released M.D.SqlClient package.

[drdamour]

Yes, no breaking changes for EF Core

is there a support matrix of SQL Data Client for EF Core SQL Provider somewhere?

[ErikEJ]

There is some general advice here: https://docs.microsoft.com/en-us/ef/core/providers/sql-server/?tabs=dotnet-core-cli (there is some known issues with 3.0.0 )

[RyanThomas73]

There is some general advice here: https://docs.microsoft.com/en-us/ef/core/providers/sql-server/?tabs=dotnet-core-cli (there is some known issues with 3.0.0 )

@ErikEJ Do you have links for any of the known issues using EF Core with the Microsoft.Data.SqlClient v3.0.0 package?
Our immediate case of concern is compatibility between EFCore3.1 with the Microsoft.Data.SqlClient 3.0 package.

[ErikEJ]

The only issue I know of is the row version null fix, where the AppContext switch is not working as expected, a PR is currently under review. If that gets released with 3.0.1, then you probably still need the AppContext switch with EF Core, but not sure.

[drdamour]

looks like the fix to this made it to 3.0.1 via dotnet/SqlClient#1246