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.