I made a few tables in EF and entered in some seed data where I give value to a few columns with a primary key. When I run the application I am getting the error message:
Cannot insert explicit value for identity column in table ‘Persons’ when IDENTITY_INSERT is set to OFF.
How do I turn it on? I read on here to use:
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
above the property that is a primary key. I am still getting the same error message unfortunately. Please help.
I added [DatabaseGenerated(DatabaseGeneratedOption.None)]
to all my properties that have a primary key. When I ran the migration I can see that the identity column is removed, But I am still getting the same error message.
When I go into SQL SEO I can still see the identity column on my primary key. I tried refreshing the database. What am I doing wrong? The only thing I can do is go into properties and remove the identity, but why can’t I do it the way mentioned above?
12
Answers
In EF Core 1.1.2, I got this to work with transactions. In my “database initializer” that put seed data into the tables. I used the technique from this EF6 answer. Here’s a sample of the code:
Another way is to explicitly open a connection then
SET IDENTITY_INSERT <table> ON
.Apparently once a connection has been explicitly opened before an EF request, that connection is not automatically closed by EF, so the setting is applied to the same connection context.
This is the same reason that Steve’s response with transactions works as transactions keep a connection alive.
Improved solution based on NinjaCross’ answer.
This code is added directly in the database context class and allows to save changes by also specifying that identity insert is needed for a certain type (mapped to a table).
Currently, I have only used this for integrative testing.
Steve Nyholm
‘s answer works fine, but I will provide some extra explanation and some generic code with exception handling.Normally the context takes care of the transaction, but in this case manually taking care of it is required. Why?
Database context will generate a
BEGIN TRAN
after theSET IDENTITY_INSERT
is issued. This will make transaction’s inserts to fail since IDENTITY_INSERT seems to affect tables at session/transaction level.So, everything must be wrapped in a single transaction to work properly.
Here is some useful code to seed at key level (as opposed to table level):
Extensions.cs
DbInitializer.cs
(assumes that model class name is the same as table name)
Below solution worked for me.(Link)
I have added below annotations. and removed
[Key]
Annotation.Namespace can be changed according to the entity framework version. For Entity framework core namespace is
System.ComponentModel.DataAnnotations.Schema
I did not face a data migration since I have tried in a new project.
@Steve Nyholm answer is OK, But in .Net core 3 ExecuteSqlCommand is Obsolete, ExecuteSqlInterpolated replacement of ExecuteSqlCommand:
In order to add related entities with an object graph using the
DbContext
I used aDbCommandInterceptor
which automatically setsINSERT_IDENTITY ON
for the table in question and thenOFF
after the insert. This works with IDs manually set andDbContext.SaveChanges
. I used it in my integration tests but after a performance optimization maybe it could be suitable for production code in some cases. Here is my answer to a similar SO question which explains the details.Use “SET IDENTITY_INSERT [table] ON/OFF” into transaction
Note, my entityframework was generated by reverse engineering
Another way is to use ExecuteSqlRaw. Unlike ExecuteSqlInterpolated, you do not have to convert your passed string to a formattable string type.
Had to deal with the same issue and this seems to be a clean solution.
Credit to >> https://github.com/dotnet/efcore/issues/11586
I have made some changes so it now works with .Net Core 3.1 + (Tested in .Net 5) and also added this Method SaveChangesWithIdentityInsert
Usage
The solution proposed by @sanm2009 contains some nice ideas.
However the implementation has some imperfections related to the misusage of Task/async/await.
The method SaveChangesWithIdentityInsert does not return Task, nor await for the calls to EnableIdentityInsert and DisableIdentityInsert.
This could lead to undesired side effects.
The following implementations supports both async/await, and non-awaitable paradigms.
If you don’t want to use EF core’s auto-generating primary key values feature, you can turn it off. You can add your data to the primary key It should resolve the error – Set Identity Insert off
Setting Database Generation option to None helped me. You can find more about it here- https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations
You should keep it off, dont turn it on !
Its turned off for good reasons (security performance)…
Do this instead in your code.
For example, outside your default Create controller when you need to
_context.add()
a new entry in your DB: