How to resolve EF Core migration error with HiLo?

asp.net-core c# entity-framework entity-framework-core

Question

A little background. I'm using:

  • .Net Core 2.2.3
  • Postgres as my database
  • Npgsql.EntityFramworkCore.PostgreSQL 2.20
  • EntityFramwork as ORM

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

  • Orders
  • OrderItems
  • Roles (completely unrelated to previous).

And I do a simple seed to database which assigns IDs as follow:

Orders:

  • 1 MyOrder1

OrderItem:

  • 2 MyOrderItem1
  • 3 MyOrderItem2
  • 4 MyOrderItem3

Roles:

  • 5 MyRole1
  • 6 MyRole2

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();
1
0
11/8/2019 4:22:42 PM

Accepted Answer

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

2
5/3/2019 8:11:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow