Microsoft.Data.SqlClient provider issues and TODOs

[ErikEJ]

IF YOU WANT TO REPORT ISSUES, PLEASE CREATE A NEW ONE

Investigate app.config/web.config transformations (are they needed?)

Consider need for https://github.com/NuGet/Home/issues/5986#issuecomment-732401471

Related: https://github.com/dotnet/ef6/issues/823 https://github.com/dotnet/SqlClient/issues/725

Usage docs here: https://github.com/ErikEJ/EntityFramework6PowerTools#preview-of-ef6-sql-server-provider-based-on-microsoftdatasqlclient

[ErikEJ]

@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) ?

[ajcvickers]

@ErikEJ I don't think it's out of the question. On the other hand, it's not going to happen immediately, so it probably makes sense to have an external package for now. Name seems good to me. /cc @bricelam since he's good at package naming.

[bricelam]

Couldn't have picked a better name myself. 😉

Regarding the transforms, I think (based on https://github.com/dotnet/ef6/pull/953#issuecomment-506987989) that they're not actually needed. But I honestly can't remember the details.

[jvanderwoude80]

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 System.Data.Entity;
using Microsoft.Data.SqlClient;

[DbConfigurationType(typeof(System.Data.Entity.SqlServer.MicrosoftSqlDbConfiguration))]
public partial class MyDb : DbContext
{
    public MyDb(SqlConnection connection) : base(connection, true)
    {
        Database.SetInitializer<MyDb>(null);
        this.Configuration.LazyLoadingEnabled = false;
        this.Configuration.ProxyCreationEnabled = false;
    }
}

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): db.Database.ExecuteSqlCommand with the same error as before using the new package:

System.NotSupportedException : Unable to determine the provider name for provider factory of type 'Microsoft.Data.SqlClient.SqlClientFactory'. Make sure that the ADO.NET provider is installed or registered in the application config.

Is this a scenario that should work now? Or did I miss or something / configured incorrectly? Help would be appreciated. Thanks!

[ErikEJ]

@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)

DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);

[jvanderwoude80]

@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: System.InvalidOperationException : No Entity Framework provider found for the ADO.NET provider with invariant name 'Microsoft.Data.SqlClient'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information. Also tried using an app.config (not sure if possible in .NET core), but same error.

Any ideas? Do you need other code fragments or debug information that will help? Thanks again!

[ErikEJ]

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.

[jvanderwoude80]

I'm not sure, but I guess a teams session would surely help to sort it out.

[ErikEJ]

You can contact me on ejlskov at hotmail dot com...

[ErikEJ]

@jvanderwoude80 You had multiple DbContext classes in your solution, and the fix was to add the attribute to them all!

[DbConfigurationType(typeof(System.Data.Entity.SqlServer.MicrosoftSqlDbConfiguration))]

[jvanderwoude80]

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.

[ErikEJ]

I have just published a 1.0-rc1 package based on your feedback!

[jvanderwoude80]

Thanks! Confirmed it also works.

[robertmclaws]

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.

