TypeProvider not working with dotnet core 3.0

[tforkmann]

Description

I'm trying to use the typeprovider with netcoreapp3.0 (3.0.100). But it is not working for me.
I alread tried to reference System.Data.SqlClient.
Ionide is failing as well.

Repro steps

1. Set up Dotnet core app 3.0

2. Add SqlProvider

3. Try to get datacontext

open FSharp.Data.Sql
open System
open Config

/// Query DME
let [<Literal>]  CompileTimeConnectionString = "myconnectionString"

type SqlDme = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, ConnectionString=CompileTimeConnectionString, UseOptionTypes=true> //, ContextSchemaPath = ContextSchemaPath>

Expected behavior

TypeProvider should just work

Actual behavior

I get following error message when building the project:
Could not load file or assembly 'System.Data.SqlClient, Version=4.6.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. Das System kann die angegebene Datei nicht finden. [C:\Users\tforkmann\Documents\1_Tests\PreveroFeed\src\Server\Server.fsproj]

Known workarounds

1. Add FSharp.Compiler.Tools
2. Add to fsproj file
   <DotnetFscCompilerPath></DotnetFscCompilerPath>

Related information

• MS Sql
• Windows 10
• netcoreapp3.0

[forki]

Id there a general strategy for type providers in dotnet sdk 3.x? Since we don't get any new fsharp.compiler.tools packages anymore - what's the replacement?

/cc @dsyme @cartermp

[Bjorn-Strom]

Having this same problem on mac.

[cartermp]

@forki There is no replacement, as that was never a supported scenario in the first place. I'd encourage never installing that package.

[forki]

Reality is that this was the only way to get things working. But it's not really important anymore. The question is how can we proceed?

[cartermp]

Realistically there's probably a bug in either the type provider, the provider sdk, or F# compiler that needs to be resolved. Using an unsupported package containing a very old .NET Framework-only version of the F# compiler isn't a scenario you should be considering here.

[Thorium]

I'm happy to approve PRs (and release new packages). But I know nothing about .NET Core 3.0, I still use .NET Framework. For example I have no clue do you still need to set DotnetFscCompilerPath or not, to resolve the duplicate fsc problem.

This type provider loads DB drivers via reflection (to not depend every DB), which is a challenge in Core as the dependencies are broken down to many dlls that are loaded from unknown cache location.

Here is an example typeprovider from the TypeProvider SDK, referring NETStandard.Library.NETFramework, I guess it's outdated as well. However if you don't do that, loading different versions of .NET strandard.dll will cause a loop in reflection.

[tforkmann]

Today I worked a bit on the problem.

I created a fork of the sqlprovider and only focused on the netcore version.
I got the compile compiling on NetCore 3.0. The only thing missing is that the SqlClient needs to be present before compile time.
With the Compiler Tools I didn’t need to add anything.
I don’t understand what the compiler tools is doing differently to make the Client available before compile time

[mcspud]

For example I have no clue do you still need to set DotnetFscCompilerPath or not, to resolve the duplicate fsc problem.

Nope!

@tforkkmann
FWIW I can get this to run seamlessly on Ubuntu 16.04 and OSX using .net core 3.0.100, npgsql and postgres 11 and VS Code as long as I set the resolution paths up from the "unknown cache directory".

I am having lots of issues with SaveContextSchema() though, but I've referenced that elsewhere.

[tforkmann]

@mcspud Which directory are you adding as a resolution folder? The Package folder?

[mcspud]

Hey @tforkmann - yep. I'm using the osx package location,

let [<Literal>] ConnectionString =
    "Server=localhost;" +
    "Database=jabronis;" +
    "User Id=localdev;" +
    "Password=password;"

let [<Literal>] DatabaseVendor = Common.DatabaseProviderTypes.POSTGRESQL
let [<Literal>] CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL
let [<Literal>] ContextSchemaPath = __SOURCE_DIRECTORY__ + "/.postgres.schema.json"
let [<Literal>] ResolutionPath = @"~/.nuget/packages/Npgsql/4.1.1/lib/netcore3.0"

