Cannot insert explicit value for identity column in table when identity insert is set to off

Question

I tried filling in a table I generated using EF Core with values through Microsoft SQL Management Studio, since the list was predefined and wouldn't require editing in the future. I got the error I wrote in the title inside the console of SSMS.

I managed to resolve the issue by writing SET IDENTITY_INSERT Countries ON above the script that would insert all the values.

This problem seems to have to do with the primary key attribute.

My question is: how do I fix this using EF Core methods, instead of the SET IDENTITY_INSERT ON SQL command, and what exactly is the issue?

Problem table:

public class Country
{
    public string Code { get; set; }
    [Required]
    public int Id { get; set; }
    public string LongName { get; set; }
    [Required]
    public string Name { get; set; }
}

*"This problem seems to have to do with the primary key attribute."* It's nothing to do with the primary Key, it's to do with the `IDENTITY` property. You cannot insert into a column with the `IDENTITY` property unless you use `SET IDENTITY_INSERT ON`. The fact that you *want* to always insert into the column infers that the column should not have the `IDENTITY` property. — Larnu, Oct 22 '19 at 08:36
Possible duplicate of [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) — HariHaran, Oct 22 '19 at 08:40
@Larnu what does the identity property do? Also, which one of my EF Core props would be it? — NAlexP, Oct 22 '19 at 08:43
[CREATE TABLE (Transact-SQL) IDENTITY (Property)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15) @NAlexP . — Larnu, Oct 22 '19 at 08:43

score 1 · Accepted Answer · answered Oct 22 '19 at 08:42

1

You need to apply annotation attribute to Id and it will do the job

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

answered Oct 22 '19 at 08:42

Saif

2,233
2
13
31

What does that annotation actually mean, though? – NAlexP Oct 22 '19 at 08:44
It is .net attributes that can be applied on an entity property to override default convention – Saif Oct 22 '19 at 12:24

Cannot insert explicit value for identity column in table when identity insert is set to off

1 Answers1