C# Linq to SQL：为什么我会收到 IDENTITY_INSERT 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/637121/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq to SQL: Why am I getting IDENTITY_INSERT errors?
提问by tsilb
I'm using Linq to SQL. I have a DataContext against which I am .SubmitChanges()'ing. There is an error inserting the identity field:
我正在使用 Linq to SQL。我有一个 DataContext，我正在针对它 .SubmitChanges()'ing。插入身份字段时出错：
Cannot insert explicit value for identity column in table 'Rigs' when IDENTITY_INSERT is set to OFF.
The only identity field is "ID", which has a value of 0. It's defined in the DBML as:
唯一的标识字段是“ID”，其值为 0。它在 DBML 中定义为：
[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
There are a few foreign keys, and I've verified they have values that jive with the foreign tables' content.
有一些外键，我已经验证它们具有与外部表的内容一致的值。
Why would I be getting this error?
为什么我会收到这个错误？
Edit: Here is the query:
编辑：这是查询：
exec sp_executesql N'INSERT INTO [dbo].[Rigs]([id], [Name], [RAM], [Usage], [MoreInfo], [datetime], [UID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
SELECT [t0].[id], [t0].[OSID], [t0].[Monitors]
FROM [dbo].[Rigs] AS [t0]
WHERE [t0].[id] = @p7',N'@p0 int,@p1 varchar(1),@p2 int,@p3 varchar(1),@p4 varchar(1),@p5 datetime,@p6 int,@p7
int',@p0=0,@p1='1',@p2=NULL,@p3='4',@p4='5',@p5=''2009-03-11 20:09:15:700'',@p6=1,@p7=0
Clearly it is passing a zero, despite having never been assigned a value.
显然它传递了一个零，尽管从未被赋值。
Edit: Adding Code:
编辑：添加代码：
Rig rig = new Rig();
int RigID;
try
{ // Confirmed these always contain a nonzero value or blank
RigID = int.Parse(lbSystems.SelectedValue ?? hfRigID.Value);
if (RigID > 0) rig = mo.utils.RigUtils.GetByID(RigID);
}
catch { }
rig.Name = Server.HtmlEncode(txtName.Text);
rig.OSID = int.Parse(ddlOS.SelectedValue);
rig.Monitors = int.Parse(txtMonitors.Text);
rig.Usage = Server.HtmlEncode(txtUsage.Text);
rig.MoreInfo = Server.HtmlEncode(txtMoreInfo.Text);
rig.RigsToVideoCards.Clear();
foreach (ListItem li in lbYourCards.Items)
{
RigsToVideoCard r2vc = new RigsToVideoCard();
r2vc.VCID = int.Parse(li.Value);
rig.RigsToVideoCards.Add(r2vc);
}
rig.UID = c_UID > 0 ? c_UID : mo.utils.UserUtils.GetUserByToken(this.Master.LiveToken).ID;
if (!mo.utils.RigUtils.Save(rig))
throw new ApplicationException("There was an error saving your Rig. I have been notified.");
hfRigID.Value = rig.id.ToString();
public static User GetUserByToken(string token)
{
DataClassesDataContext dc = new DataClassesDataContext(ConfigurationManager.ConnectionStrings["MultimonOnlineConnectionString"].ConnectionString);
return (from u in dc.Users
where u.LiveToken == token
select u).FirstOrDefault();
}
Also, I notice that when I UPDATE an existing rig (insertonsubmit), it doesn't update. Profiler doesn't even show any queries being run.
此外，我注意到当我更新现有装备 (insertonsubmit) 时，它不会更新。Profiler 甚至不显示任何正在运行的查询。
采纳答案by eglasius
Is your code setting the ID value explicitely to 0? (instead of leaving it untouched).
您的代码是否将 ID 值明确设置为 0？（而不是让它保持原样）。
Update 1:As you posted on the updated version, linq2sql is clearly passing the value to the db. Here is one I haven't had any trouble with:
更新 1：当您发布更新版本时，linq2sql 显然正在将值传递给数据库。这是我没有遇到任何问题的一个：
[Column(Storage="_CustomerID", AutoSync=AutoSync.Always, DbType="Int NOT NULL IDENTITY", IsDbGenerated=true)]
public int CustomerID
I just saw another one, and it has the same exact definition of the one you are using.
我刚刚看到了另一个，它与您正在使用的定义完全相同。
[Column(Storage="_TestID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int TestID
Update 2:Regarding updates, you are not supposed to do InsertOnSubmit for that. Just update the values and call .SubmitChanges (should be throwing an exception). On the insert it is really weird, as the property attributes you posted seems to be correct, so the Insert method linq2sql generates should be correct as well. I would try, re-adding the table on the designer again and verifying all the properties are correct.
更新 2：关于更新，您不应该为此执行 InsertOnSubmit。只需更新值并调用 .SubmitChanges （应该抛出异常）。在插入时它真的很奇怪，因为您发布的属性属性似乎是正确的，因此 linq2sql 生成的 Insert 方法也应该是正确的。我会尝试，再次在设计器上重新添加表格并验证所有属性是否正确。
Note that the generated insert method should look like (with a matchingRig_Insert):
请注意，生成的插入方法应如下所示（带有匹配的Rig_Insert）：
private void InsertRig(Rig obj)
{
System.Nullable<int> p1 = obj.Id;
this.Rig_Insert(/* bunch of values */, ref p1); obj.Id = p1.GetValueOrDefault(); }
this.Rig_Insert(/* 一堆值 */, ref p1); obj.Id = p1.GetValueOrDefault(); }
回答by Chris
If you are supplying the value of the IDENTITY column, you must add
如果您提供 IDENTITY 列的值，则必须添加
SET IDENTITY_INSERT ON
before your SQL Statements, and
在您的 SQL 语句之前，以及
SET IDENTITY_INSERT OFF
after to turn it off. That goes for ANY raw SQL. If you used LINQ to create an object like:
关掉之后。这适用于任何原始 SQL。如果您使用 LINQ 创建一个对象，如：
var customer = new LinqContext.Customer;
customer.FirstName = "Bob";
customer.LastName = "Smith";
LinqContent.Customer.Add(customer);
LinqContext.SubmitChanges();
I believe that will work, forgive me if I have classes wrong. You get the basic idea.
我相信这会奏效，如果我上课错了，请原谅我。你得到了基本的想法。
Edit: Oops.. Sorry, didn't read the entire question, I take it back, tired and fatigued from work...
编辑：哎呀..对不起，没有阅读整个问题，我收回了，工作累了，累了……
回答by Marek Karbarz
seems like your ID is being assigned somewhere (even if just defaulting to 0) - would you care for posting some of your LINQ code?
似乎您的 ID 被分配到某个地方（即使只是默认为 0）-您是否愿意发布一些 LINQ 代码？
回答by Josh
Ensure the property Auto Generated Value in your dbml for that column is set to true. If not, LINQ will attempt to default the value based on the type.
确保 dbml 中该列的属性 Auto Generated Value 设置为 true。如果不是，LINQ 将尝试根据类型默认值。
回答by Oscar Ortiz
My theory of what happened is as follows:
我对发生的事情的理论如下：
- You designed and created the DataBase
- You created the DB Context using LINQ TO SQL in visual Studio
- You forgot to set the auto-identity to your table
- You fixed that by going to your DataBase and setting the auto-identity
- But you forgot to recreate/update your LINQ TO SQL DB Context!!!
- 您设计并创建了数据库
- 您在 Visual Studio 中使用 LINQ TO SQL 创建了数据库上下文
- 您忘记将自动标识设置为您的表
- 您通过转到您的数据库并设置自动标识来解决这个问题
- 但是您忘记重新创建/更新您的 LINQ TO SQL 数据库上下文！！！
:D
:D
回答by Hugo Nava Kopp
I was having the exact same problem.
我遇到了完全相同的问题。
My solution was to manually make the identity INT column into a nullableINT? column in the DBML designer, not in the actual table of the DB of course. Also set the DbType to NULLinstead of NOT NULL. Like so:
我的解决方案是手动将身份 INT 列变成可以为空的INT？DBML 设计器中的列，当然不是在 DB 的实际表中。还设置了DbType，为NULL，而不是NOT NULL。像这样：
[Column(Storage="_TestID", AutoSync=AutoSync.OnInsert, DbType="Int NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int? TestID
This works with all DB operations. When inserting simply set the identity column to NULLrather than to 0.
这适用于所有数据库操作。插入时只需将标识列设置为 NULL而不是 0。
回答by donstack
my case, I forgot to update my dbml file after setting Id column to identity.
我的情况，我忘记在将 Id 列设置为身份后更新我的 dbml 文件。
