Problems migrating to PostgreSQL

#1 · March 9, 2018, 3:12 pm

Hi,

Everything is working with MSSql and I have a problem now migrating to PostgreSQL.

I could manage seeding the data into the db by referencing the id (yes it needs the id too when seeding) but for the user and roles I don't know what and where to change?

When launching the app for the first time after having deleted the DB in postgres I get

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (4ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p9='?', @p3='?', @p10='?', @p4='?', @p5='?', @p6='?', @p7='?', @p8='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "AspNetRoleClaims" ("ClaimType", "ClaimValue", "RoleId")
VALUES (@p0, @p1, @p2)
RETURNING "Id";
UPDATE "AspNetRoles" SET "ConcurrencyStamp" = @p3, "Description" = @p4, "Name" = @p5, "NormalizedName" = @p6, "UpdatedBy" = @p7, "UpdatedDate" = @p8
WHERE "Id" = @p9 AND "ConcurrencyStamp" = @p10;
Npgsql.PostgresException (0x80004005): 23502: null value in column "Id" violates not-null constraint

In the database I only have the admin and no roles.

Running it for a second time it add the users and the roles but with the Pk as text.

link to print screen db: https://postimg.org/image/vgr6ctj9h/

When I launch the app I can log in with all the different built-in users but the roles seems not working. For example for the admin I don't have access to the admin page with user and roles, for the the approver role neither it has access to his own page.

When I switch back to MSSql it's working again as expected.

I suspect it is because the Pk is text instead of int.

Any idea?

#2 · March 15, 2018, 10:19 am

Is it an option for you to move to a DB first rather than Code first approach while using EF?

You could migrate the database via SQL and remove the automatic seeding that builds up the inital DB and use EF Core scaffolding to maintain the DAL with a DB first approach.

Jes

#3 · March 15, 2018, 3:04 pm

Thanks Jes, I can indeed try that later on.

Question will adding migrations and updating the database still work? (I'm new to ef core)

The reason why I need to move to another DB vendor is for licensing and running the app on a Linux, this is the power for me of .net core and angular.

I'm still busy developing full functionalities in the app and will try later DB first with Postgres or even other "Free" Linux DB.

I've also seen that it isn't because the IDs are text as in MSSql it's the same, the db is exactly the same in postgres as in mssql.

#4 · March 16, 2018, 7:59 am

@alve The beauty of using an orm like EntityFrameworkCore is that its cross platform and allows you to switch between databases. And the orm with its appropriate providers should take care of the underlying differences. You shouldn't have to make further changes in to your business code.

Could this be a configuration issue with the PostgreSQL you're using? E.g. this link shows how to configure entityframework core with a postgreSQL provider like npgsql

#5 · March 17, 2018, 10:57 am

@alve it sound like the issue you have may be a bug in the provider or configuration. Regarding EF Code First migrations, I don't use them and I'm not a fan but as I understand it, you can initialise them to start afresh from any desired state of an existing schema & data.

https://weblog.west-wind.com/posts/2016/Jan/13/Resetting-Entity-Framework-Migrations-to-a-clean-Slate

Each to their own of course, we are using DB First in .NET Core and it is working very well for us. To be honest I have never been a fan of EF in the past because it was too much of a "black box" but with .NET Core the generated code is clean and understandable. You can use partial classes to preserve custom code also BTW.

Whenever we want to push out changes to our data model we can regenerate our entire DAL from our schema with a single command:

Scaffold-DbContext -connection "Server=AAA.BBB.CCC.com;Database=Some.Database;Password=FakePass;Persist Security Info=True;User ID=DBUSER;" -provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Now anyone care to explain why they would prefer instead to write this code by hand? 🙂

#6 · April 12, 2018, 7:13 am

I still don't get it working.

I've checked everything and set up everything as found in the different blog/posts online. I can get for example the customers but not the roles.

When I try to remove a migration I get an error, from what I see it has to be with the driver.

I'll perhaps create a new project instead of starting from the pro template.

On the other hand can someone explain what the DesignTimeDbContextFactory does?

IMPORTANT!

This forum is now archived. Click here for the New Support Forum