I've spent a day trying to migrate an Entity Framework 6 SQL Server CE to PostgreSQL.
I've copied the database over fine, but I can't seem to get the data provider to work.
Firstly I tried the older 2.2.7 version of the EF provider, which doesn't require .NET 4.5 (because our project can't use it), but that didn't work. I've been using NpgsqlConnection instead of SqlConnectionStringBuilder and changed the app.config to have a connection string with Host (I tried Server but it didn't like it) and Database values, and made sure the EF section is as follows:
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
</providers>
</entityFramework>
I've now ignored this limitation just to try and get it working, so I have the EF6 v3.1.1 version of the EF provider and the latest .NET provider (v3.2.2) and that's not working either.
In an attempt to fix it, I've also been trying to re-sync the EDMX from the SQL Server CE to the PostgreSQL replacement by installing the VS2015 extension, but it just gave a message saying an error occurred, so I tried this: https://github.com/npgsql/npgsql/issues/1514 which got further, opening the window, but then crashed as soon as I typed into the boxes. Since upgrading the project to .NET 4.5, this provider no longer appears at all...
I've tried reinstalling everything several times and restarting VS a lot, and Since upgrading I've also tried installing the GAC files, as it wasn't clear if the VS extension needed them (also v3.2.2).
Is anyone able to shed some light on how easiest to get all this working, preferably without the requirement of .NET 4.5?
Thanks
I got it working with installing the Visual Studio Extension Npgsql PostgreSQL Integration. Follow the instruction on this page: https://www.npgsql.org/efcore/
This thread led me to the right path: Add custom data source to visual studio 2015
Installing the npgsql extension as described in tsChan's answer wasn't enough for me, I also had to modify my app.config (I suspect because it was created before I installed the extension) to include the npgsql data provider in the EntityFramework section as follows:
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
</providers>
</entityFramework>
After I did that, the provider appeared in the wizard options.
PostgreSQL data provider missing from wizard in Visual Studio 2019 //It works