Does the nuget package Microsoft.Data.SqlClient work with Entity Framework?

Question

My application runs on .NET framework 4.7 and I'm using Entity Framework 6.1.3. Currently, my code uses some classes from the namespace System.Data.SqlClient such as SqlParameter. I want to switch to Microsoft.Data.SqlClient.

However, I'm not sure if EF6 is compatible with Microsoft.Data.SqlClient. This is an old article from Microsoft, it says that EF Core, EF 6 etc. haven’t yet made the transition to the new provider Microsoft.Data.SqlClient. So, I'm a bit confused.

Everything has been working well with System.Data.SqlClient for the below code

public async Task<ICollection<int>> GetChildCustomerIdsAsync(int customerId)
{
   var sqlParameters = new List<SqlParameter>()
   {
      new SqlParameter("@CustomerId", customerId)
   };

   return await DbContext.Database.SqlQuery<int>("dbo.sp_GetChildCustomerIds @CustomerId=@CustomerId",
                sqlParameters.ToArray()).ToListAsync().ConfigureAwait(false);
}

However, when I am switching to Microsoft.Data.SqlClient, I'm getting this error:

System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects.
at System.Data.SqlClient.SqlParameterCollection.ValidateType(Object value)
at System.Data.SqlClient.SqlParameterCollection.AddRange(Array values)
at System.Data.Entity.Core.Objects.ObjectContext.CreateStoreCommand(String commandText, Object[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternalAsync.d__6f`1.MoveNext()

Answer 1

No, EF 6 does not work with Microsoft.Data.SqlClient, but I have published a package that does.

NuGet package: ErikEJ.EntityFramework.SqlServer

Documentation: here and here

Answer 2

Microsoft is planning it

https://github.com/dotnet/ef6/issues/823#issuecomment-948340657

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

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/plan#theme-clear-path-forward-from-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.

---

In the meantime, you can try adding a provider created by ErikEJ. This should help.

NuGet package: ErikEJ.EntityFramework.SqlServer

Documentation: here and here

---

Microsoft has accepted ErikEJ's pull request.

NuGet package: Microsoft.EntityFramework.SqlServer (release is pending)

Documentation: here