type Sql =
    SqlDataProvider<
        ConnectionString=ConnectionString,
        DatabaseVendor=DatabaseVendor,
        CaseSensitivityChange=CaseSensitivityChange,
        ResolutionPath=ResolutionPath>

And from my .fsproj:

...
  <TargetFramework>netcoreapp3.0</TargetFramework>
<ItemGroup>
    ...
    <PackageReference Include="Npgsql" Version="4.1.1" />
    <PackageReference Include="SQLProvider" Version="1.1.68" />
  </ItemGroup>

That .nuget path is the default location for .net core packages on mac.

This all works nicely, but on VSCode intellisense can be pretty flakey often times requiring me to restart it to get it kick back in. It doesn't work at all on Rider for the generative type provider. I also can't successfully export the schema so I can't actually build the project in a docker image on a CI/CD environment lol :)

[jl0pd]

Hello everyone. I'm new to F# and learning it.

I've solved problem by adding System.Data.SqlClient to project:

<PackageReference Include="System.Data.SqlClient" Version="4.8.0" />

but got this:

The most strange thing - I can create an instance of IAsyncDisposable:

let a = { new IAsyncDisposable with
    member __.DisposeAsync() = ValueTask()}

How to make it work?

Configuration

• Dotnet core SDK v3.0.101
• Linux mint 19.1
• Ionide-fsharp 4.3.2

[Thorium]

Hi,

I just updated the package to contain System.Data.SqlClient of package 4.8.0 Hopefully helps.

@jl0pd just a guess, sounds like your compile-time version of System.Runtime.dll doesn't match the execution time version of System.Runtime.dll

[tforkmann]

Hi,

i tried with the new SqlProvider version.

Somehow it still asks for the System.Data.SqlClient version 4.6.1

Can you maybe update the 4.6.1 version as well?

[tforkmann]

I also saw that the System.Data.SqlClient doesn't has a net core 3.1 version.

We probably need to migrate to Microsoft.Data.SqlClient
https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

[Thorium]

System.Data.SqlClient are one of the few which are currently not loaded via reflection...

[Thorium]

I think it's version 4.6.1? Or 4.8? Is there a problem? Do you do binding redirects?

[tforkmann]

I have 4.8 in my paket.lock.
Is binding redirects required?

[Thorium]

I think I updated it to 4.8, but I may have missed something.

SQLProvider/paket.lock

Line 152 in 4d79deb

System.Data.SqlClient (4.8)

[tforkmann]

Hmm, ok.
Updated to 1.1.82. Still the same errror.
Why are you using the FSharp.Compiler.Tools now?
Is it still required?

[tforkmann]

Ok, it's compiling and running but ionide shows that error.
Maybe it is just an Ionide issue?!

[Thorium]

I was planing to use FSharp.Compiler.Tools because people have here lot of compilation issues and some of them come by using wrong fsc.exe, so I thought maybe the compiler tools would provide a consistent version. However it's not used right now.

[cartermp]

I would highly recommend not using FCT. Chiefest of issues being that there will forever be a delta between what you compile with and what your IDE things you're doing.

[njeisecke]

I'm trying this on macOS. After getting rid of Mono and Visual Studio Mac, with only pure .net code 3.1 SDK installed it started to work.

• I've setup the test project roughly following https://docs.microsoft.com/en-us/dotnet/fsharp/get-started/get-started-command-line.
• added the packages "SQLProvider" and "MySqlConnector" using "dotnet add package"
• seems to work without an explicit ResolutionPath (for MySql)

Visual Studio Code:

• Intellisense with ionide in VSC seems to work ok (db schema changes require a restart)
• Ionide: set "Fsac Runtime" to netcode
• Ionide: set "Use SDK Scripts" to true (so fsi works with .net core)

