ASP.NET MVC, Strongly-Typed Views, LINQ to SQL, and IDENTITY_INSERT

Given some of the recent discussions and debates over the value of ASP.NET Web Forms (with which I have deep experience) and ASP.NET MVC, I thought I would immerse myself more deeply into MVC. To that end, I’m doing some prototyping with the ASP.NET MVC 2 release that shipped with Visual Studio 2010 Beta 2.

Following along with the demos in the first installment of Rob Conery’s “Mastering ASP.NET MVC 2” video series on Tekpub.com, I created a simple database with a couple of tables, modeled the DB using LINQ to SQL, and created a Controller and some strongly-typed views for the Index, Details, Edit, and Create actions.

One issue that I ran into early on was that I was getting the following error when attempting to create a new record:

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

Whether I failed to follow precisely the steps in the demos, or whether there’s some other issue involved, I’m not sure, but the crux of the issue is that my database was set up with the ID columns as Identity values, which automatically provide a value for the column based on the increment you supply (usually 1). Meanwhile, my LINQ to SQL model was also set to provide values for these columns if no value was provided. So when I submitted a new record (I had already removed the ID markup from the Create view), and LINQ to SQL attempted to insert a value into the Identity column…BOOM…error.

The solution, in my case, was to update my LINQ to SQL model. For example, one of the model entities, Venue, looks like this:

I selected the VenueId property, which maps to the column of the same name in the DB, which has Is Identity set to True, and set the property’s Auto Generated Value property to True, as shown below:

This tells LINQ to SQL that I’ve already taken care of incrementing the Identity column, so my model doesn’t need to. Problem solved, now I can create records without the errors. Hopefully, this will help some folks if they run into this error when working with ASP.NET MVC and LINQ to SQL.