Npgsql 4.0 support

[MatthiasWeiser]

trafficstars

The latest Npgsql 4.0 and SQLProvider do not work together. One has to use an older version of Npgsql. Using Npgsql 4.0 and latest SQL Provider in one project together, one receives a runtime error regarding an old System.Threading version 4.2.2 which is not existing anymore. Could you point me at the right place to change this, so that I can provide a fix? Thanks!

[Thorium]

Do you have the missing threading dll (from another NuGet package) also in the reference path?

[GiorgioG]

I'm seeing the same issue, I dropped in v4.5 of the threading dll in there because 4.2.0 no longer exists - still not working.

[MatthiasWeiser]

I have started with an empty .NET 4.7 F# Console app. The dll itself is not missing - the 4.5 version of System.Threading.Tasks.Extensions is installed. It might be that the System.Threading.Tasks.Extensions needs to be added to paket file as an requirement - I am not sure where the need for the 4.2 version of System.Threading.Tasks.Extensions comes from as I did not see it used in the source code. Just adding the nuget packages SqlProvider and Npgsql references plus the Type definition and get the error.

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: Could not load file or assembly 'System.Threading.Tasks.Extensions, Version=4.2.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. Das System kann die angegebene Datei nicht finden. SqlProviderTest C:\Users\Dell\Source\Repos\SqlProviderTest\SqlProviderTest\Program.fs 9 Active

[MatthiasWeiser]

Tried dotnet core as well - still the same issue...

[Thorium]

I'll tried it says non-blocking socket operation could not be completed immediately [::1]:5432 on SqlDesignTime.fs line 62-63 con.Open() and prov.CreateTypeMappings con.

Something related to this? https://github.com/npgsql/npgsql/issues/1183

