C# .NET Entity Framework: How to insert record into database table with auto-increment primary ID?

Updated: 9 Sep 2016

Just got a comment asking me to take this post down because it’s outdated. Well, this was written in 2012 so it’s really VERY old. And I no longer use C# .NET Entity Framework. I’m leaving this post here for people who’re still using the old framework. All comments are closed now. I’ll no longer answer question relating to this topic. Please read it with caution.


I want to insert some records into the database table but it is giving an error which says “INDENTITY_INSERT is set to OFF”, something like that.

To resolve this, there’re 3 steps:

First, in Visual Studio 2010 and click on the .edmx file.
Look for the relevant and right click on the identity column (ID in most cases) and choose Properties.
In the properties window, look for StoreGeneratedPattern and change the value to “None”.
Save the changes.

In some cases (in my case), changing the value in step one is not enough. We still need to manually edit the .edmx file.
In Visual Studio 2010, right click the .edmx file -> “Open With…” -> Choose “XML (Text) Editor” to open the .edmx file
Look for the relevant table and change StoreGeneratedPattern=”Identity” to StoreGeneratedPattern=”None” for the identity column.
Save the changes.

Once the EF settings are done, we can set the primary key at the EF side.
However, SQL Server does not allow the Identity column to be set unless IDENTITY_INSERT is ON. (http://msdn.microsoft.com/en-us/library/ms188059.aspx).
So we need to manually execute SQL command to turn on the IDENTITY_INSERT first before we insert new records.

using (TestDBEntities1 context = new TestDBEntities1())
	using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
		int i = context.ExecuteStoreCommand("SET IDENTITY_INSERT TestDB.dbo.Person ON");
		Person p = new Person();
		p.PersonID = 4;
		p.Name = "Michael";
		i = context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Person OFF");

This is a workaround to let us manually set the identity column.
Warning: when IDENTITY_INSERT is set to ON, we need to manually assign the identity key (ID in most cases). You must ensure that it is unique and not null or empty. If you want to get the next available ID, you may add this:

Person p = context.Person.OrderByDescending( c => c.PersonID).FirstOrDefault();
int newId = (null == p ? 0 : p.Id) + 1;




  • mahesh

    November 19, 2013
  • nirav

    October 30, 2014


    I have tried the same steps which mentioned here , still i am getting error(“Cannot insert explicit value for identity column in table ‘Dealer’ when IDENTITY_INSERT is set to OFF.”) while inserting record.


  • David Logan

    April 14, 2015

    Is there a way to do this without rebuilding the project? Seems to me there should be a way to set the StoreGeneratedPattern in code so that I don’t have to have two sets of libraries to do this.

Comments are closed.