SqlProgrammabilityProvider design-time: "The given key was not present in the dictionary" and then "No constructors are available" the first time an sproc is used until project reload

[cmeeren]

The first time I use an sproc in code using SqlProgrammabilityProvider, I often get the following error:

The given key was not present in the dictionary

Then after some seconds, it changes to this error:

No constructors are available for the type

This happens even when I get Intellisense autocompletion for the sproc name.

If I remove it and retype it, I only get the second error ("no constructors ...").

The error disappears after project reload (I usually just make an edit to the fsproj and save, and revert and save again to reload the project without losing the open documents).

If it's relevant, my connection strings are

Data Source=.\\SQLEXPRESS;
Initial Catalog=MY_DB_NAME;
Integrated Security=True"

I haven't always gotten the errors, but I seem to consistently get the error for the project I'm working on now.

Unfortunately I don't have a solid repro. Let me know if I can be of further help.

[smoothdeveloper]

@cartermp / @dsyme this is where dotnet/fsharp#4978 would save the day.

It is hard to figure those issues without full stack trace being reported. Hard for library maintainers, and kind of blocking/involve long debugging/trial&error when there aren't clear reproducible steps.

Also, I noticed that the instanciated types wouldn't refresh in the IDE since some update in vs2019, while it used to work well before (changing the .sql file or connection string, the types would refresh), it seems some project level caching of types avoids the tooling to refresh the types after touching the type instanciations.

Even after a build which compiles, squiggles seem to remain in editors.

The only work around for this AFAIK (beside closing VS) is to unload/reload project, this makes usage of the provider.

[smoothdeveloper]

@cmeeren, I suspect this is related to caching, I've noticed while working on experimental changes, that the lookup in the cache may not be deterministic.

You may give a try at the branch in #351 which has minor changes around the cache.

I don't have deep understanding on the lifecycle of the cache nor how the type provider component are handled in tooling context, but I remember seeing the reported issue with the release version, and that my changes in this branch also make it more "deterministicish" when the type caching occurs.