Code does not compile under NET6 RC1

[kkkmail]

SQLProvider based code works fine at design time but fails at compile time under NET6 RC1.

To Reproduce

1. Use VS2022 with NET6 RC1 and create F# Windows NET Core DLL project (use platform: Windows and then choose: F# Class Library template).
2. Make sure that it is targeting NET6 (<TargetFramework>net6.0</TargetFramework>).
3. Add a file, e.g. DbData.fs to the project.
4. Copy / paste the following code into that file:

   
   namespace SqlProviderTest

open FSharp.Data.Sql

module DatabaseTypes =

[<Literal>]
let ConnectionString = "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=SSPI"

type private MyDatabase = SqlDataProvider<
                Common.DatabaseProviderTypes.MSSQLSERVER,
                ConnectionString = ConnectionString,
                UseOptionTypes = true>

type private MyContext = MyDatabase.dataContext

let load() =
    let ctx = MyDatabase.GetDataContext()
    let x = ctx.Dbo.MyTable
    0

5. Make sure to adjust `MyDatabase` in `ConnectionString` above to point to some real database (or just create a database with the name `MyDatabase`) and adjust `MyTable` to point to some real table in that database (or create such a table). The structure of the table is irrelevant.
6. Observe that `SQLProvider` works fine at design time: no errors are underlined, provided that the database and table exist and if not, then it will underline what's incorrect.
7. Attempt to build the solution and observe that `SQLProvider` now fails with e.g.
`
1>C:\GitHub\CoreClm\SqlProviderTest\DbData.fs(11,31): error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: System.Data.SqlClient is not supported on this platform.
`
Line 11 is the line with `type private MyDatabase = SqlDataProvider<`.

**Expected behavior**
The code should compile "out of the box".

**Desktop:**
 - OS: Windows 10 Pro
 - VS: VS 2022, Preview 4.1 with NET6 RC1.

[Thorium]

This is not problem of SQLProvider but the underlying System.Data.SqlClient.dll You have 3 options to explore: 1) Are you loading the dll from NuGet package or from .NET Framework full? I think the Nuget-version supports that platform but the one that comes with .NET Framework doesn't. 2) You can use Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC and load Microsoft.Data.SqlClient instead of System.Data.SqlClient. https://fsprojects.github.io/SQLProvider/core/mssql.html#Using-Microsoft-Data-SqlClient-dll-instead-of-build-in-System-Data-SqlClient-dll and set the ResolutionPath static parameter. 3) Or you can also try the SSDT-version, documented in the linked page above.

[granicz]

@Thorium I am running into the same issue with net5.0 and tried your option #2 with no luck. I added a NuGet reference to Microsoft.Data.SqlClient 4.0.0-preview2.21264.2 and set ResolutionPath to the package's ref folder (which is not really a portable solution with the NuGet cache being global, but I didn't want to commit dlls into source control):

    type db =
        SqlDataProvider<
            DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC,
            ConnectionString="Server=localhost;Database=MyDb;Integrated Security=true;",
            ResolutionPath="C:\Users\adam.granicz\.nuget\packages\microsoft.data.sqlclient\4.0.0-preview2.21264.2\ref\netcoreapp3.1">

with these I am getting:

Severity    Code    Description Project File    Line    Suppression State
Error   FS3033  The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
Details: 
Cannot load a reference assembly for execution.
Could not load type 'System.Data.Common.DbCommandBuilder' from assembly 'System.Data.Common, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
Could not load type 'System.Data.Common.DbDataAdapter' from assembly 'System.Data.Common, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
Could not load type 'System.Data.Common.RowUpdatedEventArgs' from assembly 'System.Data.Common, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
Could not load type 'System.Data.Common.RowUpdatingEventArgs' from assembly 'System.Data.Common, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
Current execution platform: .NETFramework,Version=v4.7.2    MyMin01 C:\granicz\MyMin01\Site.fs  65  Active

Note again that my project targets net5.0, despite what the last line says. Clearly, I am not doing what needs to be done - can you advise?

[kkkmail]

@Thorium Thanks for your comments. Unfortunately only option 2 could be a viable option because:

1. Option 1 is not viable at all. It is NET6 and so there is no NET Framework anymore. Almost anything that is referencing its pieces simply cannot work in NET6. That includes System.Data.SqlClient. Rather, Microsoft.Data.SqlClient must be used instead.
2. Option 2 could theoretically work. Unfortunately the documentation is insufficient and no working examples are provided even for NET Core 3.1 (which is an LTS). On top of that, as reported by @granicz, it does not seem to work even under NET5.
3. Option 3 is not viable. As the code does not work at compile time, it is irrelevant if it could use SSDT at design time to figure out what's in the database. Throw in an extra complexity of dealing with [constant] generation of SSDTs and this gets immediately ruled out: If I have to generate something out of a database, then why not generate some F# (or, better, C#) database access layer code directly? Except that it would kill the purpose.

