How to tell Entity Framework that my ID column is auto-incremented (AspNet Core 2.0 + PostgreSQL)?

Question

Code is simple. Tag.cs entity:

public partial class Tag
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

HomeController.cs class:

public async Task<IActionResult> Index()
{
   tagRepository.Insert(new Tag 
               { 
                   Name = "name", 
                   Description = "description" 
               });
   await UnitOfWork.SaveChangesAsync();  // calls dbContext.SaveChangesAsync()

   return View();
}

TagRepository.cs class:

    // Context it's a usual DBContext injected via Repository's constructor
    public virtual void Insert(TEntity item)
                => Context.Entry(item).State = EntityState.Added;

Tag table was created by running:

CREATE TABLE Tag (
    ID SERIAL PRIMARY KEY,
    Name text NOT NULL,
    Description text NULL
);

When run my application I get an error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (28ms) [Parameters=[@p0='?', @p1='?', @p2='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "tag" ("id", "description", "name")
      VALUES (@p0, @p1, @p2);
Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "tag_pkey"
   at Npgsql.NpgsqlConnector.<DoReadMessage>d__157.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__156.MoveNext()

As you can see Entity Framework tries to send id=0 value to the DB, but there is already a record with id=0 in my DB and that's why it throws duplicate key error.

I didn't find any answer so far and my question is: how can I get rid of this error and tell Entity Framework that my id column is auto-incremented and there is no need to update it?

Trilok Kumar · Accepted Answer · 2018-05-06T18:25:49.757

28

You have to use here "ValueGenerationOnAdd()". As the issue you are getting is already reported on GitHub. Please find the below link.

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/73

You can find more info regarding Generated Value pattern from following link.

Value generated on add

public classs SampleContext:DBContext{
public DbSet<Tag> Tag { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Tag>()
        .Property(p => p.ID)
        .ValueGeneratedOnAdd();
 }
public class Tag{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Description{get;set;}
  }
}

Source:- https://www.learnentityframeworkcore.com/configuration/fluent-api/valuegeneratedonadd-method

Hope this will help

edited May 06 '18 at 18:25

answered May 06 '18 at 18:14

Trilok Kumar

571
6
12

2

I've generated context from existing DB (using command `Scaffold-DbContext`) and interesting stuff is that all my `id` properties in all entities were generated with `.Property(p => p.Id).ValueGeneratedNever()`. This might be a bug, I guess.. Anyway, I've replaced it with `.ValueGeneratedOnAdd()` and it works. Thank you for links and explanation. – Andrey Kotov May 06 '18 at 18:48
I use ValueGeneratedOnAdd() to automatically create id, but it doesn't work. Appreciate if you or @AndreyKotov could take a look. See https://stackoverflow.com/questions/59673778/how-to-avoid-null-value-in-column-violates-not-null-constraint-using-valuegen – Tim Jan 10 '20 at 06:06
FYI you do not need to manually set ValueGeneratedOnAdd if your property is called Id or TagId - that's done for you by convention (see https://learn.microsoft.com/en-us/ef/core/modeling/keys?tabs=data-annotations and https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations). – Shay Rojansky Jan 10 '20 at 18:23
The source referred to in the answer has nothing to do with the question. The question is about Postgre SQL, but not about the SQL Server. So, the answer does not work for me. – manymanymore May 11 '20 at 19:15

score 5 · Answer 2 · answered May 11 '20 at 19:19

5

According to the Postgre SQL docs here is an example which shows how to achieve the desired result:

protected override void OnModelCreating(ModelBuilder modelBuilder)
   => modelBuilder.Entity<Blog>().Property(b => b.Id).UseIdentityAlwaysColumn();

answered May 11 '20 at 19:19

manymanymore

2,251
3
26
48

`.UseIdentityAlwaysColumn()` doesn't resolve.. what is the EF Core version do you use? – anatol Jun 05 '20 at 13:00
btw, I've tried with `UseNpgsqlIdentityAlwaysColumn()` and `UseNpgsqlSerialColumn()` - these do not work – anatol Jun 05 '20 at 13:18
1

@anatol, hi. `Microsoft.EntityFrameworkCore.Design` - `3.1.3`; `Microsoft.EntityFrameworkCore.Tools` - `3.1.3`; `Npgsql.EntityFrameworkCore.PostgreSQL` - `3.1.3`. – manymanymore Jun 05 '20 at 21:05

score 1 · Answer 3 · answered Jun 06 '20 at 08:25

After a lot of wasted time on research I've solved it absolutely unexpected. Actually, the solution to my problem lies on the surface, because the problem is not in EF and not in it's stuff. In my case it was SEQUENCE. I mean, involved table has serial column and related sequence just was changed by some side-effect. In another words - sequence was restarted from 1 and at the same time the table is already having values with 1, 2, 3 ... etc. That's why postgres throws 'duplicate key' error. So, the solution is:

ALTER SEQUENCE "your_table_seq" RESTART WITH your_value;

How to tell Entity Framework that my ID column is auto-incremented (AspNet Core 2.0 + PostgreSQL)?

3 Answers3