0

I am confused by this behavior in LINQ. When I try to insert a value into an IDENTIY column using SQL I get an error. But when I try in LINQ I do not get an error and the insert value is ignored.

For example:

Table_1 has 2 columns. The first 'ID' is an IDENTITY(1,1) and increments on insert. Column 2 'TextColumn' is a varchar(50). If I run the following query in SQL:

insert into Table_1 (id,TextColumn) values (1,'test')

I get this error as expected:

Cannot insert explicit value for identity column in table 'Table_1' when IDENTITY_INSERT is set to OFF.

But when I run this code in C# no error is thrown:

var db = new TestDBDataContext();
var table1 = new Table_1();
table1.ID = 1; // SHouldn't this cause an error?
table1.TextColumn = "Test Text to insert";
db.Table_1s.InsertOnSubmit(table1);
db.SubmitChanges();

The int value '1' in this case is just ignored and the ID column auto increments normally. Here is the entry in the designer.cs file for the ID column

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]

I am confused why LINQ would not set these values to "Read Only" by default? If I toggle the Read Only property of the ID column I get the expected behavior. The Set method dissapears in the designer.cs for the ID object and an error appears in the code. Thank you!

Dgahagan
  • 43
  • 1
  • 7
  • 3
    Do you have something like `IsDbGenerated` or similar attribute on ID column in your context? – Andrei Aug 07 '15 at 14:16
  • Yes that is set to TRUE. This was the default when I added the table using the server explorer. Is there a reason it still allows me to try to set the table1.ID? It seems like trying to set a value would throw an error as it does when you try to do the same using SQL. Is there a specific reason it it ignores the supplied value? It seems like it would make it easy to overlook this type of bug. – Dgahagan Aug 07 '15 at 19:23

2 Answers2

1

You're assuming that Linq2sql is just blindly building a SQL statement out of the values in table1. It's actually a bit more involved than that. For example, if you the table hd ten columns and you only assigned values to 5 of them, it would only include those 5 columns in the insert statement.

So, since it knows you cannot assign a value to ID in the database, it just ignores the fact that it did so in the C# code.

James Curran
  • 101,701
  • 37
  • 181
  • 258
  • This is true, the LINQ2sql is understanding that the ID column cannot be written to directly so the problem is not sent to the SQL server. But how can I know if I try to set an ID it wont be discarded? (Without checking each and every time that the ID is not set to autogenerate). It just seems like an odd behavior to me. I have to imagine that this was done purposely for a good reason. I am just trying to understand that reason. Thank you for your answer and help! – Dgahagan Aug 07 '15 at 19:42
1

Per this post, either your property is marked as Auto Generated Value in your entity definition, or it is marked as IsDBGenerated in xml as Andrei suggested in the comments.

https://stackoverflow.com/a/6139214/1729859

Community
  • 1
  • 1
mituw16
  • 5,126
  • 3
  • 23
  • 48