-1

On occasion, I need to insert a specific ID as the primary key of an entity I'm adding to my data. In classic MS SQL, you can do this by calling SET IDENTITY_INSERT tablename ON before calling the INSERT INTO statement. I cannot seem to figure out a way to do the same using EF 4.1 Code First.

Dim specificIdRecord As New Record() With {
    .RecordID = 25,
    'other settings here
}

myDbContext.Records.Add(specificIdRecord)
myDbContext.SaveChanges()

specificIdRecord.RecordID '<== this does not always equal 25!

There's also no error being thrown to tell me it's ignoring my RecordID and automatically generating one.

NOTE: I do not want to modify my model or modify my context as this is only intended for occasional insert operations. Also, I did see a possible solution using ExecuteStoreCommand to manually set the IDENTITY_INSERT before and after the insert runs, but that will require three transactions AFAIK, I want EF to wrap this all up into one.

Community
  • 1
  • 1
just.another.programmer
  • 8,579
  • 8
  • 51
  • 90

1 Answers1

1

EF doesn't support this. If you use IDENTITY columns in the database your mapping contains this information and you can never send a key value to the database (EF will automatically skip it).

The best option in this case is using ExecuteStoreCommand for both setting IDENTITY_INSERT and INSERT SQL commands.

Any workaround based on EF would require two different EF mappings: one with key set as database generated and one without this setting. You will use the first mapping for common processing and the second mapping when you want to insert explicit key values. Using two mappings means using two different EDMX files or two different DbModelBuilder instances (two context types).

Once you will have two mappings you will still have to solve the problem with turning IDENTITY_INSERT on and off. This will require you to create custom provider wrapper to attach those SQL commands to each generatedINSERT command.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • That's frustrating. Any idea why EF would choose not to support this? I assume it has something to do with some db providers not supporting it? – just.another.programmer Aug 07 '12 at 09:32
  • I think it is about the fact that this is too low level. If you want to use database low level commands you should do it through SQL not through ORM. – Ladislav Mrnka Aug 07 '12 at 10:19
  • @just.another.programmer features don't exist by default. They have a cost to design, develop, document and maintain. Since this is not the expected usage of identity fields (it's more of a hack) and workarounds exist, what would be strange is to have it supported. – Diego Mijelshon Aug 07 '12 at 16:59