[Microsoft,System].Data.SqlClient is not supported on this platform

[moh-incom]

Describe the bug
SqlDataProvider gives compile error related to the use of Microsoft.Data.SqlClient in a fresh install of Console template in F# .Net 6.0.113 on Ubuntu 22.04.
Problem is reported as Microsoft.Data.SqlClient is not supported on this platform in VS Code,
but when compiled using .Net CLI, the error is System.Data.SqlClient is not supported on this platform.

To Reproduce
Steps to reproduce the behavior:

1. Create fresh F# .Net 6.0 Console project
2. Install SqlProvider and Microsoft.Data.SqlClient packages through Nuget
3. Use the following code in Program.fs:

open FSharp.Data.Sql


[<Literal>]
let connectionString = "Server=test;DATABASE=testdb;User=user1;Password=1234;"

type sql =
    SqlDataProvider<UseOptionTypes=Common.NullableColumnType.OPTION, ConnectionString=connectionString, DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER>

4. Observe error when running dotnet run and/or look at problems reported in highlights in IDE (VS Code)

Expected behavior
I expected the program to compile.

Screenshots
Not applicable.

Desktop (please complete the following information):

• OS: Ubuntu 22.04
• IDE: VS Code 1.74.2 with Ionide 7.4.0
• Browser (not applicable?): Firefox 109.0.1

Additional context
Might be related to this problem:
#771

[WillEhrendreich]

This is also an issue on Windows 10.

For me, this is only gotten around currently by loading up Visual Studio, because, for whatever reason, it's the only method that actually works with this type provider.

HOWEVER, once you get all the information from your db within visual studio, you can create a schema file for what your queries need, and then if the type provider is using the schema file, it doesn't have a care in the world.

The behavior of this and how you interact with it is not very intuitive, I struggled with it a long time, and finally after a long time searching throughout issues I discovered some important information about how the local cache works.

It seemed to need a better explanation, so I decided to create a pull request regarding the documentation.

I don't know if you have access to visual studio, but if you do, and want to understand how SQLProvider is caching the data and how you can have an offline schema file that enables you to work on your project while in "offline mode", please refer to this doc:

docs/content/core/general.fsx lines 278-324

I would send you to the site, but for some reason it hasn't caught up with the pull request yet, I don't know if someone needs to run another script to update the site's version of the documentation, or how that works exactly.

I legitimately would be forced to use a different database interaction method if I couldn't load up Visual Studio. I hear Sqlhydra is nice?

[knocte]

As for me, I have the opposite problem: it compiles fine, but I get errors in the IDE (underlined code with red color).

it doesn't have a care in the world.

What do you mean with this? Sorry, not a native English speaker here.

[TheJayMann]

My solution to this problem is to use both packages, then choose which one to use at compile time depending on whether it is being built by Visual Studio or by dotnet build.

In my .fsproj:

  <PropertyGroup>
    <DefineConstants Condition="'$(MSBuildRuntimeType)'=='Core'">CORE_COMPILER</DefineConstants>
  </PropertyGroup>
  ...
  <Target Name="PreBuild" BeforeTargets="PreBuildEvent" Condition="'$(MSBuildRuntimeType)'=='Core'">
    <ItemGroup>
      <SqlProviderReference Include="$(Pkgruntime_win-x64_runtime_native_System_Data_SqlClient_sni)\runtimes\win-x64\native\sni.dll" />
      <SqlProviderReference Include="$(PkgSystem_Data_SqlClient)\runtimes\win\lib\netstandard2.0\System.Data.SqlClient.dll" />
    </ItemGroup>
    <Copy SourceFiles="@(SqlProviderReference)" DestinationFolder=".\obj\SqlProvider" />
  </Target>

In my .fs:

#if CORE_COMPILER
module DbConstants = 
  let [<Literal>] ResolutionPath = __SOURCE_DIRECTORY__ + """\obj\SqlProvider"""
#endif

type internal Data = 
  SqlDataProvider<
#if CORE_COMPILER
    DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC,
    ResolutionPath=,
#else
    DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER,
#endif
    ConnectionString="""...""",
    UseOptionTypes=true
  >

In order to deal with exceptions, I handle both. To make this simpler, I use an active pattern, which also allows recursively searching InnerException and AggregateException.

type MSqlException = Microsoft.Data.SqlClient.SqlException
type SSqlException = System.Data.SqlClient.SqlException
...
    let rec (|SqlEx|_|) (ex: Exception) =
      // Simple pattern to recursively search an Exception for a SqlException.
      match ex with
      | :? SSqlException as ex -> Some {| Number = ex.Number; Message = ex.Message |}
      | :? MSqlException as ex -> Some {| Number = ex.Number; Message = ex.Message |}
      | :? AggregateException as aggEx  -> aggEx.Flatten().InnerExceptions |> Seq.tryPick (|SqlEx|_|)
      | ex when isNull ex.InnerException -> None
      | ex -> (|SqlEx|_|) ex.InnerException

