I have two SQL Express databases, the first has alot of data in it, but we've build a new application that's going to run on a database that has similar table structures (added some columns, removed some, merged a few tables etc.). The client is the same and they want to use the old data in the new application.
So what I'm trying to do now is to move data (table by table) from the old DB, convert the entity (manually) from the old format to the new and then finally save that new entity in my new DB.
I'm using Entity Framework and the server is ASP.NET Web Api. I've created two seperate DbContext, one for the old DB and one for the new. I'm able to get my data from the old DB, transform it, but then I run into my issue, I need the Identity columns to stay the same, so that I can maintain the relationships that exists in the old DB, but when I try to save the entity, EF tells me that I can't insert value in the Identity Column when Identity_Insert is OFF.
This is my actual Error message:
Cannot insert explicit value for identity column in table 'AccessoryProfiles' when IDENTITY_INSERT is set to OFF.
Here's my controller method that tries to migrate the data:
public IHttpActionResult Get()
{
try
{
var manager = new MigrationManager<AccessoryProfile>();
var entities = manager.GetAll();
foreach (var profile in entities)
{
var newEntity = new Entities.Server.Accessories.AccessoryProfile
{
Id = profile.AccessoryProfileId,
Name = profile.Name,
CreatedDate = profile.CreatedDate,
LastModifiedDate = profile.LastModifiedDate,
DeletedDate = profile.DeletedDate
};
DataService.AccessoryProfile.Add(newEntity);
}
DataService.AccessoryProfile.Commit();
return Ok();
}
catch (Exception exc)
{
Logger.Error(exc);
return null;
}
}
MigrationManager<T>
handles the old DB while DataService
handles the new one.
I've tried setting [DatabaseGenerated(DatabaseGeneratedOption.None)] on the Id, but no luck. I've tried running this before my Commit():
DbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[AccessoryProfiles] ON");
...but I still can't insert a value in the Identity Column.
How am I supposed to do this with Entity Framework? Or if you know of a better way than using EF, I'm all ears. Thanks!