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

15

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?

asp.net-core
entity-framework-core
auto-increment
npgsql
asked on Stack Overflow May 6, 2018 by Andrey Kotov • edited May 9, 2018 by Andrey Kotov

2 Answers

17

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

answered on Stack Overflow May 6, 2018 by Trilok Kumar • edited May 6, 2018 by Trilok Kumar
0

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 on Stack Overflow May 11, 2020 by Yaroslav Trofimov

User contributions licensed under CC BY-SA 3.0