designer assembly 'FSharp.Data.SqlClient.DesignTime.dll' cannot be loaded or doesn't exist

[richardjharding]

Description

Attempting to compile with latest 2.0.1 release

Repro steps

Following code does not compile

open FSharp.Data

[<Literal>]
let connectionString = @"redacted"

type userProfileDb = SqlProgrammabilityProvider<connectionString>

Expected behavior

Type provider gives access to stored procs

Actual behavior

Compiler error

SC : error FS3031: The type provider 'C:\Users\richa\.nuget\packages\fsharp.data.sqlclient\2.0.1\lib\netstandard2.0\FSharp.Data.SqlClient.dll' reported an error: Assembly attribute 'TypeProviderAssemblyAttribute' refers to a designer assembly 'FSharp.Data.SqlClient.DesignTime.dll' which cannot be loaded or doesn't exist. Could not load file or assembly 'file:///C:\Users\richa\.nuget\packages\fsharp.data.sqlclient\2.0.1\lib\netstandard2.0\FSharp.Data.SqlClient.DesignTime.dll' or one of its dependencies. The system cannot find the file specified. [C:\repos\AuthenticationServer\src\Sse.Retail.AuthServer.Web\Sse.Retail.AuthServer.Web.fsproj]

The file mentioned is not at that location - but wasn't sure if that was expected?

Have tried with and without the workaround with fsc.props file - again unsure from recent issues whether that workaround is required or not

Related information

• Operating system Windows 10
• Branch
• Database versions and sample databases being used
• .NET Runtime, CoreCLR or Mono Version Core 2.1.403
• Editing Tools (e.g. Visual Studio Version) VsCode with Ionide

[vasily-kirichenko]

I tested it on a .net core project in Rider, everything worked. I don’t know if Ionide supports type providers on .net core. Could you try VS or Rider?

[richardjharding]

So I think Ionide is not relevant here? - should just run at cmd line with dotnet build I think?

[vasily-kirichenko]

@richardjharding BTH, I'm not using Core at all.

@samhanes any ideas?

[vjraitila]

This is reproducible on a Mac using .NET Core SDK 2.1.500 + VS Code + Ionide. I do not have mono installed as I'm trying to move to pure .NET Core tooling.

You do not even have to try to use a stored procedure in order to get the error. Examining the nuget package cache, it can be confirmed that there is no design-time assembly generated in the netstandard2.0 folder.

# find . -name 'FSharp.Data.SqlClient.DesignTime.dll'
./packages/fsharp.data.sqlclient/2.0.1/lib/typeproviders/fsharp41/net40/FSharp.Data.SqlClient.DesignTime.dll
./packages/fsharp.data.sqlclient/2.0.1/lib/typeproviders/fsharp41/net461/FSharp.Data.SqlClient.DesignTime.dll
./packages/fsharp.data.sqlclient/2.0.1/lib/net40/FSharp.Data.SqlClient.DesignTime.dll

[vasily-kirichenko]

afaik type providers must be hosted on .net 4.5+ framework at design time (at least this is what was required a half of year ago).

@dsyme could you please confirm this?

[vjraitila]

I think this is no longer the case. Works just fine in FSharp.Data providers for JSON, for example.

DLL layout there:

# find . -name 'FSharp.Data.DesignTime.dll'
./packages/fsharp.data/3.0.0/lib/netstandard2.0/FSharp.Data.DesignTime.dll
./packages/fsharp.data/3.0.0/lib/net45/FSharp.Data.DesignTime.dll
./packages/fsharp.data/3.0.0/typeproviders/fsharp41/netstandard2.0/FSharp.Data.DesignTime.dll
./packages/fsharp.data/3.0.0/typeproviders/fsharp41/net45/FSharp.Data.DesignTime.dll

[samhanes]

@richardjharding What version of Visual Studio do you have installed? Are you following all of the guidance here? dotnet/fsharp#3303

@vjraitila Mono is required to run the design time component on MacOS - it has dependencies on full framework dlls. You should be fine at runtime with just netcore, however.

@vasily-kirichenko I'm planning to submit a PR to add some of these things to the documentation - there are some gotchas that are not at all obvious.

[vjraitila]

@samhanes So, the design-time component for FSharp.Data.SqlClient specifically has full framework dependencies - right? Just to clarify that this is obviously not a requirement for type providers in general.

[samhanes]