[Thorium]

I've never had any problems, so hard for me to understand what you'd need, but any PRs accepted to improve the situation.

I think the full Microsoft.Data.SqlClient vs System.Data.SqlClient on different platforms is a mess from Microsoft side.
SQLProvider can easily support either, the exceptions people are facing are underlying dll compatibility issues, not SQLProvider issues.

I have no idea what IDEs (VS, VS Code, Rider, console, ... ) are running on what frameworks (.NET Full, .NET Core, .NET Standard, .NET 5-...) and what are the corresponding compatible Microsoft vs System Data SqlClient dlls.

[RicoSaupeBosch]

I have a similar issue. I described it already here and will link it. Its basically also the same. I can compile in Visual Studio but not in Rider or with dotnet build. I can compile with VS and run with dotnet run, that is working. But it wouldnt help me in azure pipelines.

Please see this link for reference. I really do need a working version, work around or anything.

https://forums.fsharp.org/t/error-building-project-that-uses-fsharp-data-sql-sqltypeprovider/3141

[WillEhrendreich]

hey @RicoSaupeBosch , perhaps this will help you.

  <ItemGroup>
     <PackageReference Include="FSharp.Data" Version="6.0.1-beta002" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.0" />
    <PackageReference Include="Microsoft.Data.SqlClient.SNI.runtime" Version="5.1.0" />
    <PackageReference Include="SQLProvider" Version="1.3.7" />
  </ItemGroup>
  <Target Name="CopyFiles" BeforeTargets="BeforeCompile">
    <ItemGroup>
      <!-- Because this ItemGroup is inside the target, this will enumerate
          all files just before calling Copy. If the ItemGroup were outside
          the target , it would enumerate the files during evaluation, before
          the build starts, which may miss files created during the build. -->
      <MySourceFiles Include="$(PkgMicrosoft_Data_SqlClient_SNI_runtime)/runtimes/**/*64/**/*.dll;$(PkgMicrosoft_Data_SqlClient)/**/*qlclient.dll" />
    </ItemGroup>
    <Message Text="One of the files doesnt exist in the destination folder, Executing CopyFiles task" Condition="!Exists('$(MSBuildProjectDirectory)/SqlProvider/Microsoft.Data.SqlClient.dll') Or !Exists('$(MSBuildProjectDirectory)/SqlProvider/Microsoft.Data.SqlClient.SNI.dll')" Importance="High" />
    <Copy ContinueOnError="true" SourceFiles="@(MySourceFiles)" SkipUnchangedFiles="true" Condition="!Exists('$(MSBuildProjectDirectory)/SqlProvider/Microsoft.Data.SqlClient.dll') Or !Exists('$(MSBuildProjectDirectory)/SqlProvider/Microsoft.Data.SqlClient.SNI.dll')" DestinationFolder="$(MSBuildProjectDirectory)/SqlProvider/" />
    <Message Text="Completed CopyFiles task" Condition="!Exists('$(MSBuildProjectDirectory)/SqlProvider/Microsoft.Data.SqlClient.dll') Or !Exists('$(MSBuildProjectDirectory)/SqlProvider/Microsoft.Data.SqlClient.SNI.dll')" Importance="High" />
  </Target>

in one's connection code, we make sure to make that folder as the destination point for these dll's to be copied:

  let [<Literal>] ResolutionPath = __SOURCE_DIRECTORY__ + """\SqlProvider"""

  type sql = 
    SqlDataProvider< 
      DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC, 
          ResolutionPath=ResolutionPath,
          ConnectionString=cs
      >

After a gigantic amount of time/hair pulling/gnashing of teeth/cries for mommy, this workaround finally was discovered, and this is mostly exactly what @TheJayMann is doing in his suggestion (THANK YOU SO MUCH @TheJayMann), but modified so that it just does it regardless, instead of having to detect if some environment variable for CORE_COMPILER is correctly set, because I couldn't get that to properly work at least in my project and for Neovim/dotnet build. This solution is still not without it's quirks, some copy paste things are difficult to do at some times, specifically if the dll in question is being accessed, but I've had better luck doing this than other methods for anything other than Visual Studio.

By the way, the folder the dlls copy to doesn't have to be named SqlProvider, it could have been bananas or codswallop or whatever folder name your operating system won't yell at you for, or even none at all and just directly into the project directory, but it has to be consistently pointing to the same place as far as both the code file and the project file.