CloudNimble.BurnRate.Api2 Error: 0 : Exception: Unable to cast object of type 'Microsoft.Data.SqlClient.SqlConnection' to type 'System.Data.SqlClient.SqlConnection'. 
StackTrace:    at void System.Data.SqlClient.SqlCommand.set_DbConnection(DbConnection value)
   at void System.Data.Common.DbCommand.set_Connection(DbConnection value)
   at void System.Data.Entity.Internal.InterceptableDbCommand.set_DbConnection(DbConnection value)
   at void System.Data.Common.DbCommand.set_Connection(DbConnection value)
   at void System.Data.Entity.Core.Common.Utils.CommandHelper.SetStoreProviderCommandState(EntityCommand entityCommand, EntityTransaction entityTransaction, DbCommand storeProviderCommand)
   at DbCommand System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.PrepareEntityCommandBeforeExecution(EntityCommand entityCommand)
   at async Task<DbDataReader> System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommandsAsync(EntityCommand entityCommand, CommandBehavior behavior, CancellationToken cancellationToken)
   at T System.Data.Entity.Utilities.TaskExtensions+CultureAwaiter<T>.GetResult()
   at async Task<ObjectResult<TResultType>> System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.ExecuteAsync<TResultType>(ObjectContext context, ObjectParameterCollection parameterValues, CancellationToken cancellationToken)
   at T System.Data.Entity.Utilities.TaskExtensions+CultureAwaiter<T>.GetResult()
   at async Task<T> System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransactionAsync<T>(Func<Task<T>> func, IDbExecutionStrategy executionStrategy, bool startLocalTransaction, bool releaseConnectionOnSuccess, CancellationToken cancellationToken)
   at async Task<TResult> System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.ExecuteAsyncImplementation<TResult>(Func<Task<TResult>> func)
   at T System.Data.Entity.Utilities.TaskExtensions+CultureAwaiter<T>.GetResult()
   at async Task<ObjectResult<T>> System.Data.Entity.Core.Objects.ObjectQuery<T>.GetResultsAsync(MergeOption? forMergeOption, IDbExecutionStrategy executionStrategy, CancellationToken cancellationToken)
   at T System.Data.Entity.Utilities.TaskExtensions+CultureAwaiter<T>.GetResult()
   at async Task<bool> System.Data.Entity.Internal.LazyAsyncEnumerator<T>.FirstMoveNextAsync(CancellationToken cancellationToken)
   at async Task System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.ForEachAsync<T>(IDbAsyncEnumerator<T> enumerator, Action<T> action, CancellationToken cancellationToken)
   at T System.Data.Entity.Utilities.TaskExtensions+CultureAwaiter<T>.GetResult()
   at async Task<T[]> System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.ToArrayAsync<T>(IDbAsyncEnumerable<T> source, CancellationToken cancellationToken)
   at async Task<QueryResult> Microsoft.Restier.EntityFramework.EFQueryExecutor.ExecuteQueryAsync<TElement>(QueryContext context, IQueryable<TElement> query, CancellationToken cancellationToken)
   at async Task<QueryResult> Microsoft.Restier.AspNetCore.Query.RestierQueryExecutor.ExecuteQueryAsync<TElement>(QueryContext context, IQueryable<TElement> query, CancellationToken cancellationToken)
   at async Task<QueryResult> Microsoft.Restier.Core.Query.DefaultQueryHandler.QueryAsync(QueryContext context, CancellationToken cancellationToken)
   at async Task<QueryResult> Microsoft.Restier.Core.ApiBaseExtensions.QueryAsync(ApiBase api, QueryRequest request, CancellationToken cancellationToken)
   at async Task<IQueryable> Microsoft.Restier.AspNetCore.RestierController.ExecuteQuery(IQueryable queryable, CancellationToken cancellationToken)
   at async Task<IActionResult> Microsoft.Restier.AspNetCore.RestierController.Get(CancellationToken cancellationToken)
   at async ValueTask<IActionResult> Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
   at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()+Logged(?)
   at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()+Awaited(?)
   at void Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
   at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()+Awaited(?)
   at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeNextExceptionFilterAsync()+Awaited(?)

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

[ErikEJ]

@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.

[ErikEJ]

@robertmclaws

Except that it appears that EntityConnectionStringBuilder is hard-coded to use System.Data.SqlClient.SqlConnection.

I doubt that is true, I have used it with other providers previously.

[robertmclaws]

So, does calling DbConfiguration.SetConfiguration(new System.Data.Entity.SqlServer.MicrosoftSqlDbConfiguration()); work, or does it have to be specified as an attribute?

Also, do I need to call DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance); too?

Thanks!

[ErikEJ]

