-
-
Notifications
You must be signed in to change notification settings - Fork 27
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Microsoft.Data.SqlClient provider issues and TODOs #82
Comments
@ajcvickers given the current lack of plans for the EF6 repo, I assume that a PR to add support for Microsoft.Data.SqlClient is out of the question? Given that, are you OK with the package name (ErikEJ.EntityFramework.SqlServer) ? |
Couldn't have picked a better name myself. 😉 Regarding the transforms, I think (based on dotnet#953 (comment)) that they're not actually needed. But I honestly can't remember the details. |
Hi, thank you very much for this package! It would solve our issues, however I can't make it work yet. My scenario: Migrated from a working .NET framework 4.6.1 app using EF6 to communicate with an AzureDb with Always Encrypted to a .NET Core 3.1 app. Had to use Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider and thus Microsoft.Data.SqlClient. This (of course) did not work because M.D.S was not supported in EF6. Tried using this new package in the following way:
Using a constructor with an exising SQL connection (using Microsoft.Data.SqlClient) containing the connection string and token for access to the DB and master pw for always encrypted. However this fails on the first call to the DB (in another connector class):
Is this a scenario that should work now? Or did I miss or something / configured incorrectly? Help would be appreciated. Thanks! |
@jvanderwoude80 I am not able to repro, please provide a full repro. Maybe this needs to run before an data access code (if this fixes your issue, I can add this line to the DbConfiguration class)
|
@ErikEJ thanks! Sorry, full repro is kinda difficult because the app is using a private incompany (confidential) nuget framework containing the entity code and models of the specific DB, and (maybe part of the problem) also other DB connectors also using EF6 (but with System.Data.SqlClient) so both ErikEJ.EntityFramework.SqlServer.dll and EntityFramework.SqlServer.dll are in the bin folder.. So I hope this code fragments will do.. Anyway, above line of code (when used in the constructor above) does change the behavior, but gives this error: Any ideas? Do you need other code fragments or debug information that will help? Thanks again! |
Are you using .NET Framework libraries from .NET Core? That could cause issues, I believe. The need to be rebuilt for .NET Standard or .NET Core/5. Happy to take a Teams session to get to the bottom of this, if you think that may help. |
I'm not sure, but I guess a teams session would surely help to sort it out. |
You can contact me on ejlskov at hotmail dot com... |
@jvanderwoude80 You had multiple DbContext classes in your solution, and the fix was to add the attribute to them all!
|
Yes, that fixed it. Thank you very much! Summarizing: With your package I now have a working .NET Core 3.1 solution with EF6 using the new Microsoft.Data.SqlClient. Also always encrypted (on a Azure SQL DB) using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider is working. |
I have just published a 1.0-rc1 package based on your feedback! |
Thanks! Confirmed it also works.
|
Hey folks! Having a problem getting this going in our project with the RC1 build. We're still using EDMX files because they are wicked awesome for codegen. To use EDMX files in EF6 on .NET Core, it requires building an EntityConnection manually, so we wrote a constructor overload and a helper to make this happen. DbContext Code: using CloudNimble.BurnRate.Core;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Infrastructure;
namespace CloudNimble.BurnRate.Data
{
/// <summary>
///
/// </summary>
public partial class BurnRateContext : DbContext
{
/// <summary>
///
/// </summary>
public BurnRateContext() : base("name=SomeConnectionString")
{
}
/// <summary>
/// Creates a new <see cref="BurnRateContext"/> instance for a given connection string.
/// </summary>
/// <param name="sqlConnectionString">A SqlClient connection string that does not have EntityClient metadata.</param>
public BurnRateContext(string sqlConnectionString) : base(GetEntityConnection(sqlConnectionString), true)
{
}
/// <summary>
///
/// </summary>
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
/// <summary>
///
/// </summary>
/// <param name="sqlConnectionString">A SqlClient connection string that does not have EntityClient metadata.</param>
/// <returns>an <see cref="EntityConnection" /> object populated with the default values for an BurnRateContext EF6 connection.</returns>
private static EntityConnection GetEntityConnection(string sqlConnectionString)
{
var entityBuilder = new EntityConnectionStringBuilder()
{
Provider = "Microsoft.Data.SqlClient",
ProviderConnectionString = sqlConnectionString,
Metadata = @"res://*/EntityModel.csdl|res://*/EntityModel.ssdl|res://*/EntityModel.msl",
};
return new EntityConnection(entityBuilder.ToString());
}
#endregion
}
} Now we can pull the connectionstring from .NET Core Configuration, pass it in, and get the job done. Except that it appears that EntityConnectionStringBuilder is hard-coded to use System.Data.SqlClient.SqlConnection.
Would it be possible to whip up replacement methods so we can still use this approach? Or is there a better way to accomplish this that doesn't require more coding on your end? Thanks! /cc @caldwell0414 |
@robertmclaws I am not sure I can see a connection between the EntityConnectionBuilder approach and the call stack. Have you decorated all your DbContext classes with the DbConfigurationType attribute? Apparently that is important. |
I doubt that is true, I have used it with other providers previously. |
So, does calling Also, do I need to call Thanks! |
I have not tested that, why dont you try?
No: EntityFramework6PowerTools/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlDbConfiguration.cs Line 13 in 9598dd5
|
The important part is that all DbContexts classes in your solution must be configured to use the new provider... |
I dug deeper into that. This did fix my issue, however it turned out that while my solution was not using or referencing other DbContext classes, the framework used by the solution did! One database call was performed before my DbContext that needed Microsoft.Data.SqlClient was called. If only this earlier DbContext is configured also to use the new provider or that database call was not performed (commented out) it also works. It even worked when this earlier DbContext call was the only DbContext configured to use the new provider. Summarizing: only when multiple DbContext classes are used they have to be configured (sctrictly speaking only the first one that is used, but I don't think that is recommended..) |
@robertmclaws I'm also using an existing SQL connection the same way you do (using the constructor overload) but not with a connection string builder. I'm using a connection string stored in a keyvault directly. I also don't specify the provider in the SqlConnection, decorating the DbContext class with the configuration is enough to accomplish that. In your code example I don't see the decorator or other way of configuring the provider. I thinks that should do the trick in your case. I do however have some problems with the connection not closing using a SqlConnection this way, but I'm still trying to figure out what the problem is.. @ErikEJ is it possible to override or change the existing configuration when using the decorator? I want to use the |
@jvanderwoude80 The MicrosoftSqlDbConfiguration class is just a convenience method, you can create your own configuration as you please. |
@robertmclaws Just confirmed that calling
before using any DbContexts works fine. |
Figured it out. FUN FACT: In an EDMX file, the provider type is hard-coded in the |
@robertmclaws Good one! Will add to the docs! |
@jvanderwoude80 So I have this class in my codebase now: using System.Data.Entity;
using System.Data.Entity.SqlServer;
namespace CloudNimble.BurnRate.Data
{
/// <summary>
///
/// </summary>
public class EasyAFSqlAzureConfiguration : DbConfiguration
{
private const string MicrosoftDataSqlClient = "Microsoft.Data.SqlClient";
/// <summary>
///
/// </summary>
public EasyAFSqlAzureConfiguration()
{
SetProviderFactory(MicrosoftDataSqlClient, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
SetProviderServices(MicrosoftDataSqlClient, MicrosoftSqlProviderServices.Instance);
SetExecutionStrategy(MicrosoftDataSqlClient, () => new SqlAzureExecutionStrategy());
}
}
} The first two lines are from @ErikEJ's Then I call it at the very top of my API's Startup.cs: public void ConfigureServices(IServiceCollection services)
{
DbConfiguration.SetConfiguration(new EasyAFSqlAzureConfiguration());
//...
} I'm NOT using the I have this deployed right now and it seems to be working OK. |
@robertmclaws Thanks, I already updated the readme with that info. |
@robertmclaws thanks. I stumbled on the same approach after some googling, works for me too. I keep however having problems with connection pooling not working on my azure db. Every time a new connection is opened instead of the existing one reused (and that one is not closed) till the limit of the db is reached. Still not sure what causes it. If I have further details I will post it. |
@ionmincu Thanks, I was able to see what was going on now (lack of smoke testing!). Working on a fix! |
@ionmincu Thanks for your testing, this is now fixed in RC5, which everyone watching this should be using! |
@ErikEJ thank you, seems to be working now 👍 |
@ionmincu @robertmclaws @jvanderwoude80 |
As far as I'm concerned, certainly! |
I guess so, appears to work in our e2e tests, but, just so you know this purely experimental for our project (for now). |
Thanks, RTW coming up! |
Hey @ErikEJ, seems like the EF Designer has a hard time functioning with Microsoft.Data.SqlClient as the Provider. app.config in my Data project looks like this: <?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<add name="SomeContext" connectionString="metadata=res://*/EntityModel.csdl|res://*/EntityModel.ssdl|res://*/EntityModel.msl;provider=System.Data.SqlClient;provider connection string=""" providerName="System.Data.EntityClient" />
</connectionStrings>
<entityFramework>
<providers>
<provider invariantName="Microsoft.Data.SqlClient" type="System.Data.Entity.SqlServer.MicrosoftSqlProviderServices, ErikEJ.EntityFramework.SqlServer" />
</providers>
</entityFramework>
<system.data>
<clear />
<DbProviderFactories>
<add name="SqlClient Data Provider" invariant="Microsoft.Data.SqlClient" type="System.Data.Entity.SqlServer.MicrosoftSqlProviderServices, ErikEJ.EntityFramework.SqlServer" />
</DbProviderFactories>
</system.data>
</configuration> I'm sure I'm missing something here... any ideas (besides changing it back while I'm editing EDMX files)? Thanks! |
@robertmclaws Your connection string seems to still refer to System.Data.SqlClient. If you have an EDMX, you also need to update the Provider Name in the EDMX file, as per the readme. |
Good catch on the connection string. Still getting "Error 175: The ADO.NET provider with invariant name 'Microsoft.Data.SqlClient' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details." when I try to open the adjusted EDMX file in the designer. |
Have you tried to register the provider by hand in machine.config? The VS process needs to know about it, based on the invariant name in the EDMX. |
Happy to have a look if you can share a repro .NET Framework project with an EDMX file. |
Not sure if I can provide a repro, but we'll see. Doesn't a machine.config situation mean that the provider would need to be GAC'd, like this: https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider |
Yes, which is most likely not something you want to do. Again, happy to have a play with a simple repro. |
I had a play, I think it is not possible - your system.data section is wrong, though: Final attempt would be to try machine.config, info on how to get the correct values are here: dotnet/SqlClient#836 (comment) (But then you also need to register a copy of the Microsoft.Data.SqlClient.dll in GAC) |
GACing + machine.config did not help. Put it in both the 32 and 64 bit machine.config versions, restarted the machine, no dice. When you open the EDMX file in the text editor with |
@robertmclaws Thanks, I think we can conclude that this library is a runtime solution (not a design time one) that enables you to move forward, including away from using EDMX files 😄 |
I have a .net 4.7 app that uses EF 6.4.4 and I added your latest version of this assembly. I updated the context and added the required attribute. Error calling Services.BusinessLogic.SystemCluster.Save ServiceUserName=LocalSystemMessage = Invalid value for key 'authentication'. |
Follow the read me, make sure to reference only my package from all involved projects. In order to help further I need to see your code. |
How to configure "SetDefaultConnectionFactory" with MicrosoftSqlConnectionFactory? This is what I am trying now:
|
Also, is LocalDb supported? Getting this error message: |
Looks like this message is actually coming from a third-party: How to work around that? |
Why do you need it - you can just use
Ask the author for an update, looks like they have a hardcoded check for System.Data.SqlClient I am going to lock this issue now. Happy to help you further in new issue(s). |
@eolamisan >> This is what I am trying now: And that does not work? |
@eolamisan I have created this issue: zzzprojects/EntityFramework-Extensions#467 |
Anyone watching this: version 6.5.0-rc1 has been released. Use M.D.S. 4.0.1 and removes some unused classes from the library, including all spatial and MicrosoftSqlFunctions |
UPDATE: my port of this to an official provider has been published https://www.nuget.org/packages/Microsoft.EntityFramework.SqlServer/6.5.0-preview2-24180-01 |
IF YOU WANT TO REPORT ISSUES, PLEASE CREATE A NEW ONE
Investigate app.config/web.config transformations (are they needed?)
Consider need for NuGet/Home#5986 (comment)
Related: dotnet#823 dotnet/SqlClient#725
Usage docs here: https://github.com/ErikEJ/EntityFramework6PowerTools#preview-of-ef6-sql-server-provider-based-on-microsoftdatasqlclient
The text was updated successfully, but these errors were encountered: