EF Cannot insert explicit value for identity column in table 'X' when IDENTITY_INSERT is set to OFF
I have to migrate data from one database to another. Keys should be preserved.
I'm using EF with an auto-generated .dbmx file. I've read many answers to similar problem and follow those suggestions: I do turn IDENTITY_INSERT ON exactly before inserting the entry; on the database diagram I changed the StoreGeneratedPattern to None on Id column (also tried to do the same with adding corresponding attribute to Id property).
Here is my code:
var myEntity = new MyTable
{
Id = 12345,
Name = "Lorem ipsum",
BrandId = brandId,
};
MyContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MY_TABLE_NAME ON");
MyContext.MyTable.Add(myEntity);
await MyContext.SaveChangesAsync(); //getting error here
When do that in Management Studio, it works just fine. The problem appears only when using Entity Framework.
What is looking weird that I can see in SQL Server Profiler that INSERT_IDENTITY command is executed and insert SQL comes with Id column, but seems is not executed. When I copy paste this SQL query it works just fine.
c# entity-framework ef-database-first identity-insert
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
add a comment |
I have to migrate data from one database to another. Keys should be preserved.
I'm using EF with an auto-generated .dbmx file. I've read many answers to similar problem and follow those suggestions: I do turn IDENTITY_INSERT ON exactly before inserting the entry; on the database diagram I changed the StoreGeneratedPattern to None on Id column (also tried to do the same with adding corresponding attribute to Id property).
Here is my code:
var myEntity = new MyTable
{
Id = 12345,
Name = "Lorem ipsum",
BrandId = brandId,
};
MyContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MY_TABLE_NAME ON");
MyContext.MyTable.Add(myEntity);
await MyContext.SaveChangesAsync(); //getting error here
When do that in Management Studio, it works just fine. The problem appears only when using Entity Framework.
What is looking weird that I can see in SQL Server Profiler that INSERT_IDENTITY command is executed and insert SQL comes with Id column, but seems is not executed. When I copy paste this SQL query it works just fine.
c# entity-framework ef-database-first identity-insert
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
add a comment |
I have to migrate data from one database to another. Keys should be preserved.
I'm using EF with an auto-generated .dbmx file. I've read many answers to similar problem and follow those suggestions: I do turn IDENTITY_INSERT ON exactly before inserting the entry; on the database diagram I changed the StoreGeneratedPattern to None on Id column (also tried to do the same with adding corresponding attribute to Id property).
Here is my code:
var myEntity = new MyTable
{
Id = 12345,
Name = "Lorem ipsum",
BrandId = brandId,
};
MyContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MY_TABLE_NAME ON");
MyContext.MyTable.Add(myEntity);
await MyContext.SaveChangesAsync(); //getting error here
When do that in Management Studio, it works just fine. The problem appears only when using Entity Framework.
What is looking weird that I can see in SQL Server Profiler that INSERT_IDENTITY command is executed and insert SQL comes with Id column, but seems is not executed. When I copy paste this SQL query it works just fine.
c# entity-framework ef-database-first identity-insert
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
I have to migrate data from one database to another. Keys should be preserved.
I'm using EF with an auto-generated .dbmx file. I've read many answers to similar problem and follow those suggestions: I do turn IDENTITY_INSERT ON exactly before inserting the entry; on the database diagram I changed the StoreGeneratedPattern to None on Id column (also tried to do the same with adding corresponding attribute to Id property).
Here is my code:
var myEntity = new MyTable
{
Id = 12345,
Name = "Lorem ipsum",
BrandId = brandId,
};
MyContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MY_TABLE_NAME ON");
MyContext.MyTable.Add(myEntity);
await MyContext.SaveChangesAsync(); //getting error here
When do that in Management Studio, it works just fine. The problem appears only when using Entity Framework.
What is looking weird that I can see in SQL Server Profiler that INSERT_IDENTITY command is executed and insert SQL comes with Id column, but seems is not executed. When I copy paste this SQL query it works just fine.
c# entity-framework ef-database-first identity-insert
c# entity-framework ef-database-first identity-insert
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
edited Nov 25 '18 at 7:46
marc_s
578k12911161262
578k12911161262
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
asked Nov 25 '18 at 4:30
mykhailovskyimykhailovskyi
386217
386217
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The problem is that the EDMX is and Designer.cs files are generated before you compile, when you genrate the schema from the DB and do not change. The validation is run against those what is in those files. They expect the identify_insert property to be enabled, so when you programatically disable it in the DB, the EF layer throws the exception you encountered. Basically saying - your DB doesn't match what I have.
Honestly, doing a large table data transfer this way is not what EF is designed for. You should use Bulk Data Copy
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53464656%2fef-cannot-insert-explicit-value-for-identity-column-in-table-x-when-identity-i%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The problem is that the EDMX is and Designer.cs files are generated before you compile, when you genrate the schema from the DB and do not change. The validation is run against those what is in those files. They expect the identify_insert property to be enabled, so when you programatically disable it in the DB, the EF layer throws the exception you encountered. Basically saying - your DB doesn't match what I have.
Honestly, doing a large table data transfer this way is not what EF is designed for. You should use Bulk Data Copy
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
add a comment |
The problem is that the EDMX is and Designer.cs files are generated before you compile, when you genrate the schema from the DB and do not change. The validation is run against those what is in those files. They expect the identify_insert property to be enabled, so when you programatically disable it in the DB, the EF layer throws the exception you encountered. Basically saying - your DB doesn't match what I have.
Honestly, doing a large table data transfer this way is not what EF is designed for. You should use Bulk Data Copy
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
add a comment |
The problem is that the EDMX is and Designer.cs files are generated before you compile, when you genrate the schema from the DB and do not change. The validation is run against those what is in those files. They expect the identify_insert property to be enabled, so when you programatically disable it in the DB, the EF layer throws the exception you encountered. Basically saying - your DB doesn't match what I have.
Honestly, doing a large table data transfer this way is not what EF is designed for. You should use Bulk Data Copy
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
The problem is that the EDMX is and Designer.cs files are generated before you compile, when you genrate the schema from the DB and do not change. The validation is run against those what is in those files. They expect the identify_insert property to be enabled, so when you programatically disable it in the DB, the EF layer throws the exception you encountered. Basically saying - your DB doesn't match what I have.
Honestly, doing a large table data transfer this way is not what EF is designed for. You should use Bulk Data Copy
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
answered Nov 25 '18 at 4:39
MattMatt
9,48853162269
9,48853162269
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
add a comment |
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
thanks for quick answer. That's partially true what you said. But I changed on EDMX diagram behavior for Id column.
– mykhailovskyi
Nov 25 '18 at 4:46
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53464656%2fef-cannot-insert-explicit-value-for-identity-column-in-table-x-when-identity-i%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
