How to use Identity Insert using LINQ to SQL?

Okay, I have the following situation. I've got a table with a system generated key, but I have a need to insert a record and supply the key instead of letting the system generate it. If I were doing this in a .sql script, I'd simply set the insert identity on, do the insert, and then turn it off. Like this:

Code:

---

SET IDENTITY_INSERT dbo.Client ON
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (782, 'Edgewood Solutions')
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (783, 'Microsoft')
SET IDENTITY_INSERT dbo.Client OFF

---

A normal insert into this client table, as I'm doing the code now, looks like this (please forgive any typos, I'm generating this manually):

Code:

---

Dim ClientEntities As New ClientEntities()
Dim clientDB As New Client()
clientDB.ClientName= "Edgewood Solutions"
ClientEntities.Client.Add(clientDB);

---

The system automatically generates the ClientID value. What I WANT to do is provide the ClientID value using code like this:

Code:

---

Dim ClientEntities As New ClientEntities()
Dim clientDB As New Client()
clientDB.ClientName = "Edgewood Solutions"
clientDB.ClientID = 782
ClientEntities.Client.Add(clientDB);

---

However, as the database is set, this code will produce an error... I can't provide a value for the ClientID field. So, how do I do the equivalent of "SET IDENTITY_INSERT dbo.Client ON" in this code?

I tried calling ClientEntities.Database.ExecuteSqlCommand with the "SET IDENTITY_INSERT Client ON" command, but this did not work. Even if I provide a ClientID, it is ignored and the DB auto-generates a value. One promising method eliminated...

If the ClientIDs were going to be generated by code/client, rather than by the database, it shouldn't have been an Identity field.

There is another way... don't know if it's any better or worse, but either way, it's going to require some changes to your approach and architecture....

Leave the Identity field in place... add a new field, type GUID (uniqueidentifier) ... then in your code, instead of generating the int Identity value, just create a GUID and associate it with your row, when you insert it, you can then use the GUID to get the ID back...


Clunky... but it's one of a couple options I can think of (the other is to make ID a number, non-identity).

-tg

The identity field is used most of the time, and has been for years. Works fine, and there is existing software that makes use of it... adding another psuedo-ID column would require changing those programs to accommodate it. Not something I want to do.

Inserting a specific ID into an identity field is something that can be done in a SQL script, and I can even do it with SqlCommands in .NET. (In both cases, "SET IDENTITY_INSERT dbo.Client ON" and then do the insert with ID included.) I'm just hoping there's a way to do it in entity framework... there certainly should be.

Just out of curiosity why do you need to the specific id?

Hi,

I was intrigued with this question so I have spent a bit of time this morning to see if this could be done and you will be glad to hear that it can.

After a bit of trawling about on the net, piecing together bits and pieces, I knew I was on the brink of a solution but the bit I was missing was peoples references to "editing a property in the EF Table in question" to set a property called StoreGeneratedPattern = "None". This stumped me for a while until I came across THIS post from Andrews Blog. In this blog he mentions that the file that needed to be edited was the EF edmx file using an XML Text Editor. Doh! Anyway you can do this by following his advice or you can follow the below:-

1) Open your Entity Framework model, select the table you need to work with, select the Primary Key Column and in properties set StoreGeneratedPattern to None.

2) Close the EF Model in your project and right click it in the Solution Explorer and select Open With and select XML (Text) Editor.

3) Now search for your Primary Key field in the Table in question and, once again, set the Attribute StoreGeneratedPattern="None" <- That's the key to making this work.

4) Next, add a Reference to System.Transactions in your project.

5)Finally, the last thing to do is encompass all the required code to set your Primary Key manually within a Transaction Block. Have a look at this complete example:-

vb.net Code:

Imports System.Transactions
 
Public Class Form1
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Using myEntity As New NORTHWINDEntities
      Using myScope As New TransactionScope(TransactionScopeOption.RequiresNew)
        myEntity.Connection.Open()
        myEntity.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Employees ON")
 
        Dim myEmployee As New Employee With {.EmployeeID = 630, .LastName = "Harper"}
        myEntity.Employees.AddObject(myEmployee)
        myEntity.SaveChanges()
 
        myEntity.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Employees OFF")
        myScope.Complete()
      End Using
    End Using
    MsgBox("Done!")
  End Sub
End Class

Hope that helps.

Cheers,

Ian

Ian, I found that post, too, but it is insufficient to my need. MOST inserts done to my table will allow the system to generate the identity value... only a small number will need to insert specific values for the ID field. The solution you linked, changing the model in the project to move from "Identity" to "None" for that field seems to be a 100% change... doing so changes it so that an identity value for that column is NEVER generated. When I tested the code both ways after changing the StoreGeneratedPattern to None, it wouldn't auto-generate a key value on an insert... database error about missing value for the field.

I need the ability to provide a specific value for the identity field THIS TIME, while leaving the auto-generating ability intact for other transactions.

So...

Why not create a new table that has the same structure as the existing table - but not identity - a staging table.

Add to this table with whatever LINQ'ness you need.

Then run a quick SQL to Insert into RealTable Select * From StagingTable