5

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Suraj Patil
  • 85
  • 1
  • 5
  • 2
    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. – Jeroen Mostert Sep 10 '20 at 13:39
  • Did you try add parameter without @ just new SqlParameter("CustomerId", customerId) ? – Ivan Martinyuk Sep 10 '20 at 13:44
  • @IvanMartinyuk Removing @ from the parameter name didn't work. – Suraj Patil Sep 10 '20 at 16:28

2 Answers2

7

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

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Thank you @ErikEJ 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. – Suraj Patil Sep 10 '20 at 16:44
  • If anyone needs more information about this, it can be found on https://github.com/dotnet/SqlClient/issues/725. – Suraj Patil Sep 11 '20 at 05:39
  • This issue is the actual issue for MDS support in EF6: https://github.com/dotnet/ef6/issues/823 – Dai Jul 28 '21 at 03:27
0

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

Igor
  • 349
  • 1
  • 6