(Reproducable just by putting Npgql.dll, System.Runtime.CompilerServices.Unsafe.dll and System.Threading.Tasks.Extensions.dll to reference path and then debugging F#-interactive.)

[piaste]

I'll tried it says non-blocking socket operation could not be completed immediately [::1]:5432 on SqlDesignTime.fs line 62-63 con.Open() and prov.CreateTypeMappings con.

That error happened with 3.2.7 too, but it's innocuous (as described in the issue).

I have the following DLLs in my ReferencePath and Npgsql 4 seems to work on .NET Core 2.1 on Windows:

Npgsql.dll 4.0.0.0 System.Threading.Tasks.Extensions.dll 4.6,24705.1 (same one I had for 3.2.7) System.Data.Common.dll 4.6,24705.1 (this one was actually for MSSQL, iirc) System.Runtime.CompilerServices.Unsafe.dll from the 4.5.0 NuGet package (needed for .NET Core 2.1 compatibility)

[MatthiasWeiser]

How do you get the dlls into the ReferencePath? I created an empty F# project and added the two nuget packages. For a trivial ETL project I expect this too work. I guess you worked with Paket or something else?

[piaste]

@MatthiasWeiser

I've been doing everything SQLProvider-related manually for a while; I find that manually janitoring DLLs in a repo folder is easier than figuring out which package manager looks in which folder (especially when CI is involved).

The basic hello world (dotnet new console -lang f#, dotnet add package SqlProvider, dotnet add package Npgsql) doesn't work for me either, because the Tasks.Extensions library is in a different folder. If I add dotnet add package System.Threading.Tasks.Extensions the error goes away. Although I then run into another DLL error from System.Runtime.CompilerServices.Unsafe, which is a weird library that got screwed up with .NET Core 2.1, and which I haven't been able to handle through the package manager (afaik it's been moved to a different NuGet package and the versions are borked), only by manually copying the DLL

[GiorgioG]

@piaste @MatthiasWeiser

This (edit devenv.exe.config w/loadFromRemoteSources tag) fixed the issue for me: https://github.com/fsprojects/SQLProvider#known-issues

The error message is confusing and not helpful (admittedly I'm an F# noob.) If the error message were more like this for example, I would have had a couple of things to check/try:

The tiny bit of code is available at: https://github.com/GiorgioG/SQLProvider/commit/f730187180dcd5712cb193b0b440697820f98fc0

Keep in mind I'm a C# dev dipping my toes into F# so I'm sure what I've written is not idiomatic/well-written F#.

[danieljsummers]

After making your change (the appropriate devenv.exe.config), my error changed from 4.2.0.0 to 4.1.0.0 - but still no dice. Could you share your resPath?

[jackmott]

What is the equivalent change to make on linux when using mono?

[Thorium]

@GiorgioG are you going to make a PR for the info?

[MatthiasWeiser]

I am still struggeling with this issue - I have really no idea where the problem begins, so that I could work on a fix.

[MatthiasWeiser]

Installing Npgsql into the GAC works as a workaround.

[ameier38]

I ran into the same issue. Below is my workaround.

Given my project structure:

project
├── Handler
│   ├── Handler.fs
│   ├── Handler.fsproj
│   ├── paket.references
│   └── temp
├── Handler.sln
├── Tests
│   ├── Program.fs
│   ├── Tests.fsproj
│   └── paket.references
├── build.fsx
├── packages
│   ├──  ...
├── paket.dependencies
└── paket.lock

and inside Handler.fs:

module Handler

open FSharp.Data.Sql

let [<Literal>] ConnectionString = "Host=localhost;Database=test;Username=test;Password=test"
let [<Literal>] ResolutionPath = __SOURCE_DIRECTORY__ + @"\temp"

type DataProvider =
    SqlDataProvider<
        DatabaseVendor = Common.DatabaseProviderTypes.POSTGRESQL,
        ConnectionString = ConnectionString,
        ResolutionPath = ResolutionPath,
        IndividualsAmount = 1000,
        UseOptionTypes = true >

let ctx = DataProvider.GetDataContext()

Here is my FAKE build.fsx file to allow the SqlDataProvider to work:

note the CopyProviderDependencies target

#r "paket: groupref Fake //"
#load ".fake/build.fsx/intellisense.fsx"

open Fake.Core
open Fake.DotNet
open Fake.IO
open Fake.IO.FileSystemOperators

let paketAlias = if (Environment.isLinux || Environment.isMacOS) then "paket" else "paket.exe"
let paketExe = __SOURCE_DIRECTORY__ </> ".paket" </> paketAlias
let solution = __SOURCE_DIRECTORY__ </> "Handler.sln"
let tempDir = __SOURCE_DIRECTORY__ </> "Handler" </> "temp"
let packagesDir = __SOURCE_DIRECTORY__ </> "packages"

Target.create "Default" (fun _ ->
    Trace.trace "Handler")

Target.create "InstallPaket" (fun _ ->
    if not (File.exists paketExe) then
        DotNet.exec id "tool" "install --tool-path \".paket\" Paket --add-source https://api.nuget.org/v3/index.json"
        |> ignore
    else
        printfn "paket already installed")

Target.create "InstallDependencies" (fun _ ->
    let result =
        CreateProcess.fromRawCommand paketExe ["install"]
        |> Proc.run
    if result.ExitCode <> 0 then failwith "Failed to install dependencies")

Target.create "CopyProviderDependencies" (fun _ ->
    let npgsqlDll = 
        packagesDir 
        </> "Npgsql" 
        </> "lib" 
        </> "netstandard2.0" 
        </> "Npgsql.dll"
    let taskExtDll = 
        packagesDir 
        </> "System.Threading.Tasks.Extensions" 
        </> "lib" 
        </> "netstandard2.0" 
        </> "System.Threading.Tasks.Extensions.dll"
    let commonDll = 
        packagesDir 
        </> "System.Data.Common" 
        </> "lib" 
        </> "netstandard1.2" 
        </> "System.Data.Common.dll"
    let unsafeDll = 
        packagesDir 
        </> "System.Runtime.CompilerServices.Unsafe" 
        </> "lib" 
        </> "netstandard2.0" 
        </> "System.Runtime.CompilerServices.Unsafe.dll"
    let memoryDll =
        packagesDir
        </> "System.Memory"
        </> "lib"
        </> "netstandard2.0"
        </> "System.Memory.dll"
    [npgsqlDll; taskExtDll; commonDll; unsafeDll; memoryDll]
    |> Shell.copyFiles tempDir)

Target.create "Restore" (fun _ ->
    DotNet.restore id solution)

Target.create "Build" (fun _ ->
    DotNet.build id solution)

Target.create "Test" (fun _ ->
    DotNet.exec id "run" "-p Tests/Tests.fsproj" 
    |> ignore)

open Fake.Core.TargetOperators

"InstallPaket"
 ==> "InstallDependencies"

"InstallDependencies"
 ==> "Restore"

"InstallDependencies"
 ==> "CopyProviderDependencies"

"InstallDependencies"
 ==> "Build"

"CopyProviderDependencies"
 ==> "Build"

Target.runOrDefault "Default"

Hopefully this helps.

[klanthier]

@ameier38 can you post your paket dependencies? (basically, which version of what you are using)

[Thorium]

After this is fixed, maybe we could create some easier install packages for SQLProvider, containing immediately working the correct dependencies for each database separately.

[Swoorup]

Anyone who fixed this or has a workaround, could you post a gist solution of the repo?

[mcspud]

@Swoorup I'm in the same camp. Struggling with this

[bdkoepke]

This worked for me:

<ItemGroup>
    <PackageReference Include="Npgsql" Version="4.0.10" />
    <PackageReference Include="SQLProvider" Version="1.1.76" />
    <PackageReference Include="System.Console" Version="4.3.1" />
    <PackageReference Include="System.Data.Common" Version="4.3.0" />
    <PackageReference Include="System.Reflection" Version="4.3.0" />
    <PackageReference Include="System.Reflection.TypeExtensions" Version="4.7.0" />
    <PackageReference Include="System.Runtime" Version="4.3.1" />
    <PackageReference Include="System.Runtime.Extensions" Version="4.3.1" />
    <PackageReference Include="System.Runtime.Serialization.Formatters" Version="4.3.0" />
    <PackageReference Include="System.Threading.Tasks.Extensions" Version="4.5.3" />
    <None Include="libraries\Npgsql.dll" />
    <None Include="libraries\System.Data.Common.dll" />
    <None Include="libraries\System.Memory.dll" />
    <None Include="libraries\System.Runtime.CompilerServices.Unsafe.dll" />
    <None Include="libraries\System.Threading.Tasks.Extensions.dll" />
  </ItemGroup>

[Swoorup]

I am getting a new error.

error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: The option value was None (Parameter 'option'

[Thorium]

That is an error in your query. E.g. You select x.Value of option type, not checking it to be null first?

[Swoorup]

Sorry this was not in a query but in the type DB = SqlProvider<...> type provider statement

[jkone27]

is still issue still open? how do we do to run the provider in a F# script with net5, is it something feasible atm?

[knocte]

@Thorium hey can you elaborate on this bit you mentioned?:

maybe we could create some easier install packages for SQLProvider, containing immediately working the correct dependencies for each database separately.

[backerman]

I'm having a similar problem with Npgsql 7 and .NET 6. It's failing to load Microsoft.Extensions.Logging.Abstractions; the default resolution path appeared to include __SOURCE_DIRECTORY__ and Npgsql's directory in the nuget cache. But how is it finding the Nuget assembly?

Based on the above discussion, I created a Nuget version of the workaround in https://github.com/fsprojects/SQLProvider/issues/555#issuecomment-489355210 above, and it appears to succeed:

• Explicitly add Microsoft.Extensions.Logging.Abstractions to the fsproj with GeneratePathProperty.

    <PackageReference Include="Microsoft.Extensions.Logging.Abstractions" Version="6.0.3" GeneratePathProperty="true" />

• Copy the Microsoft.Extensions.Logging.Abstractions assembly into obj before build.

  <Target Name="CopyLoggingAbstractionsToKnownPath" BeforeTargets="BeforeBuild">
    <Copy SourceFiles="$(PkgMicrosoft_Extensions_Logging_Abstractions)\lib\net6.0\Microsoft.Extensions.Logging.Abstractions.dll" DestinationFiles="$(MSBuildThisFileDirectory)obj\Microsoft.Extensions.Logging.Abstractions.dll" />
  </Target>

• Specify an explicit resolution path of the assembly's location.

[<Literal>]
let ResPath = __SOURCE_DIRECTORY__ + "/obj"

type DB = SqlDataProvider<
            DatabaseVendor=Common.DatabaseProviderTypes.POSTGRESQL,
            ConnectionString=ConnectionString,
            UseOptionTypes=Common.NullableColumnType.OPTION,
            Owner=Owner,
            ResolutionPath=ResPath>