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.

[tbertenshaw]

Another vote for this. Is there any news on if its planned?

[CZEMacLeod]

@ErikEJ I thought net461 was the earliest that supported netstandard20? Announcing .NET Standard 2.0 Also MDSC has a net46 target (according to its dependencies on NuGet - Microsoft.Data.SqlClient Regardless - it would be great to use EF6 with MDSC - and indeed perhaps split the sqlclient into it's own package (or packages to support SDSC and MDSC).

[ErikEJ]

@CZEMacLeod

I thought net461 was the earliest that supported netstandard20?

It is in theory, but unless you enjoy pain, 4.7.2 should be used, see footnote 1 on this page: https://github.com/dotnet/standard/blob/master/docs/versions.md

I like your idea of adding an EntityFramework.SqlServer.MDS package! Unfortunately, the main EF6 package also depends on S.D.S ! https://github.com/dotnet/ef6/blob/master/src/EntityFramework/EntityFramework.csproj#L24

[madelson]

Another upvote for this; this feels really important. I imagine that this could be quite simple (since the code should be the same between SDS and MDS). Would the team support a community contribution for this?

Here's my use-case:

• Upgrading a large set of EF6 existing applications (some legacy) and libraries to .NET Core
• EF6 supports .NET Core. Yay!
• System.Data.SqlClient does not work on linux (in my understanding)... but Microsoft.Data.SqlClient does!
• We'd like to switch to Microsoft.Data.SqlClient so that we can take advantage of its new features and bugfixes, and avoid having to deal with oddities like having separate connection pools for EF vs. non-EF queries.
• But EF isn't compatible with Microsoft.Data.SqlClient :-(

It feels like we're so close but not quite there yet. I'd be fine with this requiring us to pass in an instance of MDS.SqlClient to the DbContext constructor (although I also like other proposals to make this the default on .NET Core or at least on Linux).

[ErikEJ]

@madelson System.Data.SqlClient DOES work on .NET Core (mac, Linux. Windows)

[cjindustries]

Another vote for this from me. It would be really useful for us.

We're EF6 (in part) and .Net 4.7.2 at the moment. We use the App Insights SDK and want to make the SQL command text visible for some environments. Only by using Microsoft.Data.SqlClient will this be possible unless we install the instrumentation engine on our servers. It would be good to avoid this.

[benjaminjnr]

Any news? Is there EF 6.5 planned that will support MDS? It makes it easy to modernise our apps without having to go all in on .NET Core at once.

[vhhughes]

We're right on the verge of moving 50+ ASP.NET Framework 4.7-4.8 web apps to App Services in Azure, where we intend to use Azure SQL Databases and use Managed Identity for authentication (since Integrated Auth was our old approach for VM-hosted web apps). We can't afford to make significant code changes to them all--in fact, we are looking to do as few changes as possible. We use EF6 widely and just discovered this significant limitation today. With EF6 not supporting use of Microsoft.Data.SqlClient (not even as a provider?), we're belly up trying to use any non-username/password Azure SQL authentication. This is now a major obstacle to moving any of these 50+ to Azure. As nice a thought it is, we can't afford to move them to .NET Core. Considering Microsoft's hope that customers will move to using Azure (and PaaS, in particular), I'm really surprised that this has been so low a priority that I can find a two-year-old thread on the topic where things haven't moved an inch.

Really disheartening to think that ASP.NET Framework apps are so little supported for cloud. Unless this is expected to change really soon, we're going to have to consider some alternative to Azure PaaS. Not willing to let our security suffer like this.

[madelson]

I came across another case where this would be useful today, which is https://github.com/dotnet/SqlClient/issues/811#issuecomment-731463485. The connection string incompatibility means that if we're building connection strings with Microsoft.Data.SqlClient.SqlConnectionStringBuilder in our .NET Core app we can't then pass them to EF6. It will sometimes work and sometimes not, depending on what keys are used.

I'd like to switch my app over to fully use Microsoft.Data.SqlClient, even in EF6. This would avoid that issue.

[ErikEJ]

FYI, I just published a preview package: https://github.com/ErikEJ/EntityFramework6PowerTools/blob/community/README.md#ef6-sql-server--azure-sql-database-provider-based-on-microsoftdatasqlclient

[Inspyro]

@madelson System.Data.SqlClient DOES work on .NET Core (mac, Linux. Windows)

I already found a bug when using the .Net Core Version of System.Data.SqlClient, but it is not supported anymore in .Net Core, see reply here: https://github.com/dotnet/SqlClient/issues/1204

System.Data.SqlClient in .NET Core (CoreFx) is no longer actively maintained

Will the preview package with Microsoft.Data.SqlClient support be released as EF6 Update?

[ErikEJ]

@Inspyro Did you try the preview package? If users try it out and let me know if it is working for them, it could move out of preview state!

Will the preview package with Microsoft.Data.SqlClient support be released as EF6 Update?

Maybe, but don't hold your breath: https://github.com/ErikEJ/EntityFramework6PowerTools/issues/82#issuecomment-889384213

[Inspyro]

@Inspyro Did you try the preview package?

Not so far, but I am considering it. Especially if the bug in SqlClient is not fixed anymore

[lscorcia]

+1 for this. We are currently evaluating ErikEJ's component but an official release would be great. We are slowly migrating our legacy codebase (WebForms + shared cross-targeted business logic libraries) to .net5 but EFCore is not viable due to missing distributed transaction support.

[ErikEJ]

@lscorcia Don't hold your breath https://github.com/ErikEJ/EntityFramework6PowerTools/issues/82#issuecomment-889384213

[ajcvickers]

We're planning for next year now, and so far this is tentatively in the plan.

[Sebbs128]

I'd also like to +1 this if it helps ensure it will be in 6.5.

And also thank @ErikEJ for your MDSC provider. It really helped me out.

FWIW, our scenario is we have a group of .NET 4.8 ASP.NET apps talking to an Azure SQL DB. To date we've been using a SQL User+Password stored in the web.config files (and thus, also in source control), and we'd like to tighten that up by leveraging Azure AD (which already contains all our users+security groups, and being a cloud-first/only org there's no on-prem AD for federation to work). Where the MDSC provider saved the day was allowing auth to be passed through from Visual Studio (using Authentication=Active Directory Default in the connection string).

(And yes, we are planning on shifting to EF Core and ASP.Net Core, but that's a massive effort that needs to take place alongside BAU work).

[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 https://github.com/ErikEJ/EntityFramework6PowerTools/issues/102