12

I am trying to create a record in the db that has a predefined primary key value. I know how to do this with sql, but I was wondering if EF can do this for me? Otherwise, I will have to create a stored proc for the inserts.

ajma
  • 12,106
  • 12
  • 71
  • 90
Paul Knopf
  • 9,568
  • 23
  • 77
  • 142

1 Answers1

12

In case you have the StoreGeneratedPattern attribute set to "None" for your entity, the Entity Key value you specify for it will be passed to database.

In case this attribute is set either to "Identity" or to "Computed" there is no way to control what value will be set to this column in DB.

So, if you have an auto-incremented column in your database and the ADO.NET Entity Data Model wizard has set the StoreGeneratedPattern for you, you can manually change this attribute to "None" in the SSDL part of the model using XML Editor, and then enable IDENTITY_INSERT.

You can use the ObjectContext.ExecuteStoreCommand method if you are using EF4 or use the ObjectContext.Connection.StoreConnection property to execute a SQL command SET IDENTITY_INSERT <Your_Table> ON (both in EF1 and EF v4).

Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
Devart
  • 119,203
  • 23
  • 166
  • 186
  • This seems ok but the problem comes when we try to add multiple related objects and we are unable to set identity_insert on multiple tables. – Akash Kava Sep 29 '11 at 11:17
  • 4
    In the latest EF4.3 we are using the DbContext which is very similar to the predecessor ObjectContext. The syntax changes slightly, my line of code to turn on identity inserts looks like this: `CurrentDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [MyDataTable] ON", new object[] { });` – BenSwayne Jul 30 '12 at 19:17
  • Do you have experience doing this? Because I can't get it to work. See this issue - http://stackoverflow.com/questions/1977845/i-set-identity-insert-to-on-but-i-get-a-sqlexception-saying-its-off?rq=1 – marknuzz Mar 21 '13 at 21:39
  • 1
    How did this get 11 upvotes? It doesn't even work. See other related questions. – marknuzz Mar 22 '13 at 18:49
  • 1
    The above solution is not complete. You have to open the context's connection before you ExecuteStoreCommand to SET IDENTITY_INSERT, then close it after you're done inserting. That keeps the session open. – pbristow Jul 05 '13 at 23:32