none
LINQ to SQL with IDENTITY_INSERT override set to ON RRS feed

  • Question

  • I have conversion data that I need to populate into a new DB table that uses an IDENTITY column for the PK as follows:

    SET IDENTITY_INSERT tablename ON
    INSERT statement
    SET IDENTITY_INSERT tablename OFF

    The original identity must be preseved as it is used for the customer id from the original customer and is related to many other tables that will also be converted. After the initial population, standard IDENTITY will be used as new customers and data are added.

    The conversion application does a LOT of scrubbing on the input data prior to adding it to the new customer DB and uses LINQ for the insert. Being new to LINQ, I am wondering if it is possible to execute the IDENTITY_INSERT commands within the LINQ InsertOnSubmit?

    IOW, I want to use LINQ to do something like:

    USE MyDB

    SET IDENTITY_INSERT customers ON

    INSERT INTO customers (customer_id, first_name)
         VALUES (157876, 'Billy'), (158782, 'Bobby')
     
    SET IDENTITY_INSERT customers OFF

    Where customer_id is an identity PK that needs to preserve original id's.

    I tried this using the following C# LINQ code snippet, but it does NOT work (although it throws no errors):

    customer cust = new customer();
    cust.customer_id = 157876;
    cust.first_name = “Billy”;

    ctx.ExecuteCommand(@"SET IDENTITY_INSERT customer ON");
    ctx.customers.InsertOnSubmit(cust);
    ctx.SubmitChanges();
    ctx.ExecuteCommand(@"SET IDENTITY_INSERT customer OFF");

    The customer record is inserted into the table with the default Identity and ignores the "157876" override.

     

     

    Wednesday, January 21, 2009 9:53 PM

All replies

  • Have you modified the DBML/attributes to ensure that the customer_id column is no longer server-generated so that LINQ to SQL knows to attempt to inser the value itself?

    [)amien
    Tuesday, February 3, 2009 8:40 AM
    Moderator
  • I tried changing the attributes using reflection. However, although I was able to find them at runtime, I couldn't figure out how to change them so that LINQ would write the identity column after I temprorarily turned this off.
    Wednesday, February 4, 2009 8:57 PM
  • Is there a reason why you didn't modify the DBML or attributes directly, instead of trying to do it at runtime? What Damien is referring to is the IsDbGenerated property on the ColumnAttribute that decorates your identity property. However, if you go the route of trying to set these values at runtime, you have to be sure you do it early enough before they have been read by LINQ to SQL. I haven't tried it and would recommend setting it at design-time if at all possible, but if you try to do it with reflection before you ever create any LINQ to SQL objects like the DataContext associated with the type, it might work.

    Thanks,

    Sarah


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 5, 2009 10:42 PM
    Moderator