So, does calling DbConfiguration.SetConfiguration(new System.Data.Entity.SqlServer.MicrosoftSqlDbConfiguration()); work, or does it have to be specified as an attribute?

I have not tested that, why dont you try?

Also, do I need to call DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance); too?

No: https://github.com/ErikEJ/EntityFramework6PowerTools/blob/9598dd546d289f55a2993069cbe575dc779306c1/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlDbConfiguration.cs#L13

[ErikEJ]

The important part is that all DbContexts classes in your solution must be configured to use the new provider...

[jvanderwoude80]

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..)

[jvanderwoude80]

@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 SqlAzureExecutionStrategy but when I change the configuration in the constructor by using DbConfiguration.SetConfiguration I get an error an instance of another configuration is already used. Should I then use this way of configuring instead (appending your configuration steps) and drop the decorator?

[ErikEJ]

@jvanderwoude80 The MicrosoftSqlDbConfiguration class is just a convenience method, you can create your own configuration as you please.

[ErikEJ]

@robertmclaws Just confirmed that calling

 DbConfiguration.SetConfiguration(new System.Data.Entity.SqlServer.MicrosoftSqlDbConfiguration());

before using any DbContexts works fine.

[robertmclaws]

Figured it out. FUN FACT: In an EDMX file, the provider type is hard-coded in the edmx:StorageModels/Schema. Doesn't matter what you set it to in code, if you don't fix it there, you're hosed.

[ErikEJ]

@robertmclaws Good one! Will add to the docs!

[robertmclaws]

@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 MicrosoftSqlDbConfiguration class, the last one is from my existing codebase (EasyAF is the name of our app framework).

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 [DbConfigurationType] attribute, as it was simpler to keep the call I already had, add the other calls, and then fix the provider type in the EDMX.

I have this deployed right now and it seems to be working OK.

[ErikEJ]

@robertmclaws Thanks, I already updated the readme with that info. There is a constant in the provider, that you can use for the invariant name, btw.

[jvanderwoude80]

@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.

[ErikEJ]

@jvanderwoude80 @robertmclaws Ah! I think I know what your issue is - you are using the "wrong" execution strategy - SqlAzureExecutionStrategy depends on System.Data.SqlClient - use MicrosoftSqlAzureExecutionStrategy instead.

I am in the process of updating the docs!

[ErikEJ]

Added updated, dedicated readme file that will be included in the NuGet package

[jvanderwoude80]

@ErikEJ Thanks. Using the MicrosoftSqlAzureExecutionStrategy now. However does not fix my connection pooling issue. Turns out that the problem after all is aquiring the token for database access. When I use a hardcoded token string, connection pooling works as expected, first db call opens a connection, all other calls use this same connection. As soon as I acquire a token using MSAL (async call) before each db access connection pooling stops working. The first db call opens a connection, but the the second call for some reason cannot reuse this connection, so a new one is opened (and the old one not closed) and so on till the connection limit is reached.

I tried all kinds of different token acquisition methods, even making the whole call chain async, or making it synchronous using .Result or GetAwaiter().GetResult(). Still does not work. I read something about connection pooling and async calls causing problems. However this worked fine in my old .NET framework solution using ADAL for acquiring tokens. So I'm kinda stuck right now. Any pointers on where to look next? I did see some odd behavior in MSAL (sometimes token acquisition gets stuck in infinite loop), so it could well be a bug in MSAL or something (or combination with .NET core).

Only solution that works for now is turning pooling off. It's slower, but works. But of course connection pooling should work. Anyone experience with this kind of issues??

[ErikEJ]

@jvanderwoude80 >> As soon as I acquire a token using MSAL (async call) before each db access connection pooling stops working.

Is that really necessary - the token is long lived, is it not?

If you can create a repro, I suggest you create an issue on the Microsoft.Data.SqlClient repo

[ErikEJ]