@vjraitila Yes that's right - this is a SqlClient-specific requirement. The design-time component depends on a couple of libraries (Microsoft.SqlServer.TransactSql.ScriptDom and Microsoft.SqlServer.Types) which require full framework.

[richardjharding]

not using Visual Studio - vs code with Ionide - have followed the guidance at dotnet/fsharp#3303 ie by adding the fsc.props project import but still get that error

[samhanes]

@richardjharding But what version do you have installed? fsc.props is setting the path to the F# compiler, trying to find out which version it is using.

[vasily-kirichenko]

The design-time component depends on a couple of libraries (Microsoft.SqlServer.TransactSql.ScriptDom and Microsoft.SqlServer.Types) which require full framework.

I think these assemblies can be loaded by Core.

[samhanes]

@richardjharding The issue is that SqlClient uses a netfx-only DTC with a netstandard RTC - in order for that to work with a netcore project, I believe that you need to have VS 15.3 or above installed. Otherwise, the F# tooling is going to look for the DTC only in the same folder as the RTC which isn't going to work. Do I have that right @dsyme ?

@vasily-kirichenko To be honest, I did not try loading the Microsoft.SqlServer dependencies in a netcore version of the DTC. If that worked, it would a) eliminate the Mono requirement on MacOS/Linux and b) eliminate the need for dotnet/fsharp#3303 workarounds in projects referencing the TP.

[samhanes]

@vasily-kirichenko A few weeks ago I did send an email to the maintainer of those Microsoft.SqlServer nuget packages, asking about the possibility of a netstandard version. Have not heard anything.

Although as you say, it might "just work" already.

[vasily-kirichenko]

@samhanes I sent an email to him too, a half of year ago :) no response

[r1sc]

FYI this error pops up even without any SqlClient specific code - just adding a reference to the package and dotnet build produces the error.

[vjraitila]

Yup. It is not just a design-time problem in that sense. The project does not compile at all with pure .NET Core tooling.

Running

dotnet new console -lang f#
dotnet add package FSharp.Data.SqlClient
dotnet build

already spews out that error.

[vasily-kirichenko]

Performing ^^^ commands, I have build error having VS, Rider and full framework on windows:

FSC : error FS3031: The type provider 'C:\Users\kirichenko.nuget\packages\fsharp.data.sqlclient\2.0.1\lib\netstandard2.0\FSharp.Data.SqlClient.dll' reported an error: Assembly attribute 'TypeProviderAssemblyAttribute' refers to a designer assembly 'FSharp.Data.SqlClient.DesignTime.dll' which cannot be loaded or doesn't exist. Could not load file or assembly 'C:\Users\kirichenko.nuget\packages\fsharp.data.sqlclient\2.0.1\lib\netstandard2.0\FSharp.Data.SqlClient.DesignTime.dll'. The system cannot find the file specified. [C:\Users\kirichenko\kirichenko.fsproj]
FSC : warning FS3005: Referenced assembly 'C:\Users\kirichenko.nuget\packages\fsharp.data.sqlclient\2.0.1\lib\netstandard2.0\FSharp.Data.SqlClient.dll' has assembly level attribute 'Microsoft.FSharp.Core.CompilerServices.TypeProviderAssemblyAttribute' but no public type provider classes were found [C:\Users\kirichenko\kirichenko.fsproj]

[samhanes]

Does adding fsc.props fix this problem?

[r1sc]

Yes that works for some reason. Is there a different version bundled with .net core or what is going on here?

[samhanes]

@r1sc @vjraitila I really need to document this better, but the underlying issue is that FSharp.Data.SqlClient is not fully netstandard compliant. The TP is split into a design-time component (DTC) and a runtime component (RTC):

