Failed to load provider "Npgsql" - Unable to find the requested .Net Framework Data Provider; postgres; rpg fails;

[afust003]

Hi Simon,

I have a .NET CORE 5 project with a .tt file targeting "Settings.DatabaseType= DatabaseType.PostgreSQL;" and trying to use EfCore5 v5.0.6 to create DBContext + Domain classes from an existing postgres database. The .tt generation process fails with the following error:

// WARNING: Failed to load provider "Npgsql" - Unable to find the requested .Net Framework Data Provider.
// It may not be installed.
// Allowed providers:
//    "System.Data.Odbc"
//    "System.Data.OleDb"
//    ....

The following link does not provide enough information to get the .tt to recognize postgres Provider: https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/wiki/PostgreSQL

I've even tried to register the npgsql.dll (that is installed as Nuget package into the same project) but this doesn't really help. There's also a confusing discussion here (https://www.npgsql.org/doc/installation.html) about Windows MSI Installer prior to Npgsql 5.0.0 but leaves out 5.0.0+ (saying we should use the Nuget package); but back to this link (https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/wiki/PostgreSQL) it agains mentions the GAC.

//tried cd [user].nuget\packages\npgsql\5.0.5\lib\net5.0" gacutil.exe /i Npgsql.dll

Could you provide some clarification? Really in a crunch after burning a few days trying to unsuccessfully migrate an EF6 TSqlServer to a EFCore5 postgres backend.

Thanks, -Alex

[sjh37]

Try using the msi installer from https://github.com/npgsql/npgsql/releases/tag/v4.1.8

The generator is a T4 template that runs externally to your project, and therefore cannot make use of any nuget packages you have installed into your project. It can however use ones that are installed in the GAC. The wiki article is a bit thin and I need to flesh it out fully as you are not the only person who has had the same problem.

[sjh37]

@afust003 I have updated the documentation in https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/wiki/PostgreSQL

Let me know how you get on.

[afust003]

Thank you Simon! Installing the MSI for 4.1.8 (https://github.com/npgsql/npgsql/releases/tag/v4.1.8) helped the T4 engine to discover the provider and allowed me to reverse engineer the postgres DB with EFCore.

The following are a few concerns extra concerns that come to mind with this work-around:

1. Given that reverse engineering has to work with only 4.1.8 version of npgsql, are there any major known issues with say using 4.1.8 for reverse engineering (via .tt) and then for the run-time to use npgsql 5.0+ or even 4.1.9+ versions? For example, I previously had 2 EF6 projects. The 1st project was used as a code-first approach for creating migrations and a 2nd project used reverse engineering EF6 to create the DBContext+Domain classes to use in the business layer. I intend to follow this 2 project pattern for EFCore (as it provides some benefits) but am concerned about potential conflicts arising from the differences between the npgsql versions.
2. Is there a plan to get RPG to work with npgsql 5.0+ in the near future? Is this even necessary (say for .Net core 3+ support) or to support other npgsql features (https://www.npgsql.org/doc/release-notes/5.0.html)?
3. It appears my current EF6 strategy of reverse engineering the DBContext + Domain classes strategy (which allows me to quickly detect changes via source control delta/change tool) and to use this project as the data access layer (DAL) core component will now force me to use npgsql 4.1.8? I've yet to test my approach and will provide an update but wanted to hear your some expert thoughts.

If some of this doesn't make sense, let me know and I can try to better explain.

Thanks, -Alex

[sjh37]

Hi @afust003

1. I've not come across any issues as I'm simply reverse engineering the database. If you are concerned, you could register a 5.0+ version of the npgsql and manually update your machine.config file.
2. The generator does not care what version of the npgsql is registered in the GAC, as long as the SQL it uses against the database returns the same expected data.
3. You should be able to continue to use whatever version of npgsql you want within your own solution. They should be independent. Try registering the same version of the npgsql you are using in your solution and manually update your machine.config file. That way there can't be any discrepancies, and no accidental use of the GAC npgsql in your running solution.

[andreabertanzon]

I had the same issue and what did the trick for me was during the installation of the msi package you linked, I had to select the GAC option during installation because doing it manually with the developer command line did not work.

I have Some things to mention in addition to this:

Using npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL

as a provider, makes the reverse-poco create a Context with UseSqlserver( ) and with a wrong: using Microsoft.Data.SqlClient; statements.

As the image below shows, it also adds SqlParameters.

In addition to this by registering another provider in GAC:

npgsql, Version=5.0.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL

and by replacing the 4+ version with the 5+ in the machine.config file the reverse poco fails to generate the context. It only works with:

npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL

Anyway, all the above are quickly fixed by manually fixing the context file but I just wanted to report them just in case.

[afust003]

@andreabertanzon, are you saying you were able to get the v5 to work? @andreabertanzon and @sjh37, I tried manually registering v5 in the GAC + updating machine.config to point to v5 but wasn't able to get RPG to work with v5 for reverse-engineering database. Again, my concern is code-first migration and RPG process parity divergence. That is failing to get RPG to work with npgsql v5 driver the same way we are using npgsql v5 for migrations.

[sjh37]

@afust003 I've never used v5, and have v4 installed which I know works ok.