Auto-increment on partial primary key with Entity Framework Core

Question

I have declared the following model using the EF Core fluent API   modelBuilder Entity lt Foo gt         HasKey p   gt  new   p Name  p Id       Both fields are marked as the primary key when I create the database in PostgreSQL but the Id field is not marked as auto increment  I have also tried to add   Key   DatabaseGenerated DatabaseGeneratedOption Identity     to the Id field under Foo without it making any difference on the migration code  Is there a way to make the Id AI although it is a PPK

User · Answer

To anyone who came across this question who are using SQL Server Database and still having an exception thrown even after adding the following annotation on the int primary key

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

Please check your SQL, make sure your the primary key has 'IDENTITY(startValue, increment)' next to it,

CREATE TABLE [dbo].[User]
(
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)

This will make the database increments the id every time a new row is added, with a starting value of 1 and increments of 1.

I accidentally overlooked that in my SQL which cost me an hour of my life, so hopefully this helps someone!!!

User · Answer

Specifying the column type as serial for PostgreSQL to generate the id     Key   DatabaseGenerated DatabaseGeneratedOption Identity    Column Order 1  TypeName  serial    public int ID   get  set      https   www postgresql org docs current static datatype-numeric html DATATYPE-SERIAL

User · Answer

Annotate the property like below    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 context s OnModelCreating     builder ForNpgsqlUseIdentityColumns      This will create make all keys and other properties which have  ValueGeneratedOnAdd   have 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

User · Answer

First of all you should not merge the Fluent Api with the data annotation so I would suggest you to use one of the below   make sure you have correclty set the keys  modelBuilder Entity lt Foo gt                 HasKey p   gt  new   p Name  p Id     modelBuilder Entity lt Foo gt    Property p   gt  p Id  HasDatabaseGeneratedOption DatabaseGeneratedOption Identity     OR you can achieve it using data annotation as well   public class Foo        DatabaseGenerated DatabaseGeneratedOption Identity        Key  Column Order   0       public int Id   get  set          Key  Column Order   1       public string Name  get  set

User · Answer

Well those Data Annotations should do the trick  maybe is something related with the PostgreSQL Provider    From EF Core documentation      Depending on the database provider being used  values may be generated   client side by EF or in the database  If the value is generated by the   database  then EF may assign a temporary value when you add the entity   to the context  This temporary value will then be replaced by the   database generated value during SaveChanges    You could also try with this Fluent Api configuration   modelBuilder Entity lt Foo gt                 Property f   gt  f Id               ValueGeneratedOnAdd      But as I said earlier  I think this is something related with the DB provider  Try to add a new row to your DB and check later if was generated a value to the Id column

[c#] Auto-increment on partial primary key with Entity Framework Core

Examples related to c#

Examples related to asp.net

Examples related to entity-framework

Examples related to postgresql

Examples related to entity-framework-core