I just didn't personally want it to paste directly into my $(MSBuildProjectDirectory), which if you're unfamiliar with this is just the place holder name for whatever directory your project is in.

Of course when I say that it seems obvious, but I'd have loved for the other folks on the internet to have made less assumptions about my MsBuild system knowledge in the past, and just told me explicitly what was happening and why.

Anyway, I really hope this helps your azure pipeline stuff.

[RicoSaupeBosch]

Thanks @WillEhrendreich. I'll give that a try. For now and since its from my experience easier , I switch to SqlHydra. Generated the types with the tool and did minial chances to my logic to load data from sql. Quite a good experience so far.

I know its not a solution to the original problem but I'll test that too soon.

[Thorium]

@WillEhrendreich as you probably know already way better than me why people have these issues, is there something we can do about it? For example by improving dll-loading/picking in the source-code (via conditional compilation or whatever), or documentation?

We'd like to support on runtime (and compile-time) both System.SqlClient (old .NET full, .NET Standard 2, etc) and Microsoft.SqlClient (.NET Core, .NET Standard 2.1, etc) and also have the intellisense working (design-time) on different editors (VS and VS Code) and platforms (Win, Mac, Ubuntu). It's a lot of combination and meanwhile this seems to majorly work, people do find these kind of combinations which need quite a lot of setup-burden.

Edit: Also very annoying from Microsoft side that they do release these drivers with many frameworks in NuGet and then when you try to run it, the driver says "platform not supported". Why can't they just skip those in the Nuget target frameworks.

[TheJayMann]

It has been a while since I've had to do this, but, from what I remember, in the cases where it would fail with platform not supported, it was attempting to load the .NET Standard library, rather than the .NET or .NET Framework library, and the .NET Standard library appears to be built similar to a reference library in that all method calls throw a platform not supported exception, having the actual code reside in either the .NET library or .NET Framework library. The workarounds I initially put in place was to get around this issue.

[WillEhrendreich]

@Thorium, I'd like to officially nominate @TheJayMann as the one who seems to actually know what he's talking about, lol.

I honestly don't even know why his solution actually works, or why some things have to point to others, or why environment variables seem to be set such that it can work in Visual Studio but not in others, etc.. the only reason I could cobble together a working anything was because he gave his solution.