• The RTC (and its dependencies) must be available at runtime; they end up in your application's bin folder and get packaged with the app, etc. For FSharp.Data.SqlClient, the RTC is available for net40 and netstandard2.0 targets.
• The DTC is required to make the provided types available to your design-time tooling and to the compiler. In our case, the DTC has .Net Framework-only dependencies (Microsoft.SqlServer.Types and Microsoft.SqlServer.TransactSql.ScriptDom) and thus does not target netstandard2.0 - see discussion here, specifically bullet point 6.
• This means that a Core application should work just fine at runtime on any machine with an appropriate Core runtime installed, but in order to compile, you will need:
  • .Net Framework or Mono to be installed, and
  • fsc.props to be imported (see [Obsolete] How to enable type providers with new-style .NET SDK project files, dotnet build, .NET Standard and .NET Core programming dotnet/fsharp#3303)

[smoothdeveloper]

I just want to report that I'm in the process of updating a large VS2015 solution using the provider and it seems to work well, so I haven't seen a regression on that front.

@samhanes thanks a lot for the outstanding contribution, with your efforts the codebase got a nice cleanup along the upgrade.

[vjraitila]

@samhanes Gotcha, thanks. I can confirm that instructing the SDK to explicitly use mono as a compiler in fsc.props does work around this issue. I just had to adjust some paths in order to make it work.

Now if the maintainer of those Microsoft.SqlServer packages would just release netstandard2.0 builds for them... For what it's worth, I did send a message for them as well.

[samhanes]

@vjraitila Glad the workaround is working for you.

I can't imagine there is any missing API surface in Core required by those Microsoft.SqlServer libs - so it probably wouldn't be a huge lift to migrate them to netstandard. Unfortunately they aren't open source, so there's really nothing we can do other than whine about it.

[samhanes]

#320

[samhanes]

@richardjharding Can this be closed?

[richardjharding]

I was still getting the same issue - I created a new console app 2.1.403 sdk and now I get an error when paket attempts to install the package

Installing into projects:
 - Creating model and downloading packages.
Could not detect any platforms from 'typeproviders' in 'C:\Users\richa\.nuget\packages\fsharp.data.sqlclient\2.0.1\lib\typeproviders\fsharp41\net40\FSharp.Data.SqlClient.DesignTime.dll', please tell the package authors

not sure if this is a related issue? - am I using an unsupported sdk combination?

[samhanes]

@richardjharding What version of Visual Studio 2017 do you have installed? Are you using the fsc.props workaround?

[richardjharding]

using the fsc.props workaround - have other type providers working fine with that, not using visual studio - just vscode, dotnet cmd line

[samhanes]

@richardjharding I understand you are using the dotnet cli, but the workaround still requires a .net framework or Mono F# compiler as described in dotnet/fsharp#3303.

If you have an older version of VS 2017, (pre 15.3 I believe) you may simply need to update it.

[richardjharding]

ah ok I understand - I'll check version of compiler and update - do you know where I can find out for sure what the min version should be for this type provider?

[samhanes]

Based on the info in the issue linked above, I believe it is 15.3 but I have not verified that.

[panesofglass]

I'm also seeing this with CLI tools. I have tried the work-around, but it doesn't seem to help. To clarify, this is for an Exe, not a Library. Targeting a Library works fine. Building with MSBuild or in Visual Studio seems to work fine.

[vjraitila]

The workaround does the trick for me on Windows as well (and did on a Mac). Remember to import the fsc.props file in your project definition. Just placing it in your project root is not sufficient.

Also examine the props file whether it can find the compiler at least in one of the locations it tries to look for it.

After that I can successfully compile using Visual Studio and the command line.

When it comes to VS Code + Ionide, there's yet another trick. If you have set the F# auto completion runtime to use .NET Core in the settings.json it yet again does not work. Although Ionide does not particularly like it, you may force it to use the full framework with the following setting:

{
    ...
    "FSharp.fsacRuntime": "net"
    ...
}

I’ve only used NuGet up to this point, but Paket does give me the same error as you guys. I do not yet know if that is somehow related or not.

[samhanes]

@panesofglass To clarify, you are getting a compile-time error when you run dotnet build on an exe project, even with <Import Project="fsc.props" /> in the fsproj? But if you switch it to a library, it does compile? What TFM(s) are you targeting? Do you have a recent version of VS 2017 installed?

[samhanes]

@vjraitila I did not realize we have that issue with Ionide with FSharp.fsacRuntime": "netcore" - I had thought FsAutoComplete might respect the override tool path set in fsc.props, but it seems it does not. I don't know enough about that project to say whether it would make sense to explore changing that. Do you have any thoughts on that @Krzysztof-Cieslak ?

Honestly, I think there's still a chance that we could make the DTC work on netstandard2.0 by referencing the net40 Microsoft.SqlServer dependencies and crossing our fingers (as suggested @vasily-kirichenko). I'm planning to give that a try this weekend.

[samhanes]

Well, as far as I can tell, referencing the Microsoft.SqlServer libs from a netstandard version of the DTC isn't going to work. Several APIs moved to new assemblies (e.g., IBinarySerialize moved from System.Data to System.Data.SqlClient) and thus I'm getting compile-time errors. Maybe there is a way to workaround that sort of thing?

However, I did find this package, which is available for netstandard2.0. Apparently this is the parser used by tools like SSMS. The API is different from the parser in Microsoft.SqlServer.TransactSql.ScriptDom but it might work. I don't know that I am going to take this on right now, but it might be a way for us to get to a purely netstandard DTC.

[panesofglass]

@samhanes, yes, that is correct. I've added <Import Project="fsc.props" /> in the fsproj, and run dotnet build. I'm targeting netstandard2.0;net461 in the library and netcoreapp2.1 in the exe project. The exe project references the library. I have the latest VS 2017.

[panesofglass]

@samhanes, looks like I missed some projects. The exe project referenced two other libraries that also referenced the first library. Add <Import Project="fsc.props" /> to those fixed my build issue.

[projecteon]

Im running into issues with this as well. For me its split in 2.

With a clean solution, VS Code and Ionide behaves properly with intellisenceand errors.
To compile (and run) I had to use the <Import Project="fsc.props" /> workaround.
Once I have done a build/run, VS Code and Ionide starts reporting errors and is unable to properly do intellisence.

Does anyone have a workaround or working solution for having both of these working at the same time?
(And then without having to clean out all bin/obj folders after each build/run)

[vjraitila]

@projecteon does #318 (comment) help?

The gist of the matter is to force the autocompletion component of VS Code + Ionide to use the same compiler as the dotnet CLI does. It currently ignores the setting in fsc.props.

Although Sam did an excellent, and much needed job here by switching to the new type provider SDK, it unfortunately only got us half-way and still have to jump through quite a lot of hoops to make design-time work without a hitch when using the .NET Core SDK. AFAIK, the only way to properly fix the remaining issues requires either a significant refactoring effort or the Microsoft SQL Server team to provide netstandard ports of a couple of key libraries.

In short, we can use SqlClient with .NET Core at runtime, but still need some flavor of full .NET Framework when developing and building.

[projecteon]

@vjraitila unfortunately that workaround did not work for me. Unless I'm missing something. I added this setting on workspace settings.json level.

I'm still stuck clearing out obj/bin folders to get good intellisence in VSCode and Inoide after each build/dev build

[artem-hryb]

I have a the same error in VSCode + Ionide.
When I added fsc.props and included it into the project file and set the "FSharp.fsacRuntime": "net". The error have not gone and additionally a lot of compile errors are shown up in my SAFE(fable,elmish, giraffe) project.

[Trigun27]

Hello. This blog helps me to workaround this problem.

https://medium.com/@dsincl12/f-with-net-core-2-0-4-and-sqlprovider-d8f071119da9

For fscToolPath I did't find the exactly but this works fine.

<FscToolPath>C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\FSharp</FscToolPath> <FscToolExe>fsc.exe</FscToolExe>

and for the last step i paste this piece

<Target Name="PostBuild" AfterTargets="PostBuildEvent"> <Exec Command="copy C:\Users\{YOURUSERNAME}\.nuget\packages\system.data.sqlclient\4.5.1\runtimes\win\lib\netstandard2.0\System.Data.SqlClient.dll bin\Debug\netcoreapp2.1\" /> </Target>
It's for windows 10

[pimbrouwers]

I ultimately gave up trying to get this to work, and just opened my project in Visual Studio 2017 (Community Ed.) and everything worked instantly. If you have the paket extension, then all you'll need is a solution file and away you go.

[drk-mtr]

Using the dotnet core 3.0 SDK and targeting netstandard2.0 in VSCode with Ionide, I have to use a few workarounds. I haven't tested this properly, just some ideas to play with in case anyone is landing here via a search...

• Copy FSharp.Data.SqlClient.DesignTime.dll
  from .\packages\FSharp.Data.SqlClient\lib\typeproviders\fsharp41\net461
  to C:\Users\{Username}\.nuget\packages\fsharp.data.sqlclient\2.0.2\lib\netstandard2.0\
• Add the following to the .fsproj, where fscfix.bat is a file in the project root containing fsc %2 and fsc.exe is configured as a path environment variable:

<PropertyGroup>
    <FscToolPath>$(MSBuildThisFileDirectory)</FscToolPath>
    <FscToolExe>fscfix.bat</FscToolExe>
</PropertyGroup>

• Add a post build command to copy System.Data.SqlClient.dll as per Trigun27's note above.