Could not load file or assembly System.Data.SqlClient if building using dotnet build

[daniellittledev]

Description

When building a netcoreapp3.1 or netstandard2.1 library, the build will succeed when using Visual Studio 2019 but will fail when built via dotnet build.

Error details:

error FS3033: The type provider 'FSharp.Data.SqlCommandProvider' reported an error: Could not load file or assembly 'System.Data.SqlClient, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. Reference assemblies should not be loaded for execution.  They can only be loaded in the Reflection-only loader context. (0x80131058)

Repro steps

Create a dotnet core library which uses the type provider.

<PropertyGroup>
    <OutputType>Library</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
    <WarningsAsErrors></WarningsAsErrors>
</PropertyGroup>
<ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.1" />
    <PackageReference Include="FSharp.Data.SqlClient" Version="2.0.6" />
</ItemGroup>

Expected behavior

I'd expect the non-reference version of System.Data.SqlClient to be loaded and the build to succeed.

Actual behavior

The type provider is unable to load the System.Data.SqlClient assembly as it is a reference assembly.

Known workarounds

Forcing the project to use the full framework compiler seems to work, but you need to set some properties for this to work in .netcore3.0

  <PropertyGroup>
    <DisableAutoSetFscCompilerPath>true</DisableAutoSetFscCompilerPath>
    <DotNetFscToolPath></DotNetFscToolPath>
    <DotnetFscCompilerPath></DotnetFscCompilerPath>
  </PropertyGroup>

  <Import Project="fsc.props" />

Related information

I found a number of what appear to be related but older issues. As far as i can tell .NET Standard 2.0 type providers can now be used when the F# compiler is running on .NET Core. So I believe this is an issue with loading the correct System.Data.SqlClient assembly?

• F# and Visual F# Tools announcement for June dotnet/fsharp#3298
• Project with target netcoreapp2.0 will not build with SQLProvider package version 1.1.38. SQLProvider#529
• https://medium.com/@dsincl12/f-with-net-core-2-0-4-and-sqlprovider-d8f071119da9
• designer assembly 'FSharp.Data.SqlClient.DesignTime.dll' cannot be loaded or doesn't exist #318

Environment

• Windows
• SDK 3.1.100
• F# 4.7

[aggieben]

I recently had this problem with SQLProvider (fsprojects/SQLProvider#645), and relying on MSBuild and the net FSAC backend in ionide was enough to muddle through. However, according to Phillip Carter (dotnet/fsharp#3303 (comment)), things should just work in .NET Core 3.x. That hasn't been my experience for the same reasons described here.

[viktorvan]

I am in the same situation as aggieben, I can get the typeprovider to work using the fix mentioned but it is not ideal. So it sadly does not just work “out of the box” I am afraid.

[MarneeDear]

For anyone wondering how to make this work, I put together an example project here:

https://github.com/MarneeDear/FSharp.Data.SqlClient-dotnet

This includes an fsc.props file that should work if you only have Visual Studio 2019 installed. The older version only pointed to Visual Studio 2017.

[brettrowberry]

For anyone wondering how to make this work, I put together an example project here:

https://github.com/MarneeDear/FSharp.Data.SqlClient-dotnet

This includes an fsc.props file that should work if you only have Visual Studio 2019 installed. The older version only pointed to Visual Studio 2017.

I have to try this!

[melanore]

https://github.com/demetrixbio/FSharp.Data.Npgsql is very similar to FSharp.Data.SqlClient. We recently migrated to netstandard2.0 without any net461 dependencies. It works smoothly on Windows/Unix using 3.1.201 SDK. If you are having issues, I would suggest to try new SDK.

[smoothdeveloper]

Related: #380 and dotnet/fsharp#10323

Thanks @MarneeDear for the workaround and putting up the repository.

I'm not clear yet how to address this in the type provider or if this is an upstream problem with dotnet sdk version of F# compiler.

[jackfoxy]

I cannot get this to build with dotnet build at all now with latest version, 2.0.7, but it does build in VS 16.9.2.

According to paket.lock:

FSharp.Data.SqlClient (2.0.7)
     ...
      System.Data.SqlClient (>= 4.5.1)

Resulting in this error

Could not load file or assembly 'System.Data.SqlClient, Version=4.4.0.0

Which is obviously very messed up.

It's time to fix this properly with the new library Microsoft.Data.SqlClient

#374

I worked my way backwards to v2.0.2, which has a different problem

Assembly attribute 'TypeProviderAssemblyAttribute' refers to a designer assembly 'FSharp.Data.SqlClient.DesignTime.dll' which cannot be loaded from path...

Same issue with v2.0.1

[omcnoe]

I was also experiencing this issue.

I was able to build the project using Mono, rather than the native MacOS dotnet sdk. I configured this in Rider.

Configured to build using the MSBuild.dll 15.0 from Mono succeeded:

/Library/Frameworks/Mono.framework/Versions/6.12.0/lib/mono/msbuild/15.0/bin/MSBuild.dll

But with MSBuild.dll 16.0 from the dotnet sdk the build fails:

/usr/local/share/dotnet/sdk/5.0.301/MSBuild.dll

As far as I can tell building with the Mono MSBuild.dll doesn't affect the <TargetFramework> your resulting binary actually targets.

[kevcrooks]

We'd really like this to work too, and we'd rather not have to add fsc.props or use msbuild if possible.

Is there an update on if this looks fixable for dotnet build @smoothdeveloper ?

[costa100]

Hello, where is this at? I tried to use SqlClient in rider.net with F# 6 and it gave me the same error reported in the OP.

I tried the workaround suggested by someone else (to use fsc.props) but it didn't work.

I also tried the same project in VS 2019 and it doesn't work. In the next few days I will try VS 2022, and I will be back to this thread.

Thanks

[keppelerj]

any updates on this?

[cr3wdayt5p]

I am also interested in updates on this.

I managed to get it working on macOS 12.4 (Apple Silicon) with .NET SDK 6.0.400. But I would rather not have to provide any workarounds.

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>net6.0</TargetFramework>
        <OutputType>Exe</OutputType>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="FSharp.Data.SqlClient" Version="2.1.2" />
        <PackageReference Include="System.Data.SqlClient" Version="4.8.3">
            <ExcludeAssets>compile</ExcludeAssets>
        </PackageReference>
    </ItemGroup>
    <PropertyGroup>
        <OtherFlags>$(OtherFlags) -r:/Users/mbk/.nuget/packages/system.data.sqlclient/4.8.3/runtimes/unix/lib/netstandard2.0/System.Data.SqlClient.dll</OtherFlags>
    </PropertyGroup>
    <ItemGroup>
        <Compile Include="Program.fs" />
    </ItemGroup>
</Project>

This workaround prevents the 'ref' assembly from being provided to the compiler, and then manually adds the 'runtimes/unix/lib' assembly.

[smoothdeveloper]

@jackfoxy, @omcnoe, @costa100, @keppelerj, @cr3wdayt5p I am sorry I can't really help with the issue.

Please see the issue I opened dotnet/fsharp#10323 related to discrepancy between VS shipped compiler and the one in dotnet SDK, maybe report other reproduce cases that would help the compiler team to iron out a solution to this issue.

Thanks to everyone for providing more technical insights about workarounds for the different platforms.