The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: System.Data.SqlClient is not supported on this platform

[houdelou]

Describe the bug
Cannot execute a query in a .net 6 project.

To Reproduce
Steps to reproduce the behavior:

1. Create a new .NET 6 web F# web application in Visual Studio.
2. Add a script .fsx file
3. Install SQLProvider Nuget 1.3.1.
4. Add a reference #r to the netstandard2.0 dll.
5. Add a reference to localDB.
6. Do a basic query.
7. Select All and right-click "Execute In Interactive"
8. The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: System.Data.SqlClient is not supported on this platform

Expected behavior
Expect to see the query result in the output window.

Desktop (please complete the following information):

• OS: Windows 10

Additional context
Is .NET 6 supported ?

#r@"~\.nuget\packages\sqlprovider\1.3.1\lib\netstandard2.0\FSharp.Data.SqlProvider.dll"

open FSharp.Data.Sql

let [<Literal>] connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=~\source\repos\db.mdf;Integrated Security=True;Connect Timeout=30"

type sql = SqlDataProvider<
    ConnectionString = connectionString,
    DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER,
    IndividualsAmount = 1000,
    UseOptionTypes = FSharp.Data.Sql.Common.NullableColumnType.OPTION>


let ctx = sql.GetDataContext()

[OnurGumus]

I work on .NET 6 with macos without any problems.

[Thorium]

Have you referenced System.Data.SqlClient via .NET platform or via NuGet?

[houdelou]

@Thorium Via NuGet. I also tried with Microsoft.Data.SqlClient

[DieselMeister]

same here. The moment I ran this (Alt + F10) I get the same Error.

I am using VS 2022. The Script in VS shows no error and resolve the colums correctly.

The Moment I ran this script with "dotnet fsi" or with "Alt + F10" the error message appears.

I use "nuget: SQLProvider" (which is currently 1.3.3)
also I tried to add "nuget: System.Data.SqlClient, 4.8.5" (which is the used one)

It always ends up in:

error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: System.Data.SqlClient is not supported on this platform.

I didn't try MSSQL_DYNAMIC yet.

[WillEhrendreich]

So, for whatever reason, I've had more luck when using this if you turn off "use .Net Core Scripting". If you're doing it through visual studio, it's Options -> F# Tools -> Misc -> Use .Net Core Scripting,

and if you're using VSCode with Ionide, or Ionide-Vim, then you have to go in and make sure the option is set to false when the LSP server initializes, which is different for each.

It should be false to make this work.

The fix to use Microsoft.Data.SqlClient with MSSQL_DYNAMIC from a .Net Core Scripting environment has been unsuccessful for me, no matter what I tried.

The documentation for utilizing it is a very short little blurb at the bottom of the SQLServer doc. This I found a bit baffling, perhaps it's just me, but it doesn't seem to actually explain what to do with enough clarity.

What versions are the reference ones?
Why do I need reference ones instead of.. not.. the reference.. ones..?
From what nuget package do I grab them?
Is there an advantage of using paket for this instead? If so, what changes?
What versions are compatable with what version of dotnet?
Does it matter which sdk I'm using?
Does it matter which verison of fsharp.core gets resolved?
Exactly where should I put the "resolution folder"?
Why do I need a resolution folder in the first place?
What is blocking this from managing it's own dependencies?
Why do I have to even have to do this strange copy paste of dll's to begin with?

I'm super frustrated with the state of this library, because when it works it's absolute magic, and a huge advantage over many other solutions. It's absolutely a colossal time save, and the sort of thing we all want making our dev journey actually better.

When it doesn't work, or if you want it to work a different way, with an appSettings.json instead of app.config or web.config for example, it seems that currently you're simply up a creek without a paddle.

[Thorium]

The version 1.3.5 is now using Microsoft.Data.SqlClient on .Net6.0 and .NetStandard 2.1,
and the old System.Data.SqlClient on .Net 4.7.2 and .NetStandard 2.0.
This is initial trial via simple conditional compilation (because the base classes are the same) so please let me know if it works or not.

[WillEhrendreich]

it works in visual studio. but not in neovim or vscode. so, something about ionide, fsac, or maybe even fsharplanguageserver is different enough for this to not work, for whatever reason..

[JordanMarr]

The same project that worked in VS doesn’t work in the other IDEs?

[Thorium]

it works in visual studio. but not in neovim or vscode. so, something about ionide, fsac, or maybe even fsharplanguageserver is different enough for this to not work, for whatever reason..

If you check from Nuget, there:

• Microsoft.Data.SqlClient targets on NetStandard 2.1 (.Net6 etc)
• System.Data.SqlClient targets on .Net Standard 2.0 (net472, netCore3.1, etc)

So if it says X is not supported, then it would hint the editor itself is running on mode which is not compatible. Now, VS has its own F# things under its program folders. Where as the others have to resolve F# compiler (fsc.exe) via some other way like env-variable and they typically point to your dotnet-sdk folder.

We can switch whatever we want, e.g. design-time use System and runtime Microsoft, if that would work. We just have to find working combinations.

[WillEhrendreich]

how do i figure out what platform or mode the others are running? how do i change it? should i?

[WillEhrendreich]

@Thorium i still cannot get this to work outside of visual studio. interestingly it's not just design time stuff within neovim and vscode. It also does not build from a dotnet build or dotnet run command. I think there's an issue with it not using ms build?

Where would you start digging in to try to change it's behavior here? I'm willing to monkey around with it, I really need this to work, I would just like a few pointers as to what might affect this and where in the code to change things.

[Thorium]

In the original issue, what is the part "Add a reference #r to the netstandard2.0 dll."?

If your project targets to .NET6 (which is .NET Standard 2.1) then Microsoft.Data.SqlClient should be supported. But intellisense is using compiler, so it's compile-time, not runtime. So this hints to me that the compiler used is not .NET 6 / .NetStandard 2.1, but something else.

Visual Studio uses its own magic to figure out the compiler path for it, and depending on your VS it can be something like:
C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\FSharp or
C:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\CommonExtensions\Microsoft\FSharp
Maybe it picks the correct one.

And then, the other tools are using general environment variables to detect the version, and they are using something like:
C:\Program Files (x86)\Microsoft SDKs\F#\4.1\Framework\v4.0 or C:\Program Files\dotnet\sdk\6.0.404\FSharp or whatever.

I'm not familiar where editors pick their compiler paths, probably from environment settings. You could try to play with setting a property of FscToolPath to your .fsproj file to see if that helps.