I have a C# app using EF Core 7 and I am trying to insert a number of rows into a table that has an identity(1,1) column as its PK.
I am making a call to SqlQueryRaw to turn ON identity insert as follows (with the context):
Database.SqlQueryRaw<String>($"SET IDENTITY_INSERT BobTable ON");However, when I try and add the data and call submit it throws an exception that IDENTITY_INSERT is OFF.
I also have [DatabaseGenerated(DatabaseGeneratedOption.None)] set on the entity. I've also tried adding ValueGeneratedNever() in ModelBuilder , and in SSMS turning identity_insert on in T-SQL before running the code with the same error. Turning it on and doing the insert from SSMS works as expected.
The basic sequence is for is:
BeginTransaction
IDENTITY_INSERT ON
SaveChanges()
IDENTITY_INSERT OFF
CommitTransactionDoes anyone know why the IDENTITY INSERT is being ignored, or do you have a solution for the issue?
1条答案
按热度按时间b09cbbtk1#
As usual, half an hour after asking the question I found a solution.
When the table is not an identity table, I just use normal SaveChanges().
When the table had an identity column, I needed to use CreateDbCommand() on the DBSet to create and execute the SET statements (ON and OFF) with a call to SaveChanges() in between. This required opening a new connection to the database.