none
How can I execute the IDENTITY_INSERT command to force an override of default Identity PK handling? 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.

    OTOH, this code DOES work:

    string cmd = 
       @"DELETE FROM customer " +
       @"SET IDENTITY_INSERT customer ON " +
       @"INSERT INTO customer (customer_uid, first_name) VALUES (123456, 'Billy'), (234567, 'Bobby') " +
       @"SET IDENTITY_INSERT customer OFF";

       ctx.ExecuteCommand(cmd);


    Wednesday, January 21, 2009 10:06 PM

All replies

  • Yes and no. You can issue the "set identity insert" against an explicitly created db connection using the datacontext's executecommand, insert and then turn it off for your connection.

    However, if your column is marked as "auto generated" in the model L2S will not attempt to insert it into the table with a value.

    So to make it work you would need to:
    a) have a separate data class for use with identity insert (with auto generated off)
    or
    b) change that attribute at runtime (reflection)
    or
    c) generate a new data class for use with identity insert at runtime

    Assuming this is a one-off data load exercise, the easiest solution is your last example.
    Thursday, January 22, 2009 2:43 AM
    Answerer
  • Ahhh. that explains it.

    It is, unfortunatly, a one-off PER CUSTOMER... with a potential of 20,000 plus customer sites worldwide needing conversion.
    Thursday, January 22, 2009 4:40 PM
  • KristoferA said:

    b) change that attribute at runtime (reflection)

    The designer generated the following for the IDENTITY column:

         [Column(Storage = "_customer_uid", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
         public int customer_uid

    The following code snippet returned "None" for the property attribute:

        Type t = typeof(t_customer);
        PropertyInfo p = t.GetProperty("customer_uid");
        Console.WriteLine("Property Name: {0}  Property Attributes: {1}", p.Name, p.Attributes);

    So... how would I change this at runtime using reflection?

    Friday, January 23, 2009 3:51 PM