.NET Core/.NET Standard support (Tracking issue)

[dsyme]

I'm creating a tracking issue to help assess the status of .NET Core/.NET Standard support for SQLprovider. If people can help me define what we need to assess that would be great.

Below I've partially listed some of the scenarios. The matrix is unforuntately large because of the variety of compile-time tools that may end up hosting the type provider TPDTC, but hey.

If there is a ? it means I don't know what is the highest priority thing to assess for that scenario.

What I'm really looking for is help to know what 1 or 2 scenarios we should aim to "complete" next

Description	Compile-time Tool	Runtime target	Databases	OS	Works?	Docs?	CI?
Target .NET Framework 4.x using VS2017	devenv.exe	net461	postgres, mssql, ...	Windows	👍	👍	👍
	fsc.exe (desktop)				👍	👍	👍
	fsc.exe (dotnet)		?	Windows
Target .NET Core App 2.0 using VS2017	devenv.exe	netcoreapp2.0	?	Windows
	fsc.exe (desktop)		?
	fsc.exe (dotnet)		?
Target .NET Standard 2.0 using VS2017	devenv.exe	netstandard2.0	?
	fsc.exe (desktop)		?
	fsc.exe (dotnet)		?
Target .NET Core App 2.0 using .NET SDK	fsc.exe (dotnet)	netcoreapp2.0	?	Linux
				OSX
Target Mono using Mono	fsc.exe (desktop)	net461	postgres	Linux	👍	👍	👍
				OSX	👍	👍

[Thorium]

.NET Framework supported databases:

• Microsoft SQL Server, Postgres, MySQL / MariaDB, SQLite, FireBird, Oracle, Odbc, Access

.NET Core:

• In theory all could be supported, but I haven't seen working data drivers for Oracle, Odbc and Access.
• SQLite uses "bait and switch pcl trick" which just bites an Ubuntu/Mac developer too hard.

[charlesroddie]

I would guess (MSSQL, Postgres, MySQL) would be the most popular and worth investigating first.

"Support" I would take to mean: install a nuget package into a project, get typed access to the db in vs2017/vsmac/ vscode, and be able to run run locally or in asp.net/.net core/azure functions. "Support with workarounds" would require additional documented steps.

Edit: sql type provider for MSSQL using .net core is at the "support with workarounds" stage.

[Thorium]

Documentation running on .NET Core and .NET Standard:
http://fsprojects.github.io/SQLProvider/core/netstandard.html
https://github.com/fsprojects/SQLProvider/tree/master/tests/SqlProvider.Core.Tests

[charlesroddie]

Thanks. @j-sauer @monkieboy if the instructions in the link are not good then please leave a separate issue here.

[jlogar]

Hey, not sure where to put this... I tried out Firebird driver with .net core. Everything seems fine. Anything else needed to get those checks ticked? :)

[Thorium]

@jlogar great! Did you use Windows, Mac or Ubuntu?

[jlogar]

Oh, sorry, was sloppy with that. Was using Windows... Went on to create a repo to make it repeatable.
https://github.com/jlogar/FbNetCoreTypeProvider

[rudihorn]

