Freigeben über

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

  • Artikel

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:

image

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:

image

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.

Comments

  • Anonymous
    February 08, 2010
    Hi there, You faced this problem probably because the column was not the identity column at the time when you created the mapping otherwise Linq to SQL mapping takes care of this for you. Regards, Imran Rashid.

  • Anonymous
    February 08, 2010
    Hi Imran...thanks for the comment! I think the column was an identity value when I created the .dbml, but it's possible there was something else I was tweaking that caused the problem. The main thing I wanted to make sure was that I communicate the solution to the particular error message I encountered, regardless of how one gets there.