Linq to SQL: Why am I getting IDENTITY_INSERT errors?

Question

I am using Linq for SQL. I have a DataContext that I am against .SubmitChanges () 'ing. Error entering identifier field:

  You cannot insert an explicit value for the identity column in the Rigs table when IDENTITY_INSERT is set to OFF. 

The only identifier field is "ID", which has a value of 0. It is defined in DBML as:

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] 

There are several foreign keys, and I checked that they have values ​​that are jive with the contents of the external tables.

Why should I get this error?

Edit: Here is the request:

 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 

It is clear that it passes zero, even though it has 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(); } 

In addition, I notice that when I UPDATE an existing installation (insertonsubmit), it does not update. The profiler does not even launch any queries.

Answer 1

Is your code an explicitly set ID value of 0? (instead of leaving it untouched).

Update 1:. When you published the updated version, linq2sql explicitly passes the value to db. Here is one that I had no problems 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 exact same definition of what you are using.

 [Column(Storage="_TestID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] public int TestID 

Update 2: As for updates, you should not do InsertOnSubmit for this. Just update the values ​​and call .SubmitChanges (should be throwing an exception). On the insert, this is really strange, since the attributes of the properties you posted look correct, so the Insert linq2sql method must also be correct. I would try adding the table to the constructor again and checking that all the properties are correct.

Note that the generated insertion method should look (using matchRig_Insert):

 private void InsertRig(Rig obj) { System.Nullable<int> p1 = obj.Id; 

this.Rig_Insert (/ * set of values ​​* /, ref p1); obj.Id = p1.GetValueOrDefault (); }

Answer 2

My theory of what happened is as follows:

• You have created and created the database
• You have created a DB context using LINQ TO SQL in visual Studio
• You forgot to set auto-identification in your table.
• You fixed it by going to your database and setting an auto-ID
• But you forgot to recreate / update your LINQ to SQL DB Context !!!

: D

Answer 3

Make sure the Auto Generated Value property in your dbml for this column is set to true. If not, LINQ will try to set the value by type.

Answer 4

If you specify an IDENTITY column value, you must add

 SET IDENTITY_INSERT ON 

before your SQL statements and

 SET IDENTITY_INSERT OFF 

after shutting down. This applies to ANY raw SQL. If you used LINQ to create an object of type:

 var customer = new LinqContext.Customer; customer.FirstName = "Bob"; customer.LastName = "Smith"; LinqContent.Customer.Add(customer); LinqContext.SubmitChanges(); 

I believe this will work, forgive me if I have the wrong classes. You get the main idea.

Edit: Sorry ... Read, I haven’t read the whole question, I am taking it away, tired and tired of work ...

Answer 5

it looks like your identifier is being assigned somewhere (even if it defaults to 0) - do you care to post any of your LINQ code?

Answer 6

I had the same problem.

My solution was to manually make the column of the identifier INT into NULL INT? column in the DBML designer , but not in the actual database table. Also set DbType to NULL instead of NOT NULL. For example:

 [Column(Storage="_TestID", AutoSync=AutoSync.OnInsert, DbType="Int NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] public int? TestID 

This works with all database operations. When pasting, just set the identifier column to NULL , not 0.

Answer 7

In my case, I forgot to update my dbml file after setting the Identifier column.