2

I am attempting to convert data from an old database into a new database, where the primary key's value is important.

The code I currently have is as such (actual table name replaced with "TableName"):

using (var oldContext = new OldContext())
using (var newContext = new NewContext())
using (var transaction = newContext.Database.BeginTransaction())
{
    foreach (var item in GetConversionItems())
    {
        var newItem = Convert(item);
        newContext.TableName.Add(newItem);
    }

    try
    {
        newContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[TableName] ON;");
        newContext.SaveChanges();
        newContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[TableName] OFF");
        transaction.Commit();
    }
    catch (Exception e)
    {
        transaction.Rollback();
    }
}

and I do explicitly assign a value to the primary key.

public static TableName Convert(OldItem oldItem)
{
    ...
    newItem.ID = oldItem.CONTROL;
    ...
}

But I get the exception:

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

I've tried making the actual database calls (IDENTITY_INSERT and SaveChanges()) inside the foreach loop, but the problem persisted.

The answers I was able to find for this issue were all for code-first.

CarenRose
  • 1,266
  • 1
  • 12
  • 24
  • 1
    I never solved this issue - I instead created another edmx in the data conversion project, rather than referencing my existing one, then manually edited the XML of the edmx to remove the Identity specification on the column. – CarenRose Sep 21 '18 at 19:47

0 Answers0