So it looks like the F# compiler (4.5, outdated anyway) from Mono does not work and mixing mono build environment with .net code runtime environment is a bad idea. All the obviously outdated documentation ("how to get startet with f#") from Microsoft is really irritating.

Hope this helps.

[Thorium]

@njeisecke, I'm confused... I thought type providers still need Mono to compile. Has this changed with .NET Core 3.1?

[njeisecke]

Seems to work. I removed all of the mono stuff and use the "dotnet" command for everything. It builds and runs.

[cartermp]

Type Providers have not needed mono to compile for quite a long time. There's no reason to require .NET Framework of any flavor unless you specifically require an API that has no alternative on .NET Standard.

[Thorium]

Type Providers have not needed mono to compile for quite a long time.

Wow, cool. :-D All these undocumented features that I'm not aware of.

[JordanMarr]

Since each would be a separate type provider, that would be 19 total projects (1 common, 9 TP design-time, 9 TP runtime) projects excluding tests. This would be a rather significant change to the layout of the codebase and the build.

I love the idea of having a core project and a separate project/nupkg for each provider type. This would allow each provider type to freely include whatever packages it needs, and it would get rid of dynamic loading for devs and users.

But can’t there just be one core project (with design-time and runtime) plus one project per provider type since the provider type projects are just implementing an interface that returns a custom schema model? 19 projects sounds too daunting.

[mebrein]

I finally got this type provider up-and-running on w10 with sqlserver on azure, targeting net5.0. I'll post my configuration here hoping it might save other devs some time. It does include compiler tools, I also read it is disadvised. Maybe someone can verify this, if needed I suggest add this to the documentation (or I'll make a pull request). So new devs on this combination can have a better onboarding experience while things are being sorted out.

<Project Sdk="Microsoft.NET.Sdk">
    
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net5.0</TargetFramework>
        <DotnetFscCompilerPath></DotnetFscCompilerPath>
    </PropertyGroup>

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

    <ItemGroup>
        <PackageReference Include="FSharp.Compiler.Tools" Version="10.2.3" />
        <PackageReference Include="FSharp.Data" Version="3.3.3" />
        <PackageReference Include="SQLProvider" Version="1.1.98" />
        <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
    </ItemGroup>
    <ItemGroup>
        <Reference Include="FSharp.Data.SqlProvider">
            <HintPath>compiletime/FSharp.Data.SqlProvider.dll</HintPath>
        </Reference>
    </ItemGroup>
    <Target Name="PreBuild" BeforeTargets="PreBuildEvent">
        <Exec Command="xcopy %USERPROFILE%\.nuget\packages\SQLProvider\1.1.98\lib\net451\FSharp.Data.SqlProvider.dll compiletime\ /y" />
    </Target>
    <Target Name="PostBuild" AfterTargets="PostBuildEvent">
        <Exec Command="xcopy %USERPROFILE%\.nuget\packages\SQLProvider\1.1.98\lib\netstandard2.0\FSharp.Data.SqlProvider.dll bin\Debug\net5.0\ /y" />
    </Target>
</Project>

open FSharp.Data.Sql

type sql =
    SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, "Server=***;Database=;Password=***;MultipleActiveResultSets=True;App=****">

let ctx = sql.GetDataContext()

let s =
    query {
        for r in ctx.Dbo.Main do
            sortBy (r.Name)
            select (r)
    }
    |> Seq.toArray
    |> Seq.head
    
printfn "%s" s.Name     

[jkone27]

Got still problems running the same net5 solution working on win , and not compiling on docker/Linux unfortunately 😢 .
I tried referencing 4.6.1.0 explicitly in the web solution but doesn't seem to solve the issue.
Wasn't it upgraded to 4.8 also (reading from this thread), tryied also pre-release packages but didnt find any new package.
using <PackageReference Include="SQLProvider" Version="1.1.98" />

