c# - Linq to SQL: Why am I getting IDENTITY_INSERT errors?

Question

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.

Answer 1

My theory of what happened is as follows:

1. You designed and created the DataBase
2. You created the DB Context using LINQ TO SQL in visual Studio
3. You forgot to set the auto-identity to your table
4. You fixed that by going to your DataBase and setting the auto-identity
5. But you forgot to recreate/update your LINQ TO SQL DB Context!!!

:D