ef6
ef6 copied to clipboard
dotnet
Consider supporting Microsoft.Data.SqlClient in EF6
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.
@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
@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.
My vote is stay with System.Data.SqlClient - it will receive no new features, just like EF6 🤡
I think the tension is between:
- Having to change all your using statements, or
- Missing features that you were using on .NET Framework
For now, nothing but Always Encrypted, I think. But that one alone is a pretty big deal.
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.
@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.
@CZEMacLeod The existing provider will still be in-the-box. We aren't changing that.
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?
@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.
@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.
@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?
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)
@rythos42 AzureKeyVaultProvider for .NET Core has been released! https://www.nuget.org/packages/Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider/
@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.
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.
To add a use case, in order to support SQL dependency tracking in Application Insights as documented here, I need Microsoft.Data.SqlClient support.
@collinstevens several of the linked approaches support SDSqlClient. What platform are you running on?
@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.
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?
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.
@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).
@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
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).~~
@madelson System.Data.SqlClient DOES work on .NET Core (mac, Linux. Windows)