error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: 
Could not load type 'System.Data.SqlClient.SqlCommand' 
from assembly 'System.Data.SqlClient, Version=4.6.1.0, 
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.

can't figure out a way for assembly redirect in net5 (guess there isn't really)

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

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Compile Include="ProvidedTypes.fs" />
    <Compile Include="Library.fs" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="FSharp.Data" Version="3.3.3" />
    <PackageReference Include="Microsoft.AspNetCore.Http" Version="2.2.2" />
    <PackageReference Include="Newtonsoft.Json" Version="12.0.3" />
    <PackageReference Include="SQLProvider" Version="1.1.98" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
  </ItemGroup>

</Project>

could somehow assembly resolution from within type provider ignore version numbers, but just try a name match only? is it possible to use something like this within the type provider during type generation?

https://stackoverflow.com/questions/1460271/how-to-use-assembly-binding-redirection-to-ignore-revision-and-build-numbers/2344624#2344624

just wonder, is this line meant to copy from 461 all the time?
https://github.com/fsprojects/SQLProvider/blob/f5ce0435c346a46ac3e9e5d5f034f4ca0dd0eb1b/build.fsx

CopyFile "bin/netstandard2.0" "packages/System.Data.SqlClient/lib/net461/System.Data.SqlClient.dll" 

Maybe this is not behaving correctly for net5 and netcore?
https://fake.build/apidocs/v4/fake-filehelper.html#Functions%20and%20values

CopyFile Dest Target

[Thorium]

net-standard branch is now merged and release as version 1.2.0 in the Nuget.
That should fix all these issues.

[tforkmann]

Whott Whootttt

[tforkmann]

If I run the same thing on a NET.5 console app.
I get the following error:

I tried to copy X68 Microsoft.Data.Sqlite file but that didn't help.

I guess the .NET Interactive is doing something better then my standard console app.

Any idea what could be the reason for the Invoke exception?

[Thorium]

Sqlite? Which database? Are you using MS-SQL or SQLite?

…

-- - Tuomas

On Mon, 8 Mar 2021, 20:21 Tim Forkmann, ***@***.***> wrote: If I run the same thing on a NET.5 console app. I get the following error: [image: image] <https://user-images.githubusercontent.com/4168951/110375814-bbb7f180-8052-11eb-83d3-4de7263173b4.png> I tried to copy X68 Microsoft.Data.Sqlite file but that didn't help. I guess the .NET Interactive is doing something better then my standard console app. Any idea what could be the reason for the Invoke exception? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#645 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABX7246SASA2RIHSXP47ADTCUWSXANCNFSM4JBMDGGA> .

[tforkmann]

MS-SQL

[Thorium]

Then copy Microsoft.Data.SqlClient.dll and not Sqlite.dll

Edit: see bottom of page
https://fsprojects.github.io/SQLProvider/core/mssql.html#Using-Microsoft-Data-SqlClient-dll-instead-of-build-in-System-Data-SqlClient-dll

[tforkmann]

Sorry that was a typo in my previous comment. I copied the Microsoft.Data.SqlClient.dll. Any other idea?

[jkone27]

i receive this error when trying in F# script .fsx from vs2019 preview (with similar code of the notebook, also MSSQL)

but only when using named type provider arguments

type EnosisDb = SqlDataProvider<DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER,
    ConnectionString = myConnectionString,
    IndividualsAmount = 1000,
    ContextSchemaPath = schemaPath
    >

Severity	Code	Description	Project	File	Line	Suppression State
Error	FS3033	The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Expecting element 'root' from 
namespace ''.. Encountered 'None'  with name '', namespace ''.	F# Miscellaneous Files
	C:\Users\XXX\Desktop\FsharpScripts\sqlProvider\testSqlProvider.fsx	18	Active

[Bjorn-Strom]

Having this same problem on mac.
Copied the Microsoft.Data.SqlClient.dll file and getting the same invocation error.

[rytido]

