HasData seeding causes Add to fail with PostgresException: 23505 on calling SaveChanges

[bugproof]

Npgsql.EntityFrameworkCore.PostgreSQL version: 2.2.0-preview1

HasData seeding causes Add to fail when calling SaveChangesAsync.

Example:

public class Item 
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ItemConfiguration : IEntityTypeConfiguration<Item>
{
    public void Configure(EntityTypeBuilder<ProductGroup> builder)
    {
        builder.HasData(new Item { Id = 1, Name = "Test" });
    }
}

Now when you want to add new Item, this code won't work and will cause PostgresException: 23505:

var item = new Item { Name = "Cool item" };
db.Add(item);
await db.SaveChangesAsync(); // PostgresException: 23505

Note that this code will work fine if we won't use HasData.

What you have to do instead, you have to set Id explicitly to 0:

var item = new Item { Id = 0, Name = "Cool item" }; // default or default(int) doesn't work, it must be 0...

Or it starts to work too after inserting the item manually using some DB manager.

I think #367 and #573 is related?

[roji]

Always seed with negative values to make sure seeded values don't collide with values later generate from the identity sequence.

[roji]

Duplicate of #367

[BojidarStanchev]

"Always seed with negative values to make sure seeded values don't collide with values later generate from the identity sequence."

All due respect, but this is not a solution, but an ugly hack. This issue should not be closed, instead, it should be properly addressed and fixed.

[roji]

@BojidarStanchev this difficulty is related to the way PostgreSQL manages sequences and serial columns. See conversation with possible proposals in #573 (comment). At the end of the day I'm not sure why negative values is an ugly hack or problematic - why is an id of -1 worse than 1?

[BojidarStanchev]

-1 is more negative, we need more positivity in the world.
Now seriously, -1 is not worse technically. But me having to manually write this in the migrations sucks, Especially during development because migrations will get deleted multiple times, then you forget to add 1 line there (or maybe someone else in the team doesn't yet know about this problem). This will result in a lot of time wasted on debugging.

If you consider how many people wasted a few hours researching why this happens there are probably thousands of work hours lost because of this not working as intended.

[roji]

But me having to manually write this in the migrations sucks,

The point is that you have to manually write an ID in the migrations, in any case - it just can't be positive. This is how EF Core works, and if you don't you actually get an exception telling you to assign a negative value (so it's better to raise this question with EF Core rather than Npgsql).

Especially during development because migrations will get deleted multiple times

Just to make sure there's no confusion, the seed data isn't supposed to be written in the migrations, it's supposed to be written in your context model, via HasData() calls in your OnModelCreating(). If you're manually added seed lines into generated migrations then this could be the source of the problem, try adding them to the model instead.

[bugproof]

Don't use HasData: dotnet/EntityFramework.Docs#1055