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.

Protoactinium

Can you include the code where you create the object and add it to the DataContext? – Smokejumper

I second Portman, please post the code you are using for the insert, see my answer, it is likely the issue is in there – Quietude

Answer 1

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(); }

Quietude

Except the second definition has "IsPrimaryKey=true," added to it. – Photolysis

@Chris, I said the samed definition as the one he is using ;) – Quietude

@Freddy, pardon my ignorance, I'm tired from work, how are they the same? – Photolysis

my second one, with the one from tsilb: [Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] ... same options all around, it is likely the issue is on the insert code (added a comment to the question asking for it) – Quietude

The first solution did not work for me, but setting AutoSync to OnInsert did. Hooray! Thank you, sir (: – Gitlow

Answer 2

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

Whose

Exactly what it was! – Whiten

you made my day :) – Tswana

Thank you! Was banging my head against exactly this messing around in LinqPad. – Procora

Answer 3

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.

Assentation

Answer 4

If you are supplying the value of the IDENTITY column, you must add

SET IDENTITY_INSERT ON

before your SQL Statements, and

SET IDENTITY_INSERT OFF 

after to turn it off. That goes for ANY raw SQL. If you used LINQ to create an object like:

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...

Photolysis

he is not doing it on purpose, that would cause it to insert 0 ... then fail for duplicate inserts – Quietude

Answer 5

seems like your ID is being assigned somewhere (even if just defaulting to 0) - would you care for posting some of your LINQ code?

Putandtake

Answer 6

I was having the exact same problem.

My solution was to manually make the identity INT column into a nullable INT? column in the DBML designer, not in the actual table of the DB of course. Also set the DbType to NULL instead of NOT NULL. Like so:

[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 NULL rather than to 0.

Apace

Answer 7

my case, I forgot to update my dbml file after setting Id column to identity.

Jordan