A little background. I'm using:
and I'm using that simple HiLo sequence declaration in my context.
protected override void OnModelCreating(ModelBuilder modelBuilder) {
base.OnModelCreating(modelBuilder);
modelBuilder.ForNpgsqlUseSequenceHiLo();
}
Which creates my migration files as follows:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateSequence(
name: "EntityFrameworkHiLoSequence",
incrementBy: 10);
migrationBuilder.CreateTable(
name: "AspNetRoles",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SequenceHiLo),
Name = table.Column<string>(maxLength: 256, nullable: true),
NormalizedName = table.Column<string>(maxLength: 256, nullable: true),
ConcurrencyStamp = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_AspNetRoles", x => x.Id);
});
// other tables code goes here... All key has
// .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SequenceHiLo)
}
Everything works, but HiLo is assigning strange IDs (but still unique). How strange? Let me explain. For example:
I have tables
And I do a simple seed to database which assigns IDs as follow:
Orders:
OrderItem:
Roles:
So it looks like it have shared HiLo for all tables. I thought that It's HiLo per table.
Also I get error when I use clean new migration and do:
dotnet ef database drop && dotnet ef database update
I've got error (translated to English from Polish):
42P07: relation "EntityFrameworkHiLoSequence" already exist
@UPDATE
Thanks to @jpgrassi I found solution for strange IDs. I've made named HiLo for every model. It looks like this:
protected override void OnModelCreating(ModelBuilder modelBuilder) {
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<OrderItem>()
.Property(b => b.Id)
.ForNpgsqlUseSequenceHiLo("OrderItemsHiLo");
modelBuilder.Entity<Order>()
.Property(b => b.Id)
.ForNpgsqlUseSequenceHiLo("OrdersHiLo");
// More sequences goes below...
}
Now it all sound logical. But... I have new error:
42P07: relation "OrderItemsHiLo" already exist
My Up
section:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateSequence(
name: "OrderItemsHiLo",
incrementBy: 10);
// more code...
}
And in my Down
section in migration I have:
migrationBuilder.DropSequence(name: "OrderItemsHiLo");
My question is: Why this error is thrown on clean migration and how to get rid of it?
@UPDATE
I just removed that line and it resolved my second issue. Stupid me...
_context.Database.EnsureCreatedAsync();
Even though OP edited the post and pretty much solved the issue with my help, I'll post an actual answer so others running into the same "issue" can benefit.
Regarding the "strange" Id's generated. You need to create one sequence per entity, so they don't "share" ids. More on the provider documentation: HiLo Autoincrement Generation
Override the OnModelCreating
in your DbContext
class and add this:
modelBuilder.Entity<OrderItem>()
.Property(b => b.Id)
.ForNpgsqlUseSequenceHiLo($"Sequence-{nameof(OrderItem)}");
// same for others you want a sequence
Re-add the migrations and you should have something like this:
migrationBuilder.CreateSequence(
name: "Sequence-OrderItem",
incrementBy: 10);
// Order entity code ommited
Id = table.Column<int>(nullable: false)
.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SequenceHiLo)
Now regarding your "second" question, about not being able to re-create the database after running dotnet ef database drop
I was unable to reproduce it. I created a sample ASP.NET Core App which uses EF Core with PostegreSQL and I was able to drop/create the db normally.
I pushed the app to my GitHub account, so you can clone it and try it yourself: efcore-postgres-hilo-sequence