LINQ to SQL and auto increment fields

When using LINQ to SQL in Visual Studio 2005, Table and Column mapping has to be coded manually. This is not a very difficult job but it's repetitive and boring (of course, if you are using Visual Studio 2008 to automatically generated the mapping code, this will be done automatically).

I ran into an issue while following a guide on manually map database tables to classes. When I tried to insert a new record, leaving the identity field empty in the object, I had the following error:

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

It was pretty obvious that, as the identity column was and Int32, it was set to 0 as default, and when LINQ tried to insert that row in the table the SQL Server raised an error as this auto incremented field cannot be explicitly set.

This is simply solved by using the IsDbGenerated attribute in the Column annotation description of the field, as shown in the following code.

private Int32 _id;

[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public Int32 Id { get { return this._id; } set { this._id = value; } }

This attribute tells LINQ not to give a value for this field as it is generated by the database at insert time.

Another very nice thing about this is that once you have inserted the object in the database, this field will hold the value generated by the database. Practically, this means that after executing the following code

DataTable.InsertOnSubmit(obj);
DataContext.SubmitChanges();

the obj.Id property will contain the value that was assigned by the database.