Consider supporting Microsoft.Data.SqlClient in EF6

[divega]

Currently EF6 depends on System.Data.SqlClient which is included in .NET Framework, but SQL Server is moving development to Microsoft.Data.SqlClient, where any new features will be supported.

EF6 is in a different situation from EF Core, in that we don't have any breaking change release ahead of us, so I think we probably shouldn't change what provider we use by default, but it should be easy to enable the new one to work.

[ajcvickers]

@divega I was just thinking I needed to file this! :-)

What do you think about depending on the new package (and only the new package) for .NET Core, and keeping the old dependency for .NET Framework?

/cc @bricelam

[divega]

@ajcvickers, from the perspective that the combination of EF6 + .NET Core is completely new and has no compat baggage per se, it seems alright. But it feels a bit weird to diverge so much across TFMs, plus in principle I think we should support the new provider in .NET Framework anyway (I assume in a new package), so not sure it buys us much in terms of simplicity. But this is just my first take on it. I am happy to discuss it.

[bricelam]

Will this make porting apps to .NET Core easier or harder?

[ErikEJ]

My vote is stay with System.Data.SqlClient - it will receive no new features, just like EF6 🤡

[bricelam]

I think the tension is between:

• Having to change all your using statements, or
• Missing features that you were using on .NET Framework

[ErikEJ]

What missing features (other than Always Encrypted) ?

[divega]

For now, nothing but Always Encrypted, I think. But that one alone is a pretty big deal.

[ajcvickers]

We decided that the existing provider will continue to use System.Data.SqlClient, which is non-breaking. However, we will likely release a new provider that uses Microsoft.Data.SqlClient at some point post 6.3, since it closes important gaps on .NET Core, such as Always Encrypted.

[jtlimson]

+1

[CZEMacLeod]

@ajcvickers Does this mean that the existing SqlClient provider will be shipped as a separate NuGet package (as a dependency?) and allow you to use it or the new MS.D.SC or will we be landed with having to load both providers / assemblies into memory?
I always found it slightly odd that even if you were using sqlite or another provider, you ended up with the SqlServer provider anyway.

[ajcvickers]

@CZEMacLeod The existing provider will still be in-the-box. We aren't changing that.

[dmmusil]

However, we will likely release a new provider that uses Microsoft.Data.SqlClient at some point post 6.3

Will this provider be available for .NET Framework or will we need to swap it out when upgrading from Framework to Core? Any updated timeline yet?

[CZEMacLeod]

@dmmusil @ajcvickers The Microsoft.Data.SqlClient package seems to support net46, netcore2.1 and netstandard2.0 so I imagine the EF6 provider could target net46 & netstandard2.1 which would match both the sqlclient provider and the EntityFramework 'minimum' supported versions.
I cannot comment on the decision Microsoft will take on this wrt the amount of effort they are willing to put into developing the EF6 provider, and the platform(s)/runtime(s) they will support; other than I hope they keep as much compatibility and as broad a scope as possible to allow people to incrementally update.
I know I am still targeting net462 for much of our codebase, and netstandard2.0 where possible, and simply cannot update some of it due to missing or 'legacy' parts of the framework used that would be too much work to recode. Obviously I use netcore2.2 or higher for new stuff, and reuse the netstandard2.0 code where possible but for large projects this is not always practical.

[ajcvickers]

@dmmusil @CZEMacLeod It will likely multi-target .NET Framework and .NET Standard 2.1, like the existing provider.

When/if we do that will depend mostly on adoption of EF6 on .NET Core. It's not planned for the near future--we'll look at adoption numbers in the coming months and gather feedback (e.g. this issue) and make a call then.

[dmmusil]

@ajcvickers Thank you. When considering adoption numbers, remember there's lots of us (no idea how many, but I'm hoping enough) that can't adopt because we need Always Encrypted support. Until then we will be on EF 6 in .NET 4.x.

Also for Always Encrypted support, it would need to target .NET Core as well right?

[ErikEJ]

@dmmusil the latest M.D.S. already supports Always Encrypted.

[rythos42]

Adding a use-case for consideration --

