Postgres Inserting/Updating ID -2147482647

Question

I am using Npgsql.EntityFrameworkCore to insert and update records in a table. However every so often it throw the error:

The instance of entity type 'UserAccount' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

Where my table/Entity is UserAccount.

I look at the database and I see that one of the IDs is -2147482647

This happens sporadically and I cannot reproduce or find any documentation on it. I have no errors before the ID is updated, once I update the ID to something appropriate the system runs normally again without the former error.

It seems that -2147482647 is the temporary ID that Entity uses for objects before it SavesChanges. Once it saves the changes it should update that ID to what the Database sequence should be. So when it tries to hold an object as -2147482647 it sees that ID is already in the system and throws the error.

Has anyone had ever had anything like this happen before? I have been troubleshooting it the best I can but without being able to reproduce it I'm just taking shots in the dark.

-2147482647 looks like the integer overflow of a 4-byte signed integer. — dthusian
– dthusian, Commented Nov 6, 2018 at 13:49
The error says it all, instead of using DbContext.Add() try to Find() first before adding the entity. If the entity exists just attach it (use Attach() ). Nevertheless the strange ID seems like an issue with the Npgsql package! — Obay Abd-Algader
– Obay Abd-Algader, Commented Nov 6, 2018 at 14:00
@ObayAbd-Algader, that is what I am doing. I am doing a Find() which is returning a result. Then I am editing that result and then calling a SaveChanges(), I am not using an Attach() under the assumption that Entity will do this anyway when I Save. — A. Hasemeyer
– A. Hasemeyer, Commented Nov 6, 2018 at 14:27

Mateus FlowinTech · Accepted Answer · 2021-01-29 14:02:51Z

1

I had this problem, and had to do with Db concerns.

First, make a select all from your-table, and order it by Id decreasing, you can limit to one, if you want to. Take a note of the last number the auto-increase generated.

Now, go to your Sequence files, open the table_sequence which you have the problem. Now see if the number match.

What happened to me, is that I made a backup from Production Db, and restored it on my Local Db, but I didn't delete the older tables from Local. So my sequence file was from the older Db Data.

Hope I help you, and I apologize for my english, it's self-learned.

edited Jan 29, 2021 at 14:02

answered Jan 29, 2021 at 13:55

Mateus FlowinTech

751 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Inserting/Updating ID -2147482647

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related