Could not load file or assembly Microsoft.Data.SqlClient, Version=5.0.0.0 .NET 7 Azure AppService Linux

[kieren-harvey-shell]

At runtime, when we invoke a db context method, e.g. SaveChanges, we are always getting an issue with the Microsoft.Data.SqlClient dependency.

A .NET 7.0 app, that has a dependency on Microsoft.EntityFrameworkCore.SqlServer

• tried almost every version of that package Microsoft.EntityFrameworkCore.SqlServer 7.0.0 - 7.0.10
• Have tried adding a direct dependency on Microsoft.Data.SqlClient, we can't go lower than 5.0.1 (for all versions of 7.0.x, so can never take a dependency on Microsoft.Data.SqlClient 5.0.0.0
• event went back to Microsoft.EntityFrameworkCore.SqlServer 6.0.21 (which has a dependency on Microsoft.Data.SqlClient 2.*, that also exhibited an unable to load file or assembly error on Microsoft.Data.SqlClient...

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7

The error only occurs in the Linux based App service, which is published using:
dotnet publish --os: linux
dotnet sdk version: 7.0.203
Target OS: Azure AppService

Same dotnet app / sdk works as expected on local developer Windows 10 machine.

System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

File name: 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

I did notice in the .deps. that get generated in the bin dir (on local dev windows 10) that there is some reference to a 5.0.0.0 (but I can't see any way to influence that):

"Microsoft.Data.SqlClient/5.1.1": {
        "dependencies": {
          "Azure.Identity": "1.7.0",
          "Microsoft.Data.SqlClient.SNI.runtime": "5.1.0",
          "Microsoft.Identity.Client": "4.48.1",
          "Microsoft.IdentityModel.JsonWebTokens": "6.25.1",
          "Microsoft.IdentityModel.Protocols.OpenIdConnect": "6.25.1",
          "Microsoft.SqlServer.Server": "1.0.0",
          "System.Configuration.ConfigurationManager": "6.0.1",
          "System.Diagnostics.DiagnosticSource": "6.0.0",
          "System.Runtime.Caching": "6.0.0",
          "System.Security.Cryptography.Cng": "5.0.0",
          "System.Security.Principal.Windows": "5.0.0",
          "System.Text.Encoding.CodePages": "6.0.0",
          "System.Text.Encodings.Web": "7.0.0"
        },
        "runtime": {
          "lib/net6.0/Microsoft.Data.SqlClient.dll": {
            "assemblyVersion": "5.0.0.0",
            "fileVersion": "5.0.0.0"
          }
        },
        "runtimeTargets": {
          "runtimes/unix/lib/net6.0/Microsoft.Data.SqlClient.dll": {
            "rid": "unix",
            "assetType": "runtime",
            "assemblyVersion": "5.0.0.0",
            "fileVersion": "5.0.0.0"
          },
          "runtimes/win/lib/net6.0/Microsoft.Data.SqlClient.dll": {
            "rid": "win",
            "assetType": "runtime",
            "assemblyVersion": "5.0.0.0",
            "fileVersion": "5.0.0.0"
          }
        }

[JRahnama]

@kieren-harvey-shell thanks for bringing this up. This might be related to #2093 that we recently addressed and will be available in our next preview release. We will get back to you on this.

[BeamsplitterSoftware]

I am seeing this exact issue on Windows Server 2020 Standard. (Works correctly on Windows 11 Pro dev machine.)

[dougwalden]

I am seeing this exact issue running on a Windows Server Core 2019 container. Application is .Net8.
Any resolution?

[kf-gonzalez]

@kieren-harvey-shell can you provide repro steps to help us troubleshoot this issue please?

[basztech]

I'm having the same issue. It works fine in VS locally but errors when deployed. We have Windows Server 2019 for both local dev and deployment.

[WhereIsW4ldo]

Also having the same issue here! I'm building to a docker image and can reproduce it there every time, but locally running it (in VS or Rider) works fine...

Note: I do see that the dll is indeed copied to my container, so that should not be a problem at all!

[CMarius94]

I have the same issue. .NET Core 6 with EFCore 7, deployed on a linux docker image. Even if the microsoft.data.sqlclient.dll is present in the folder, it still can't find it, while on local VS on Win10 runs absolutely fine

[dip07]

Having the same issue. Can someone help how to go around fixing this:

Could not load file or assembly 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'

I am using .Net Core app with Microsoft.EntityFrameworkCore, Version=7.0.20. Migrated from System.Data.SqlClient (used in SqlBulkCopy) to Microsoft.Data.SqlClient by adding explicit package reference in csproj. Now, when I run the service on my local machine, it runs fine. However, when I run this in Linux Docker Container in Azure, it fails with this error:

`Error while getting config from db
System.AggregateException: One or more errors occurred. (Could not load file or assembly 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.
)
---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

  File name: 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'
     at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.<>c.<get_IsMultipleActiveResultSetsEnabled>b__7_0(String cs)
     at System.Collections.Concurrent.ConcurrentDictionary2.GetOrAdd(TKey key, Func2 valueFactory)
     at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.get_IsMultipleActiveResultSetsEnabled()
     at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerCompiledQueryCacheKeyGenerator.GenerateCacheKey(Expression query, Boolean async)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken)
     at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator()
     at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)`

I checked the folder in the Container, the Microsoft.Data.SqlClient.dll is present in the same folder. Using .Net 6 on the Docker container

[imasud00]

Could you confirm that you are copying all the DLLs that MDS depends on to the Docker image?

[dip07]

I can see them in the folder:

[comvir]

I encountered a similar problem, and finally found out that the reason was that packaging missed the runtimes folder

[dip07]

@comvir , Which runtime folder are you suggesting? How did you solve it? Can you please explain a bit. Thanks

[kf-gonzalez2]

Closing it since is not a SqlClient issue

[dip07]

https://stackoverflow.com/questions/70426907/dotnet-publish-in-docker-not-including-runtimes-folder-net-5-sdk-for-aw

Using "RUN dotnet publish "MyProject.csproj" -c Release -o /app/publish -r linux-x64" from the above post helped us resolve this issue