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

[Thorium]

To get a script working with this erasing type provider, I have to add a reference to the DesignType dll, and it seems to fail for System.Data.SqlClient is not supported on this platform.

And still my SQLServer says: System.Data.SqlClient is not supported on this platform.

Would it help to use Microsoft.Data.SqlClient instead or System.Data.SqlClient, or is the problem still the same?

In theory it would be easy to copy the current logic from Providers.MsSqlServer.fs and replace the driver to have another new DatabaseProviderType (and probably @tforkmann already did that), however in practice this driver seems to have half of the internet as dependencies. That's not very nice for SQLProvider which is used also e.g. PostGres, MySql, etc, probably their users want another half of the internet as their dependencies, not these ones (and there might be conflicts).

[cartermp]

I think Microsoft.Data.SqlClient would be preferred in theory, though there's also an issue with that right now unfortunately: https://github.com/dotnet/SqlClient/issues/292#issuecomment-624755275

I believe it's actually an issue with dependency resolution for native resources in FSI itself, which we very recently did fix for a different scenario. So it would need to be tested. In the interim, adding this will make the MS.Data.SqlClient package work with .NET Core scripting: https://github.com/dotnet/SqlClient/blob/v2.0.0-preview3/release-notes/2.0/2.0.0-preview3.md#enabling-managed-networking-on-windows

[Thorium]

I'm relying on this typeprovider heavily in production in a financial system with more than 100 000 registered users. A preview-version dependency with a feature to only "for testing and debugging purposes"... hmm... there is a minor hint of bleeding edge I'd rather avoid.

[Thorium]

Is there any easy way to manage BindingRedirects in FSI? I tried to do this but it didn't work:

#redirect "FSharp.Core" -> 4.1.0.0

[Thorium]

@cartermp : I can run SQLProvider from dotnet.exe F# scripts now, Net-standard -branch has the runtime-designtime-separation. The NuGet package is still released from the master with the old single-file structure. Both are working.

There is now one new type of provider:

• DatabaseProviderTypes.MSSQLSERVER_DYNAMIC - whatever version of Microsoft.Data.SqlClient.dll

You have to copy the dll you want to use to a folder that you point with ResolutionPath static parameter.

Now, here is a sample, I copied 3 files from my NuGet cache to a resolutionpath:

microsoft.data.sqlclient.sni.runtime\2.0.0-preview1.20141.10\runtimes\win-x64\native\Microsoft.Data.SqlClient.SNI.dll
microsoft.data.sqlclient\2.0.0-preview4.20142.4\runtimes\win\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll
system.configuration.configurationmanager\4.7.0\lib\netstandard2.0\System.Configuration.ConfigurationManager.dll

...and my F# script:

#r "nuget:SQLProvider"
open System
open FSharp.Data.Sql
[<Literal>]
let connStr = @"Data Source=localhost;Initial Catalog=HR; Integrated Security=True"
type HR = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER_DYNAMIC, connStr, ResolutionPath = @"C:\Temp\sqltest">
let ctx = HR.GetDataContext()
query { for emp in ctx.Dbo.Employees do select emp.FirstName } |> Seq.toList
;;

So this is how it runs with the new NuGet reference (dotnet fsi /langversion:preview) to SQLProvider (current version 1.1.91):

Is that ok? Anything else?

[cartermp]

That's looking really close! I think the copying of dlls to the resolution path should ultimately not be required, though. Is that the only provider where it's required?

[Thorium]

@cartermp , there is a reason for copying: How could you create a typeprovider which usage wouldn't hard-code your solution to the dependency-hierarchy of the used components?

For example: You may not want 3MB Oracle.ManagedDataAccess.dll to your project if you are creating a little portable SQLite-database for your mobile phone.

The supported reference driver list for SQLProvider is long:

• MySql Data
• MySqlConnector
• Postgres
• Odbc
• Microsoft.Data.SqlClient
• System.Data.SqlClient
• Oracle
• FireBird
• Sqilite
• ...

Now, most of these drivers have a great dependency hierarchy.

Besides that you may want to use a specific version of a component, e.g. MySqlConnector driver 0.36 and not MySqlConnector driver 0.36.1, (due to feature-supports in a driver, backward-compatibility to different database versions, dependecy already by other component, e.g. your enterprise application already use certain version of Newtonsoft.Json.dll, framework limitation, or whatever other reason).

This is done via dynamic reflection-loading the database drivers. I don't currently know any better way to do this. It'll be an initial burden when taking this component into use, but the development time this saves for other reasons (strongly typed database queries for any database) will be value of the copy&paste.

[travis-leith]

How could you create a typeprovider which usage wouldn't hard-code your solution to the dependency-hierarchy of the used components? For example: You may not want 3MB Oracle.ManagedDataAccess.dll to your project if you are creating a little portable SQLite-database for your mobile phone.

@Thorium can we not have a bunch of companion packages such as `SQLProvider.MySql' that contain all the required dependencies? The user then just installs the one that is required.

[Thorium]

That would be good idea, but I'm not sure how to implement that.

[travis-leith]

How could you create a typeprovider which usage wouldn't hard-code your solution to the dependency-hierarchy of the used components? For example: You may not want 3MB Oracle.ManagedDataAccess.dll to your project if you are creating a little portable SQLite-database for your mobile phone.

@Thorium can we not have a bunch of companion packages such as `SQLProvider.MySql' that contain all the required dependencies? The user then just installs the one that is required.

@cartermp are you aware of any other projects that implement something like the above idea, that can be used as inspiration?

[cartermp]

Not quite the same scenario, but the XPlot repo produces multiple .nupkgs as a part of the build. For this repo the core runtime would just be a .dll that each "head" package/project pulls in as a dependency in addition to their own dependencies. How to handle the build from there could get more involved:

• Restructuring the codebase to follow this pattern (core .dll, multiple other .dlls that reference it)
• Updating the build to run a pack command for each of the package-producing projects
• Publishing "dev" versions of the package to a feed, such as GitHub Packages (if appropriate)

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.

Note: this is merely a packaging/build concern and assumes that the existing "common" layer can be factored as I describe without additional work. If that is not the case, then there's more work involved than just splitting out each provider into a different project/package.

[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, notifications@github.com 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 https://github.com/fsprojects/SQLProvider/issues/645#issuecomment-793047457, 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.