I have 2 APIs (one is internal, the other public), both using a shared data library. They both use EF6, .NET Framework, Always Encrypted, Azure and KeyVault. I'm trying to convert the public one to .NET Core.

It looks like I currently can't do that because EF6 requires System.Data.SqlClient and thus Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider, and the latter doesn't work on .NET Core.

So in your decisions, it'd be nice if I could use Microsoft.Data.SqlClient in both Framework and Core so this shared library works for both.

(unless I'm missing something and this is already possible, which would be lovely! :D)

[ErikEJ]

@rythos42 AzureKeyVaultProvider for .NET Core has been released! https://www.nuget.org/packages/Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider/

[rythos42]

@ErikEJ This appears to not work in my scenario.

API (.NET Core) calls EF6 (.NET Core) calls S.D.S (.NET Core) and then isn't calling Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider, presumably because it is for M.D.S and not for S.D.S?

The other possible chain, API (.NET Core) calls EF6 (.NET Core) calls S.D.S (.NET Core) calls Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider doesn't work because the latter doesn't support Core.

[mwittmann]

For interoperability, it would be great for EF to support Microsoft.Data.SqlClient. There's a mention in this thread that it would be included in EF 6.3 or later. Any updates? We're at 6.4... is there an M.D.SC EF provider? The dependency list shows only System.Data.SqlClient.

[collinstevens]

To add a use case, in order to support SQL dependency tracking in Application Insights as documented here, I need Microsoft.Data.SqlClient support.

[ErikEJ]

@collinstevens several of the linked approaches support SDSqlClient. What platform are you running on?

[collinstevens]

@ErikEJ the applications we support are deployed using IIS and Azure App Services; local development is also done with IIS Express. Having to support the configuration of Azure App Services and the installation of additional IIS modules across the plethora of applications is more onerous than it seems; also having the logs show up locally in Visual Studio Application Insights with IIS Express is extremely helpful.

We already get the nice quality of life with our .NET Core applications using Entity Framework Core and would like to see the same developer experience in a bunch of our NETFX applications.

It's also helpful to say "install the Microsoft.Data.SqlClient NuGet package for Entity Framework on NETFX projects" than to say "install this IIS module on your web servers" or "add the configurations to your ARM/Terraform/IaC tool for Azure App Services" to the other teams in the organization.

[ErikEJ]

Makes sense. I have looked at the feasibility of doing this, the main blocker was, that EF6 still supports .NET 4.0, with M.D.S. supporting .NET Standard 2.0 only (meaning .NET 4.7.2 or later) - would that work for your .NETFX apps?

[collinstevens]

Makes sense. I have looked at the feasibility of doing this, the main blocker was, that EF6 still supports .NET 4.0, with M.D.S. supporting .NET Standard 2.0 only (meaning .NET 4.7.2 or later) - would that work for your .NETFX apps?

Unfortunately not, we are still on .NET 4.6.1 for quite a few apps.

[rikat-ms]

Hi @ajcvickers, is it possible to tell me ETA for supporting Microsoft.Data.SqlClient in EF6?

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/plan#theme-clear-path-forward-from-ef6

In addition, we plan to make it clear on the legacy EF6 GitHub repo that we are not planning any future work on EF6. The exception to this is that we plan to add support for using EF6 with Microsoft.Data.SqlClient. This will be limited to runtime support. Use of the EF6 designer in Visual Studio will still require System.Data.SqlClient.

Some users must consider their migration plan based on it because:
When we connect to Azure SQL Database with Always Encrypted from EF6 + System.Data.SqlClient, we can use Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider + Microsoft.Azure.KeyVault. However, Microsoft.Azure.KeyVault has been deprecated and its EOS is March in 2023. There is no way for legacy EF6 users to keep this type of applications right now. So, it would be great if EF6 would work with Microsoft.Data.SqlClient as soon as possible to be ready for EOS.

[ajcvickers]

@rikat-ms We're still planning to do it this year.

[madelson]

@ajcvickers is there a rough design proposal for the community to react to?

[ajcvickers]

@madelson Yes, we intend to copy what @ErikEJ has already done: https://github.com/ErikEJ/EntityFramework6PowerTools/blob/community/src/ErikEJ.EntityFramework.SqlServer/readme.md

[madelson]

@ajcvickers / @ErikEJ thanks in general this looks good to me.

One question: do you think it would be possible to have the existing SqlFunctions class (and any similar classes that are used in LINQ queries) work for either provider? That would make it easier to write "portable" LINQ queries that wouldn't need to change across the provider migration.

[ErikEJ]

@madelson Have you tried it? I think they might just work. (Only renamed because of naming conflicts)

If so, maybe MicrosoftSqlFunctions class can just be removed?

[madelson]

@ErikEJ I have not tried it; was just reacting to the linked docs page.

[ErikEJ]

@madelson I just created ErikEJ#102

[stevetalkscode]

@ajcvickers - As part of this change, are there also plans to also update the SqlServerTypes referenced to the SQL Server 16 version (currently in pre-release RC - https://www.nuget.org/packages/Microsoft.SqlServer.Types/160.900.6-rc0 ) so the DbGeography/DbGeometry will work with EF6.x.x. in .NET 6? (See also dotnet/SqlClient#30 (comment)).

This would be a huge help rather than having to move to EF Core 6 and NTS

[ajcvickers]

@stevetalkscode That's not currently part of the plan here.

[stevetalkscode]

Thanks for the prompt reply.

[ErikEJ]

@stevetalkscode If you are interested, feel free to add a feature request to ErikEJ.EntityFramework.SqlServer, and I will investigate feasibility: https://github.com/ErikEJ/EntityFramework6PowerTools/issues

[stevetalkscode]

Thanks @ErikEJ for the prompt response to this in ErikEJ#103

[lukeskinner]

Hello! Is there any ETA or update on this?

We have a requirement to use Microsoft.Sql.DataClient for Azure 2FA authentication on SQL which is working with EF6 using the solution from Erik (thank you!).

At the moment we have powershell scripts we run to convert our EDMXs from one data provider (System.Data vs Microsoft.Data) whenever we need to open up the EDMX to update it from the database.

This isn't a great option for us long term as it causes confusion within our development team why we need powershell scripts to open EDMXs. Our application is not able to upgrade to newer versions of EF currently (such as core).

[ErikEJ]

@lukeskinner "This" will be a runtime only solution, and you will have the same design time issues/constraints as in my provider.

[lawrence-vo]

Just stumbled on this today. Is this a feature that is going to be released in the last two days of this year? Or should I use @ErikEJ 's unofficial package?

Thanks.

[ErikEJ]

@lawrence-vo if you need it today just use my package.

[lawrence-vo]

@ErikEJ any chance you can help me debug? I am getting a Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x64.dll' error message and would like some guidance,

Thanks!

[ErikEJ]

@lawrence-vo sure, please post a full repro in my github repository

[ErikEJ]

Five years in the making! Finally a preview release of EF6 using Microsoft.Data.SqlClient is available: #2237 (comment)

[asalvo]

OMG. I was just looking at this again this morning.

[AndersMad]

Hej @ErikEJ, first - thanks for your work! and sry for my ignorance here - but:

1. The EF release is "just" the same as using ErikEJ.EntityFramework.SqlServer right?
2. I removed above package, and before updating to the pre-relase EF6, it all worked even though I removed all System.* refs. back when I migrated to your package (cleaned all obj/dll folders etc.) but I guess it silently goes back to just using old sql client then?
3. Any quick way to know what client it used? Except for trying to connect to a server that needs e.g. Encrypt?

[ErikEJ]

@AndersMad you must use the new Microsoft.EntityFramework.SqlServer package - it is unclear if you do that.

You can get the type of DbConnection to verify.

[AndersMad]

So for Q1 I assume its basically the same.
As for Q2 I just weird that I removed your package (before upgrading to EF 6.5) and it still uses EntityConnection.StoreConnection == Microsoft.Data.SqlClient.. oh well - never mind, I'll just run with the new one and make sure its the right client, thanks.

[ErikEJ]

@AndersMad Q1; Microsoft.EntityFramework.SqlServer and ErikEJ.EntityFramework.SqlServer provides the same features, yes.

Q2: It is not clear if you are using EDMX and/or app.config based config, in these cases you force the use of Microsoft.Data.SqlClient via config.