none
Has anyone set identity_insert with LINQ? RRS feed

  • Question

  • I have an integer primary key that is an identity. I need to turn off the identity_insert in order to do a data migration.

    I have seen a bunch of posts on this, mostly old... I have changed the Auto Generated property in the designer and taken the IDENTITY off the Server Data Type. I have edited the IsDbGenerated and DbType in the attributes in the designer code file. I have tried every combination of these choices.

    I still get an exception saying the the identity_insert is off. Microsoft does not seem to be answering anyone's post on this subject. If this is not possible, I can rule it out. Am I pursuing a dead end? Has anyone actually been able to do this?

    Any help would be appreciated.

    Thanks.

    David
    Friday, January 8, 2010 3:39 PM

Answers

  • Hi David,

    Telling LINQ to SQL that the column isn't identity is only half of the solution. Although LINQ to SQL will now try to insert values into the identity column, unless IDENTITY_INSERT is actually ON for the table in question in SQL Server, then SQL Server will send an error back to LINQ to SQL similar to what you are seeing. Here is a code snippet that will set identity_insert on for a table so you can do data migrations.

    using (var context = new NorthwindDataContext())
    {
        try
        {
            var command = context.Connection.CreateCommand();
            command.CommandText = "SET IDENTITY_INSERT Orders ON";
            command.ExecuteNonQuery();

            // Do data migration for Orders here, now that IDENTITY_INSERT is on.
        }
        finally
        {
            var command = context.Connection.CreateCommand();
            command.CommandText = "SET IDENTITY_INSERT Orders OFF";
            command.ExecuteNonQuery();
        }
    }

    Hope this helps
    David

    • Marked as answer by david2929 Friday, January 8, 2010 9:45 PM
    Friday, January 8, 2010 9:04 PM
    Answerer

All replies

  • Hi David,

    Telling LINQ to SQL that the column isn't identity is only half of the solution. Although LINQ to SQL will now try to insert values into the identity column, unless IDENTITY_INSERT is actually ON for the table in question in SQL Server, then SQL Server will send an error back to LINQ to SQL similar to what you are seeing. Here is a code snippet that will set identity_insert on for a table so you can do data migrations.

    using (var context = new NorthwindDataContext())
    {
        try
        {
            var command = context.Connection.CreateCommand();
            command.CommandText = "SET IDENTITY_INSERT Orders ON";
            command.ExecuteNonQuery();

            // Do data migration for Orders here, now that IDENTITY_INSERT is on.
        }
        finally
        {
            var command = context.Connection.CreateCommand();
            command.CommandText = "SET IDENTITY_INSERT Orders OFF";
            command.ExecuteNonQuery();
        }
    }

    Hope this helps
    David

    • Marked as answer by david2929 Friday, January 8, 2010 9:45 PM
    Friday, January 8, 2010 9:04 PM
    Answerer
  • David,

    This did work! (needed to open the connection)

    I tried context.ExecuteCommand() to do the set, but that opened and closed the connection and had no effect. Your method is not very obvious. I would have thought that there would be a more direct to do such things.

    Your response was a great help. I had given up and written the thing as a stored procedure.

    Thank you.

    David
    Friday, January 8, 2010 9:54 PM
  • David2929,

     

    I'm trying to do some data migration, using LINQ, just like you did, but I can't get it to work. I am setting the ID column in my code, but I get this error when trying:

     

    "Explicit value must be specified for identity column in table 'Debtor' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

     

    How did you set the Identity column in your code?

     

    Thanks! Hope to hear from you soon!


    -brenda-

    http://www.thetechiegeekmom.com

    Friday, January 21, 2011 9:09 PM