@jvanderwoude80 You could also try to update to the latest Azure.Identity library (https://www.nuget.org/packages/Azure.Identity/1.4.1)

[ionmincu]

@ErikEJ I also have some problems with this (rc4)

Setting the following

// in DbConfiguration constructor, i can confirm they are beeing used (debugger gets into breakpoint here)
DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);
SetProviderFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);

Does not do anything, I see the providers are still System.Data.SqlClient

if i set this, the providers seem to change in the DbContext, but the lead to other issues

// in DbConfiguration constructor
SetProviderFactoryResolver(new MyDbProviderFactoryResolverAsdasd());

    public class MyDbProviderFactoryResolverAsdasd : IDbProviderFactoryResolver
    {
        public DbProviderFactory ResolveProviderFactory(DbConnection connection)
        {
            return Microsoft.Data.SqlClient.SqlClientFactory.Instance;
        }
    }

I get the following exception (taken with SourceLink)

System.Data.Entity.Core.ProviderIncompatibleException: 'This provider does not support the specified command tree. EntityClient should be used to create a command definition from this command tree.'

When using System.Data.SqlClient the DataSpace is set correctly to SSpace, but when using this library this happens

\src\EntityFramework\Core\Common\DbProviderServices.cs

\src\EntityFramework\Core\Objects\Internal\EntitySqlQueryState.cs

[ErikEJ]

@ionmincu do you have an edmx, and if so, did you update it?

If not, please share a repro, not just code fragments

[ionmincu]

We do not have an EDMX, we have code first. I can not share the repo. This happens when we get a simple ToListAsync()

[ErikEJ]

@ionmincu if you are not able to share runnable code (even privately) I am not able to assist.

[jvanderwoude80]

@jvanderwoude80 >> As soon as I acquire a token using MSAL (async call) before each db access connection pooling stops working.

Is that really necessary - the token is long lived, is it not?

True, could be a solution to acquire the token once.

If you can create a repro, I suggest you create an issue on the Microsoft.Data.SqlClient repo

After some more debugging turns out the same problem arises using System.Data.SqlClient. However, as soon as I use ADAL.net for acquiring the token (with the exact same code setup) everything is working fine. So I'm pretty sure MSAL.net is the culprit. So I will try to create an issue there, but providing a full repro requires an azure db with token access, not sure how to accomplish that. Anyway, the issue is indeed not longer concerning Microsoft.Data.SqlClient and thus this thread. Thanks for all the help!!

[ErikEJ]

@ionmincu Can you at least share your DbConfiguration and DbContext maybe?

[ErikEJ]

@jvanderwoude80 Thanks for confirming that your issues are not releated to the EF6 provider, getting closer to RTM!

[ionmincu]

@ionmincu if you are not able to share runnable code (even privately) I am not able to assist.

@ErikEJ I've managed to create a simple POC project that reproduces the error.

https://github.com/ionmincu/EfTesting/blob/master/EfTesting/Program.cs#L28

[ErikEJ]

Thanks, you should not be using the resolver, and I have no mention of that in my docs.

I will try to fix your repro.

[ionmincu]

It's true, it's not in the docs, but if I dont use that the provider seems to not change, see WriteDebugProvider in the POC repo.

[ErikEJ]

@ionmincu Thanks, I was able to see what was going on now (lack of smoke testing!). Working on a fix!

[ErikEJ]

@ionmincu Thanks for your testing, this is now fixed in RC5, which everyone watching this should be using!

[ionmincu]

@ErikEJ thank you, seems to be working now 👍

[ErikEJ]

@ionmincu @robertmclaws @jvanderwoude80 Can we go to version 1.0.0 RTW with the provider?

[jvanderwoude80]

@ionmincu @robertmclaws @jvanderwoude80 Can we go to version 1.0.0 RTW with the provider?

As far as I'm concerned, certainly!

[ionmincu]

I guess so, appears to work in our e2e tests, but, just so you know this purely experimental for our project (for now).

[ErikEJ]

Thanks, RTW coming up!