If I understand correctly, it is currently still necessary to have mono installed to compile projects for .net core. At least currently the .NET Core page (https://github.com/fsprojects/SQLProvider/tree/master/tests/SqlProvider.Core.Tests) still specifies:

FSharp and Dotnet installed. On Mac and Linux: Have also Mono installed.

As it currently stands at least, just trying to compile / run it on .NET Core makes it fail with:

error FS1108: The type 'Object' is required here and is unavailable. You must add a reference to assembly 'System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e'.

Is this particular issue currently being tracked? I find it a big hurdle to get started with SqlProviders on .NET Core, and it seems to be a 'hack' that has been present for well over a year. Is there a particular reason why this must be handled like this?

[Thorium]

As far as I know, this is a limitation of all typeproviders: if you want design time intellisense (which is the main benefit using a typeprovider in the first place), you need Mono. Or has this changed recently?

[rudihorn]

This depends on how Intellisense is used, if it is done by a project like IfSharp or with the VS Code F# Language Server addon then Intellisense is performed by dotnet core as well.

You are probably right though that this might be an actual issue with compatability with .NET core though, as I cannot get it to compile at all on .NET Core even with trying to add the fsc tool info. It doesn't seem to work for Sqlite or Npgsql, and surprisingly it is not the actual type provider stage that fails but only when the types are referenced (i.e. I get intellisense for the tablename, but it has issues compiling it).

Other type providers do work on dotnet core I believe, as I had used the CSV type provider within IfSharp.

This is essentially what happens:

[rudihorn]

@Thorium @dsyme seemingly this is an issue related to the default behaviour of types. There are a few locations where ProvidedTypeDefiniton(...name..., None) is used, which causes ProvidedTypes.fs to pick the .NET framework Object type rather than typeof<obj>. Explicitly specifying ProvidedTypeDefinition(...name..., Some typeof<obj>) makes it compile and work fine on .NET Core.

I've been trying to somewhat follow the ProvidedTypes.fs file, but it seems like a lot of logic crammed into a single file making it a little difficult. There seems to be some old code which was commented out and handled some type of mapping from type names to F# types, but I think this is related to something else (txILTypeDef function). There also seems to be some low level assembly parsing code in that file.

[rudihorn]

After the latest update I'm happy to say that postgresql works targeting .NET Core on Linux. SQLite semi works, it doesn't return errors during compilation and actually finds the tables, but the tables seem to weirdly not have any columns.

It would be nice to track the support for completion in ionide F#, the F# language server and IFSharp. It seems to work with ionide F#, but I have reported an issue for the F# language server (fsprojects/fsharp-language-server#52). I'm not entirely sure about IFSharp, but I assume it has similar issues as it did with other type providers (probably this: fsprojects/IfSharp#213).

I think especially IFSharp and F# language server are important going forward, as they both help discoverability of the language.

[Thorium]

@rudihorn a few questions with your SQLite:

• Which database driver you use for that?
• Are you using 32bit?
• What are the case sensitivity settings you are using?

[rudihorn]

@Thorium I should be using System.Data.SQLite.Core version 1.0.110. I think it was ~/.nuget/packages/system.data.sqlite.core/1.0.110/build/net46/x64/SQLite.Interop.dll which I copied into the referenced libraries as well. As such it should also be the x64 variant. I am using CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL. I haven't really played around with this much though, as I only quickly tride SQLite to see if it works any different to Npgsql. Let me know if I can help test anything else though.

[drk-mtr]

I'm not sure whether this is relevant, but when using:

• System.Data.SqlClient 4.6.1
• SQLProvider 1.1.63
• Target Framework: netstandard2.0 or netcoreapp2.1
• SDK: 3.0.100-preview5-011568
• Windows 10

I need to apply this workaround: fsprojects/FSharp.TypeProviders.SDK#244 (comment)

As a general aside, support in Ionide tends to be good for me with recent MsSql and Sqlite providers and the fsacRuntime mode on netcore, it's usually the runtime side that's tricky from my (limited) experience.

[allistercsmith]

Hey all 👋

It seems like things have improved drastically on .NET Core 2 support (2.2 to be specific) for PostgreSQL (Npgsql). I created a simple project to test this out:

Test.fsproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Compile Include="Program.fs" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql" Version="4.0.7" />
    <PackageReference Include="SQLProvider" Version="1.1.65" />
  </ItemGroup>

</Project>

Program.fs:

open FSharp.Data.Sql

[<Literal>]
let ConnStr = "<DB string here>"

type SQL = SqlDataProvider<
            Common.DatabaseProviderTypes.POSTGRESQL,
            ConnStr,
            Owner="public, admin, references",
            UseOptionTypes=true>

[<EntryPoint>]
let main _ =
    let ctx = SQL.GetDataContext()
    let (firstName, lastName) =
        query {
            for e in ctx.Public.Employees do
            select (e.FirstName, e.LastName)
        } |> Seq.head
    printfn "Hello %s %s!" firstName lastName
    0

DB SQL:

CREATE TABLE employees (
    first_name text NOT NULL,
    last_name text NOT NULL
);

This seems to build and run correctly for me on Mac OS, with no additional setup required.

I'm fairly new to the F# scene, so please forgive me if I've missed something obvious. However, assuming things do truly work out-the-box these days, it would be very worthwhile to update the docs accordingly, to encourage more adoption of this package. I know from my own experience that the extra setup described in the tests and docs can be off-putting for new F# developers, and the perception around the web is that it requires a bit of setup to get working too.

If someone else can confirm that this now works out-the-box for the above scenario, I'm happy to contribute to updated documentation where possible.

Thanks for all the hard work! 🙂

[knocte]

I can confirm @allistercsmith experience, when I tried with .NET6.0 yesterday myself except for one extra detail that I had to do to make it work: specify the ResolutionPath (after UseOptionTypes=true), pointing to a directory where I had to copy manually 2 libraries from nuget (transitive dependencies of either Npgsql or SQLProvider or both): Microsoft.Bcl.HashCode.dll and Microsoft.Bcl.AsyncInterfaces.dll .