Why EF Core 5 does not create "Primary Key Autoincrement" using "[Key]" attribute on "Id" property of my entity "Address"?

Question

I am using Microsoft.EntityFrameworkCore v5.0.3 in my VS 2019 project (running on .NET Core 3.1).

As you can see in the entity class Address the first property int Id has the attribute [Key]:

[Index(nameof(Country))]
[Index(nameof(ZIP))]
[Index(nameof(City))]
public class Address
{
    [Key]
    public int Id { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; }

    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime ChangedAt { get; set; } = DateTime.UtcNow;

    [StringLength(EFConst.MAX_LEN_COUNTRY)]
    public string Country { get; set; }     
    
    [StringLength(EFConst.MAX_LEN_ZIP)]
    public string ZIP { get; set; }
    
    [StringLength(EFConst.MAX_LEN_CITY)]
    public string City { get; set; }

    [StringLength(EFConst.MAX_LEN_STREET)]
    public string Street { get; set; }
}

But after the database schema is created, I don't see neither PRIMARY KEY nor AUTOINCREMENT in the CREATE TABLE script using Microsoft SQL Server Management Studio as you can see here:

CREATE TABLE [dbo].[Addresses]
(
    [ID] [int] NOT NULL,
    [RowVersion] [timestamp] NOT NULL,
    [CreatedAt] [datetime] NOT NULL,
    [ChangedAt] [datetime] NOT NULL,
    [Country] [nvarchar](64) NULL,
    [ZIP] [nvarchar](32) NULL,
    [City] [nvarchar](64) NULL,
    [Street] [nvarchar](128) NULL,

    CONSTRAINT [PK_dbo.Addresses] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Now, when this code is executed:

var addr1 = db.Addresses.Add(new Address
{
    Country = "SampleCountry",
    City = "SampleCity",
    ZIP = "12345",
    Street = "SampleStreet 123"
});

db.SaveChanges();

I get this exception at db.SaveChanges():

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details

Inner Exception

SqlException: Cannot insert the value NULL into column 'ID', table 'NSA.dbo.Addresses'; column does not allow nulls. INSERT fails. The statement has been terminated.

I am not wondering why the exception is thrown.

Why is the column ID in table Addresses not defined as PRIMARY AUTOINCREMENT?

I already have searched/googled the whole internet... and reached the borders of it.

None of the solutions on Stackoverflow (or other places) worked for me! As explained in official documentation of EF Core my simple project should work!

I already made step-by-step try-and-error following experiments (single and combination of some) with no success:

Using "Data Annotations" (attributes):
- [Key]
- [Required]
- [Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
- [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
Renaming Id to AddressId (+ combinations of using attributes above)
Using Fluent API by overriding void OnModelCreating(...) and using modelBuilder.Entity<Address>().HasKey(a => a.Id);
Re-defining property Id as: int? Id, int ID, int? ID, int AddressId, int? AddressId, int AddressID, and int? AddressID

Am I doing something wrong?

How can I define the "Id" property of "Address" class as PRIMARY KEY + AUTOINCREMENT by using EF Core?

Thank you!

What **database** are you using in the backend?? SQL Server for instance **doesn't** have an `AUTOINCREMENT` keyword - it's called `IDENTITY` instead. — marc_s, Feb 21 '21 at 07:10
@marc_s I am using SQL EXPRESS (MSSQL 15). There is also no "IDENTITY" keyword in the SQL create script! — Pedram GANJEH HADIDI, Feb 21 '21 at 08:15
did you do another migration and database update after using this annotation ? [DatabaseGenerated(DatabaseGeneratedOption.Identity)] — Mohamed Adel, Feb 21 '21 at 08:20
@MohamedAdel You are right! The problem was I did not delete the db before re-creating it (facepalm). Thank you very much for your help and saving my day. — Pedram GANJEH HADIDI, Feb 21 '21 at 08:37
@user14867525 you are welcome, I added as answer please accept it for further needs — Mohamed Adel, Feb 21 '21 at 08:41

score 3 · Accepted Answer · edited Feb 21 '21 at 08:41

3

Did you do another migration and database update after using this annotation ?

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

You just need to do another migration and update your DB

edited Feb 21 '21 at 08:41

marc_s

732,580
175
1,330
1,459

answered Feb 21 '21 at 08:40

Mohamed Adel

492
3
8

1

This should not be necessary. The documentation says, "By convention, a property named Id or Id will be configured as the primary key of an entity." – tnk479 Mar 15 '21 at 19:56

score 2 · Answer 2 · answered Feb 21 '21 at 08:42

2

Thank to @MohamedAdel, I solved the problem deleting the database and re-created it. Now the "ID" column is defined as

[Id] [int] IDENTITY(1,1) NOT NULL

and everything works great. Thank you all for your help!

answered Feb 21 '21 at 08:42

Pedram GANJEH HADIDI

23
8

Why EF Core 5 does not create "Primary Key Autoincrement" using "[Key]" attribute on "Id" property of my entity "Address"?

2 Answers2