With all that in mind and given that NET Framework is now nearly two generations obsolete, I wonder why not just compile the package using Microsoft.Data.SqlClient internally, so that it could work out of the box in NET5 and NET6. After all, NET Framework is obsolete and out of support, NET5 is NOT an LTS, while NET6 is an LTS. And so NET5 will be out of support in about 6-7 months from now whereas NET6 will be supported for about 3.5 years from now.

I could pull the repo and try doing that. If this works out, then I will advise...

[kkkmail]

@Thorium , @granicz Here is an update.

1. I cloned the repo, recreated NET6 only version of SQLProvider.DesignTime.fsproj and SQLProvider.Runtime.fsproj projects, referenced Microsoft.Data.SqlClient there instead of System.Data.SqlClient, fixed some small number of compile errors (mostly changed references from System.Data.SqlClient to Microsoft.Data.SqlClient and specified parameter type as string where now string and Span could be used in NET5 and NET6). I had to let ODBC provider go as it did not compile. I then made VS emit a Nuget package on each build and then referenced that Nuget package in my project.
2. The type provider then works at design time.
3. Without manually copying content of SQLProvider bin folder into a relevant local nuget folder, I'd get error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Could not load file or assembly 'Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified. at compile time. That's sort of a step forward, as before it was complaining about System.Data.SqlClient. The point is that Microsoft.Data.SqlClient is packaged inside the referenced Nuget package (by using <CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies> inside the project).
4. And if I copy all DLLs and folders from SQLProvider bin folder into both nuget folders (e.g. C:\Users\kkkma\.nuget\packages\fsharp.data.sqlprovider.designtime\1.0.0\lib\net6.0\ and C:\Users\kkkma\.nuget\packages\fsharp.data.sqlprovider\1.0.0\lib\net6.0\), then I'd get error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Microsoft.Data.SqlClient is not supported on this platform. Which seems to be a dead end.

If you have any ideas how to make it work from this point, I'd appreciate.

[Thorium]

I'm not very familiar with .NET6. I guess it's not yet released, just a preview? As SQLProvider is used also in some big enterprises, I would love to keep the .NET Framework full as backward compatibility if possible.

One question is, is your editor using .NET Core or .NET Framework. In Visual Studio 2019 there is option in: Tools -> Options -> F# Tools -> Use .NET Core scripting. I think VS Code runs on .NET Core by default.

Compile-time and design-time should basically be the same thing, as they both use FSC. Then runtime can be different, and there you maybe cannot use "Net Standard".

[kkkmail]

Long story short...

I also cloned Microsoft.Data.SqlClient (the most recent alpha version as of this date) and attempted to change netcoreapp3.1 into net6.0 there, as it seemed that Microsoft.Data.SqlClient could not properly work under NET6. That attempt resulted in multiple incompatibility errors at compile time and fixing that is clearly not what I'd like to do in my spare time.

To sum it up.

1. We have to wait for Microsoft.Data.SqlClient to be updated to support NET6 instead of current NET Core 3.1. The alternative is fork the repo and do that "by hands".
2. Once Microsoft.Data.SqlClient supports NET6, SQLProvider can be updated to support NET6 as well.

[kkkmail]

I confirm that current version: 1.2.10 with VS2022 RC2 and targeting NET6 works. You just need to manually add a Nuget package for System.Data.SqlClient and that's all. This issue can now be closed.

[vip-ehowlett]

This exact issue appears when working within Visual Studio Code. I can get the sample project to compile with Visual Studio, but seeing as I do not have a license for it, I can't actually use it. Kind of at a loss for why it will work for VS and not VSC.

[granicz]

Could we reopen this ticket please? I just tried in a project targeting net60 with the following packages:

and I am still getting

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

My TP invocation looks like this:

    type DB = SqlDataProvider<
        DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER,
        UseOptionTypes = true,
        ConnectionString = "Server=localhost;Database=DUE2;Trusted_Connection=True;">

Any idea how to fix this?

[Thorium]

@granicz your error comes from System.Data.SqlClient, not SQLProvider.

[granicz]

Well, that may be the case, but SqlClient is only triggered because of the TP, so to a degree the TP should be attentive to how it's loaded/managed/used. I have a standard Win11 system, so what could be not supported?

[keithn]

if you install SQLProvider into a .net 6 project, it should just work..... but it doesn't. No idea why I keep coming back to F#, every single time, all the the 3rd party F# stuff is just messy, either stuff doesn't work, or there is no documentation on how to make it work, or weird work around hacks..... which this ticket seems to indicate.