System.Data.Entity.Infrastructure.DbUpdateException错误,即使我没有更新id列 [英] System.Data.Entity.Infrastructure.DbUpdateException error even when i am not updating the id column
问题描述
我正在使用一个项目,我正在使用Entity框架来操纵sql server 2014上的DB。
我的代码如下:
I am working on a project where I am using Entity framework to manipulate a DB on sql server 2014. My code is the following:
private void BtnAddUser(object sender, EventArgs e)
{
var u = new User();
u.username = txtBoxNewUser.Text;
u.password = txtBoxNewPass.Text;
u.rank = cmbBoxRank.GetItemText(this.cmbBoxRank.SelectedItem);
using (var db = new ProjetPooEntities2())
{
db.Users.Add(u);
db.SaveChanges();
}
}
请注意,代码运行良好但是当我按添加按钮将创建的用户添加到数据库时,db.saveChanges()上显示错误,它说:
Please note that the code runs perfectly but it when i press the Add button to add the created user to the db an error shows on "db.saveChanges()" and it says:
System.Data.Entity.Infrastructure.DbUpdateException:'更新条目时出错。
内部异常是:
SqlException:当IDENTITY_INSERT设置为OFF时,不能在表'User'中为标识列插入显式值。
System.Data.Entity.Infrastructure.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.' And the inner exception is: SqlException: Cannot insert explicit value for identity column in table 'User' when IDENTITY_INSERT is set to OFF.
我将db中的标识列设置为id,并将其设置为从1开始自动递增1。
我尝试过搜索很多解决方案,但我没有发现。
任何帮助将不胜感激!
I have set the identity column in the db to "id" and it is set to auto-increment by 1 starting from 1. I have tried searching a lot for a solution but i found nothing. Any help will be appreciated!!
推荐答案
假设用户
entity具有以下结构:
Suppose the User
entity has this structure:
public class User
{
public int id { get; set; }
public string username { get; set; }
public string password { get; set; }
public string rank { get; set; }
}
如果使用代码优先,请添加 DatabaseGeneratedAttribute
与选项 DatabaseGeneratedOption.Identity
为此设置 id
as身份自动增量属性:
If Code First is used, add DatabaseGeneratedAttribute
with option DatabaseGeneratedOption.Identity
as this to set id
as identity autoincrement property:
public class User
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
... // other properties
}
但是,如果 Database First 被使用,打开EDMX模型设计器,在 id
字段上使用属性上下文菜单,并设置 StoreGeneratedPattern
到身份
:
However if Database First is used, open EDMX model designer, use "Properties" context menu on id
field and set StoreGeneratedPattern
to Identity
:
然后,使用XML编辑器打开EDMX文件,确保 StoreGeneratedPattern =Identity
在 id
属性如下所示:
Then, open EDMX file with XML editor and ensure it has StoreGeneratedPattern="Identity"
on id
property as shown below:
<EntityType Name="User">
<Key>
<PropertyRef Name="id" />
</Key>
<Property Name="id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
</EntityType>
如果以上方法仍然无法工作,您可以执行 SET IDENTITY_INSERT
查询 DbContext
作为(必须使用 StoreGeneratedPattern =无
EDMX或 DatabaseGenerated(DatabaseGeneratedOption.None)
在Code First中正常工作):
If those ways above still won't work thereafter, you can execute SET IDENTITY_INSERT
query in DbContext
as this (must be reverting with StoreGeneratedPattern="None"
in EDMX or DatabaseGenerated(DatabaseGeneratedOption.None)
in Code First to work properly):
using (var db = new ProjetPooEntities2())
{
// taken from /a/31856991
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");
db.Users.Add(u);
db.SaveChanges();
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");
}
请注意,这里最底层的方法不是一个好的做法,因为它有可能允许在某些情况下插入重复的值。
Note that this bottom-most approach here considered not a good practice, since it is possible to allow insertion of duplicate values in certain cases.
相关问题:
不要在表'table'中插入标识列的显式值IDENTITY_INSERT设置为OFF
这篇关于System.Data.Entity.Infrastructure.DbUpdateException错误,即使我没有更新id列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!