Results 1 to 8 of 8

Thread: How to use Identity Insert using LINQ to SQL?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2009
    Posts
    258

    Question 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?

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2009
    Posts
    258

    Re: How to use Identity Insert using LINQ to SQL?

    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...

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How to use Identity Insert using LINQ to SQL?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2009
    Posts
    258

    Re: How to use Identity Insert using LINQ to SQL?

    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.

  5. #5
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: How to use Identity Insert using LINQ to SQL?

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

  6. #6
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: How to use Identity Insert using LINQ to SQL?

    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:
    1. Imports System.Transactions
    2.  
    3. Public Class Form1
    4.   Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    5.     Using myEntity As New NORTHWINDEntities
    6.       Using myScope As New TransactionScope(TransactionScopeOption.RequiresNew)
    7.         myEntity.Connection.Open()
    8.         myEntity.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Employees ON")
    9.  
    10.         Dim myEmployee As New Employee With {.EmployeeID = 630, .LastName = "Harper"}
    11.         myEntity.Employees.AddObject(myEmployee)
    12.         myEntity.SaveChanges()
    13.  
    14.         myEntity.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Employees OFF")
    15.         myScope.Complete()
    16.       End Using
    17.     End Using
    18.     MsgBox("Done!")
    19.   End Sub
    20. End Class

    Hope that helps.

    Cheers,

    Ian

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2009
    Posts
    258

    Re: How to use Identity Insert using LINQ to SQL?

    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.

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to use Identity Insert using LINQ to SQL?

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width