Auto-Incremented value not working in PostgreSQL when using EntityFramework Core

Question

It seems like the auto-increment function for PostgreSQL doesn't seem to work.

I have the following code:

namespace project.Models
{
   public class DatabaseModel
   {
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       [Column(Order=1, TypeName="integer")]
       public int ID { get; set; }
   }
 }

When I update the database (after doing a migration) the ID column is the primary key without being a auto-increment.

When I try to add a new object to the database I get the following error:

Microsoft.EntityFrameworkCore.DbContext[1]
  An exception occurred in the database while saving changes.
  Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23502: null value in column "ID" violates not-null constraint

Can anyone help solve this problem? How can I get the key to be auto-incremented?

NOCARRIER · Accepted Answer · 2023-07-10 22:27:58Z

11

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }

To use identity columns for all value-generated properties on a new model, simply place the following in your DBContext's OnModelCreating() event handler:

protected override void OnModelCreating(ModelBuilder builder) 
{
    builder.ForNpgsqlUseIdentityColumns();
}

This will assure all keys and other properties which have ValueGeneratedOnAdd() use Identity by default.

You can use ForNpgsqlUseIdentityAlwaysColumns() to have Identity always, and you can also specify identity on a property-by-property basis with UseNpgsqlIdentityColumn() and UseNpgsqlIdentityAlwaysColumn().

postgres efcore value generation

edited Jul 10, 2023 at 22:27

NOCARRIER

2,6445 gold badges42 silver badges62 bronze badges

answered Sep 9, 2018 at 8:24

Satheesh Davidson

3614 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NOCARRIER Over a year ago

Just a quick note that presently Entity Framework does not require (nor supports) this pattern: learn.microsoft.com/en-us/ef/core/modeling/…

Phill · Accepted Answer · 2016-10-31 12:23:04Z

5

Because you created the column with the specific type integer you need to specify the type as serial for PostgreSQL to generate the id for you.

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

answered Oct 31, 2016 at 12:23

Phill

19k7 gold badges66 silver badges104 bronze badges

1 Comment

Chris Rice Over a year ago

This is no longer the case. PostgreSQL for EFCore now natively supports identity columns. npgsql.org/efcore/modeling/generated-properties.html

Samet Ertas · Accepted Answer · 2020-12-19 17:29:40Z

1

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.UseSerialColumns();

    }

You should use this in your DbContextClass to create auto incremented value for primary key

edited Dec 19, 2020 at 17:29

answered Dec 19, 2020 at 17:19

Samet Ertas

777 bronze badges

Collectives™ on Stack Overflow

Auto-Incremented value not working in PostgreSQL when using EntityFramework Core

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related