For any meaningful contribution, If I'm not mistaken(which I usually am) I'd have to know how the build target system for msbuild works(which I don't), what it's setting differently in visual studio vs neovim or whatever else(not a clue), what about sqlprovider communicates to msbuild or dotnet sdk to indicate which version of what dll to load under what circumstances(which.. is baffling to say the least).

Honestly getting someone from the microsoft fsharp team to look at things might have a big payoff, someone who worked on type providers, or someone on the fsautocomplete team.

Sorry to bug you guys, but we're rather lost here, and have been for a while, so here's a hail mary.. do you have any thoughts @dsyme, @Krzysztof-Cieslak, @baronfel, @TheAngryByrd, @KevinRansom, @cartermp, or @RonaldSchlenker. Thank you so much, every last one of you for what you guys do constantly for the fsharp community!

[Thorium]

So as far as I understand: SqlClient (the SQL driver for Ms SQL Server) needs to pick either .NET Core or .NET Framework library on runtime. It has .NET Standard dll, but that's kind of interfaces only, not capable of executing SQL.

But because this type-provider executes SQL also on design-time, it needs to pick the correct runtime execution not only runtime, but also design-time (and execute SQL on compilation). And there the issue is that meanwhile end-user can have a target for their .fsproj, the development environment IDE may run on different framework. And some IDEs run on .NET Framework (or Mono) and some run on .NET Core.

Am I correct? I assume this way .NET6 - ... is just another name for .NET Core, if your IDE runs on old .NET Framework, then it cannot use a net6 dll. But then how is the ".NET Core Scripting" affecting this, or not at all? Is the runtime coming from IDE, or are the IDEs always running the F# compiler on separate process? In VisualStudio that's a setting but VS Code just picks some environment variables from the computer?

[SchlenkR]

FYI - This might be helpful in general:

dotnet/fsharp#3736

[WillEhrendreich]

Thanks, @RonaldSchlenker, that does seem to be relevant, for sure.

In going down that rabbit hole this morning I found this:

fsprojects/FSharp.Data.SqlClient#374 (comment)

It looks like useful information, it looks like very similar issues are presenting themselves to this project as well.

Also, In regards to finding out what host environment goes with what needed dll's, it looks like these might be sorta related?
https://github.com/dotnet/fsharp/blob/main/src/FSharp.DependencyManager.Nuget/FSharp.DependencyManager.Utilities.fs
https://github.com/dotnet/fsharp/blob/main/src/Compiler/Facilities/CompilerLocation.fs

[WillEhrendreich]

Hey, also, @RonaldSchlenker , I've been digging through fsautocomplete and I found this:

https://github.com/fsharp/FsAutoComplete/blob/3b2017797f8d538b1c1ac22a80d34d03b73df02d/src/FsAutoComplete.Core/CompilerServiceInterface.fs#L193

@baronfel, @TheAngryByrd, @KevinRansom, do you know if this might be indicating to the compiler that the type provider needs the .netstandard target platform and then it somehow causes it to load the wrong dll's?

[baronfel]

@WillEhrendreich that flag is only used for Script files, not projects. It also only controls the API surface that the script is compiled for, not the runtime execution environment of the component hosting the compiler. This latter part is what matters for loading the design-time components of type providers, and for FSAC any TP with netstandard components should work.

[WillEhrendreich]

@baronfel OOOOOOH, ok. that makes more sense now.

so.. what DOES control the execution environment? I'm trying to find what gets set in fsac to indicate that, because the "not supported on this platform" message suggests to me that fsac is either inferring the wrong platform or somehow being fed the wrong platform from the passed in config somehow?

[SchlenkR]

I'm stll working continuousely on the sripped-down version of DebuggingTP (a simple TP using Microsoft.Data.SqlClient in a design time and runtime scenario) to get more insight and to figure out possible ways of working around. Problem is that I only have homeopathic dose of time per day. My current strategy is to have a self-contained published set of Microsoft.Data.SqlClient (+ transitive dependencies) for all kind of frameworks and platforms, shipping thos with the design time TP and pointing to the matching element in the set depending on the hosting environment. That takes quite a lot of time per test-cycle and debugging, but hopefully, the outcome will be a working solution and a test-tool that can be used for posible issues in the future. Stay tuned (or get involved)...

[Thorium]

In the same NuGet package of Microsoft.Data.SqlClient there are multiple versions of the same file (and I don't mean target framework):

".NET Standard common interface" version of the dll, in the lib folder:

\.nuget\packages\microsoft.data.sqlclient\5.1.5\lib\net6.0\Microsoft.Data.SqlClient.dll

And there is the actual runtimes versions per each platform (unix/windows/...):

\.nuget\packages\microsoft.data.sqlclient\5.1.5\runtimes\win\lib\net6.0\Microsoft.Data.SqlClient.dll

...and the issue is that even when runtime TP picks the correct version, the compile-time TP has the common interface version, which cannot execute the actual code, and that's why it doesn't compile. TP has to be able to execute code on compile time.

There is a metadata attribute [assembly: AssemblyMetadata("NotSupported", "True")] on the wrong file, but other than that they look the same.

[Thorium]

For now, there are a few workarounds, here 3:

1. Use VS2022 for build. VS2022 is build with .Net Framework and thus doesn't have these issues. You can still target your assembly to .NET 8 (or 6) just fine, it's just compile-time issue.

2. If you want to build on dotnet.exe and .NET8 (or 6): You change your provider to dynamic and add a ResolutionPath. Then copy correct Microsoft.Data.SqlClient.dll and Microsoft.Data.SqlClient.SNI.dll to that path. For example:
   Code from: SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, connStr>
   To: SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC, connStr, ResolutionPath = @"C:\temp\libs">
   (Yes that can be relative path as well, I just set something.)
   Then copy runtime files there, e.g. in Windows from NuGet cache the files from:
   C:\Users\(myUser)\.nuget\packages\microsoft.data.sqlclient\5.2.1\runtimes\win\lib\net8.0
C:\Users\(myUser)\.nuget\packages\microsoft.data.sqlclient.sni.runtime\5.2.0\runtimes\win-x64\native
   ...to your ResolutionPath folder, and behold it builds and runs.

3. If you are building a bigger project than a single script, consider using the Common.DatabaseProviderTypes.MSSQLSERVER_SSDT as it needs a tiny bit more initial setup but gives you clear benefits of being faster on compile-time and having version-controlled database schema, etc: https://fsprojects.github.io/SQLProvider//core/mssqlssdt.html

[Thorium]

ResolutionPath files are only compile-time. But if you need to compile on multiple platforms (e.g. half of your developers use Mac and other half Windows), and you have DefineConstants in your .fsproj, instead of doing some dynamic copies, just do something like

[<Literal>]
let respath =
#if WIN-NET6
   "/libs/win-runtime/"
#endif
#if WIN_NETFULL
   "/libs/net472/"
#endif
#if !WIN && !WIN_NETFULL
   "/libs/unix-runtime/"
#endif