Browse Tag

entity framework

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))
	{
		context.Connection.Open();
		int i = context.ExecuteStoreCommand("SET IDENTITY_INSERT TestDB.dbo.Person ON");
		Person p = new Person();
		p.PersonID = 4;
		p.Name = "Michael";
		context.People.AddObject(p);
		context.SaveChanges();
		i = context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Person OFF");
		scope.Complete();
	}
}

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;

Resource:
http://social.msdn.microsoft.com/Forums/eu/adodotnetentityframework/thread/48cfa132-8612-4353-b916-b29f32b4070e

http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/84867836-2a52-4c8d-aa3d-e2d5f1845e66