Linq to SQL:为什么我会收到 IDENTITY_INSERT 错误? [英] Linq to SQL: Why am I getting IDENTITY_INSERT errors?

问题描述

我正在使用 Linq to SQL.我有一个 DataContext，我正在针对它 .SubmitChanges()'ing.插入身份字段时出错:

当 IDENTITY_INSERT 设置为 OFF 时，无法为表 'Rigs' 中的标识列插入显式值.

I'm using Linq to SQL. I have a DataContext against which I am .SubmitChanges()'ing. There is an error inserting the identity field: 
Cannot insert explicit value for identity column in table 'Rigs' when IDENTITY_INSERT is set to OFF.

唯一的标识字段是ID"，其值为 0.它在 DBML 中定义为:
The only identity field is "ID", which has a value of 0. It's defined in the DBML as:
[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?
这是查询:
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.
添加代码:
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();
}

另外，我注意到当我更新现有装备(insertonsubmit)时，它不会更新.Profiler 甚至不显示正在运行的任何查询.
Also, I notice that when I UPDATE an existing rig (insertonsubmit), it doesn't update.  Profiler doesn't even show any queries being run.
推荐答案
您的代码是否将 ID 值显式设置为 0?(而不是让它保持原样).
Is your code setting the ID value explicitely to 0? (instead of leaving it untouched). 
更新 1: 正如您在更新版本上发布的那样，linq2sql 显然正在将值传递给数据库.这是我没有遇到任何问题的一个:
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:
[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

更新 2:关于更新，您不应该为此执行 InsertOnSubmit.只需更新值并调用 .SubmitChanges (应该抛出异常).在插入时它真的很奇怪，因为您发布的属性属性似乎是正确的，因此 linq2sql 生成的 Insert 方法也应该是正确的.我会尝试，再次在设计器上重新添加表格并验证所有属性是否正确.
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.
注意，生成的插入方法应该看起来像(带有一个匹配的Rig_Insert):
Note that the generated insert method should look like (with a matchingRig_Insert):
private void InsertRig(Rig obj)
{
    System.Nullable<int> p1 = obj.Id;

this.Rig_Insert(/* 一堆值 */, ref p1);obj.Id = p1.GetValueOrDefault();}
this.Rig_Insert(/* bunch of values */, ref p1);
        obj.Id = p1.GetValueOrDefault();
    }

                        
这篇关于Linq to SQL:为什么我会收到 IDENTITY_INSERT 错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！