LINQ到SQL：为什么会出现IDENTITY_INSERT错误？ [英] Linq to SQL: Why am I getting IDENTITY_INSERT errors?
问题描述
我使用LINQ to SQL。我有一个DataContext这对我.SubmitChanges（）'ING。有一个错误插入标识字段：
不能为表'钻机当IDENTITY_INSERT设置为OFF
$标识列插入显式值b
$ b
唯一标识字段是ID，其值0。它在DBML定义为：
[列（存储=_ ID，自动同步= AutoSync.OnInsert，的DbType =内部NOT NULL IDENTITY，IsPrimaryKey = TRUE，IsDbGenerated = TRUE）]
有几个外键，和我已经验证他们有与国外表的内容Jive的值。
为什么我会收到此错误
编辑：下面是该查询：
EXEC sp_executesql的N'INSERT INTO [DBO]。[钻机]（[ID]，[名]，[RAM]，[用法]，[MoreInfo]，[日期时间] [UID]）
值（@ P0，@ P1，@ P2，@ P3，@ P4，@ P5，@ P6）
选择[T0]。[ID]，[T0] [OSID ]，[T0]。[显示器]
从[DBO]。[钻机] AS [T0]
，其中[T 0]。[ID] = @ P7'，N@ P0 INT，@ P1 VARCHAR（1），@ p2的INT，@ P3 VARCHAR（1），@ P4 VARCHAR（1），@ P5日期时间，@ P6 INT，@ P7
诠释'，@ P0 = 0，@ P1 ='1 '，@ P2 = NULL，@ P3 ='4'，@ P4 ='5'，@ P5 =''2009-03-11 20：09：15：700'，@ P6 = 1，@ P7 = 0
显然，这是通过一个零，不在乎有没有被赋值。
编辑：添加代码：
钻机钻机=新钻机（）;
INT刚性;
试
{//证实了这些始终包含一个非零值或空
刚性= int.Parse（lbSystems.SelectedValue？hfRigID.Value）;
如果（刚性0）=钻机mo.utils.RigUtils.GetByID（刚性）;
}
赶上{}
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（在lbYourCards.Items列表项LI）
{
RigsToVideoCard r2vc =新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; （！mo.utils.RigUtils.Save（钻机））
如果
抛出新ApplicationException的（保存您的钻机一个错误我已经接到通知。）;
hfRigID.Value = rig.id.ToString（）;
公共静态用户GetUserByToken（字符串标记）
{
DataClassesDataContext DC =新DataClassesDataContext（ConfigurationManager.ConnectionStrings [MultimonOnlineConnectionString]的ConnectionString）; （在dc.Users
从u哪里u.LiveToken ==令牌
选择U）
返回.FirstOrDefault（）;
}
另外，我注意到，当我更新现有钻机（insertonsubmit），它不更新。探查器甚至不显示任何疑问正在运行。
是您的代码明确地设置ID值设置为0？ （而不是离开它不变）。
更新1：当你贴在更新版本，LINQ2SQL显然是值传递给数据库。这里有一个我还没有与任何麻烦：
[列（存储=_客户ID，自动同步= AutoSync.Always ，的DbType =内部NOT NULL IDENTITY，IsDbGenerated = TRUE）]
公众诠释客户ID
我只看到了一个又一个，它有你所使用的同样的确切定义。
[列（存储= _TestID，自动同步= AutoSync.OnInsert，的DbType =内部NOT NULL IDENTITY，IsPrimaryKey = TRUE，IsDbGenerated = TRUE）]
公众诠释TestID
更新2：关于更新，你不应该为那些InsertOnSubmit。刚刚更新的值，并调用.SubmitChanges（应抛出一个异常）。在插入真的很奇怪，因为属性的属性您发布似乎是正确的，所以Insert方法LINQ2SQL产生应该是正确的为好。我会尝试，再重新添加设计者表并验证所有属性都是正确的。
请注意，生成的插入方法应该看起来像（有matchingRig_Insert）
私人无效InsertRig（钻机OBJ）
{
System.Nullable< INT> P1 = obj.Id;
this.Rig_Insert（/ *一堆值* /，参照p1的）;
obj.Id = p1.GetValueOrDefault（）;
}
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.
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?
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.
Is your code setting the ID value explicitely to 0? (instead of leaving it untouched).
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
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.
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(/* bunch of values */, ref p1); obj.Id = p1.GetValueOrDefault(); }
这篇关于LINQ到SQL：为什么会出现IDENTITY_INSERT错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