I'm also getting an invocation error on Windows with MSSQLSERVER_DYNAMIC using Microsoft.Data.SqlClient.dll

[Thorium]

I couldn't... but I'm not expert of .net 5.0 configuration. Would it be possible for you to create a sample repo?

[rytido]

@Thorium https://github.com/rytido/sqlprovider-sample.git

[Thorium]

From the Microsoft.Data.SqlClient-package:
You have to use the Microsoft.Data.SqlClient.dll that is in the runtimes folder (over 1.4Mb),
not the lib version of around 350kb.

You'll also need Microsoft.Data.SqlClient.SNI.dll and System.Configuration.ConfigurationManager.dll.

[tforkmann]

You also have to add the Microsoft.Identity.Client.dll.

And somehow Ionide does not like it yet.
So there is no intellisense with VS Code

[rytido]

It's working for me in VS Code after also adding Microsoft.Identity.Client.dll. Thank you!

[tforkmann]

Which configuration do you have on ionide? netcore runtime?

[rytido]

ionide 5.4.0 and dotnet sdk 5.0.200 (and 3.1.406 but not sure that matters or is needed)

[costa100]

From the Microsoft.Data.SqlClient-package: You have to use the Microsoft.Data.SqlClient.dll that is in the runtimes folder (over 1.4Mb), not the lib version of around 350kb.

You'll also need Microsoft.Data.SqlClient.SNI.dll and System.Configuration.ConfigurationManager.dll.

Hi - I have trouble getting SqlProvider to work with the Microsoft.Data.SqlClient in VS 2019 on windows 2016. I get this:

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

I followed the instructions in this section: https://fsprojects.github.io/SQLProvider/core/mssql.html#Using-Microsoft-Data-SqlClient-dll-instead-of-build-in-System-Data-SqlClient-dll, however, what's not clear to me is this:

copy the reference files from the NuGet package to local resolutionPath

What is exactly the default resolutionPath in this case? I copied the following dlls to C:\Users\costa\.nuget\packages\sqlprovider\1.2.10\typeproviders\fsharp41\netcoreapp3.1, but it still doesn't work:
Microsoft.Data.SqlClient.dll
Microsoft.Data.SqlClient.SNI.dll
Microsoft.Identity.Client.dll

System.Configuration.ConfigurationManager.dll was already there.

I am using .Net 6.0, MSSQLSERVER_DYNAMIC to connect to a sql server 2017 database.

I also copied the dlls to C:\Users\costa\.nuget\packages\sqlprovider\1.2.10\lib\netstandard2.0, and it still doesn't work.

Thanks

[WillEhrendreich]

This is still profoundly unclear, even a couple of years later, @costa100 , the directions on the site have not changed to have any better example than what they had back when you opened this issue. I cannot get this working in dotnet 6, whether I'm using vs2022 or outside of VS using dotnet build from command line.

Have you had any luck in the last couple of years?

[boggye]

Well, I started to use facil. It had a bit of a learning curve, at least for me, but it works, and I have to say the support offered by the author has been outstanding. I highly recommend it. The other options that I looked at were: 1. dapper and 2. simply execute ado.net - that works as well.

[lucasteles]

Well, I started to use facil. It had a bit of a learning curve, at least for me, but it works, and I have to say the support offered by the author has been outstanding. I highly recommend it. The other options that I looked at were: 1. dapper and 2. simply execute ado.net - that works as well.

I am having a good experience using EF Core, as just a query, basic mapping e migration tool with F#

[boggye]

I am having a good experience using EF Core, as just a query, basic mapping e migration tool with F#

Just curious, how did you use it?

The problem that I have using a type provider is the "magic" that it does behind the scenes. How does it refresh when you change the database? Sometimes it freezes the IDE for prolonged periods of time. So far, I steered away from the type providers that interface with the relational databases .

With facil you know exactly what's going on, all the code is there, in your face, you can regenerate it whenever you want.