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

Kath

That article is still correct for EF6. You can't combine Microsoft.Data.SqlClient with Entity Framework (unless you take care to keep classes strictly separated, I suppose). Recent versions of EF Core have switched to Microsoft.Data.SqlClient, though. – Aishaaisle

Did you try add parameter without @ just new SqlParameter("CustomerId", customerId) ? – Green

@IvanMartinyuk Removing @ from the parameter name didn't work. – Kath

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

Incorrect

Thank you @Incorrect for the clarification. Could you please also tell me if there is any workaround to make the above code work or what would be the recommended approach to switch to Microsoft.Data.SqlClient in my application. – Kath

If anyone needs more information about this, it can be found on github.com/dotnet/SqlClient/issues/725. – Kath

This issue is the actual issue for MDS support in EF6: github.com/dotnet/ef6/issues/823 – Necker

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

Kitkitchen