Entity Framework C# - Moving data from one database to another with similar structure

Question

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!

You could just export the data in SQL Server to a .csv, convert it to an .xls and import it into the new db. Making changes to the schema in the .xls file shouldn't be a problem. — Drew Kennedy
– Drew Kennedy, Commented Aug 22, 2016 at 16:49
Can't you just do this in SQL Queries? This approach for transferring data can be a serious headache. You could face a lot of other issues (such as this one), which are all easier to deal with in SQL. I'd suggest that you write queries such as insert into from select ...etc. Also remember to worry about the referential integrity between the Primary & Foreign keys .. Good luck. — t_plusplus
– t_plusplus, Commented Aug 22, 2016 at 16:55
Please check that you're running DbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[AccessoryProfiles] ON"); against the receiving database context, rather than the sending one? — t_plusplus
– t_plusplus, Commented Aug 22, 2016 at 16:57
Is there a reason you are doing this in code, rather than using a tool like those from RedGate, XSQL, DevArt, etc.? These tools have methods built in them to address identity columns during data transfers. Is this a process you have to repeat? — Wesley Long
– Wesley Long, Commented Aug 22, 2016 at 17:00
The reason I'm doing this in code is that it's the only enviroment I feel comfortable in and that I can loop through the tables and make the changes dynamically. I'm terrible at SQL-queries and would like to avoid them, but to export the DB into .xls and making the changes there sounds like a good option, except that I have around 12k items in a specific table, and making those changes have to be made dynamically. I' take a look at excel features as I'm not well versed in that either. — Martin Johansson
– Martin Johansson, Commented Aug 22, 2016 at 17:18

Community · Accepted Answer · 2017-05-23 12:00:45Z

3

If you are inserting an entire table, I would recommend using EntityFramework.BulkInsert. For a lot of records, ".Add" or ".AddRange" extensions are pretty slow.

Here is the description and speed advantages of bulkinsert: https://efbulkinsert.codeplex.com/

You can also check out this thread for more info: Fastest Way of Inserting in Entity Framework

When setting up your SqlBulkCopy, it allows you to overload it with different SqlBulkCopyOptions. I believe one of them is "KeepIdentity" which allows you to keep the source identity. I think that might be what you're looking for.

So when you set up your sqlbulkcopy object you might write something like this:

SqlBulkCopy copy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity| SqlBulkCopyOptions.Default, null);

Also make sure you don't use the "CheckConstraints" option.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Aug 22, 2016 at 17:42

Lenny K

12814 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Johansson Over a year ago

Thanks. I'm trying to make this work, I found the KeepIdentity setting and I don't get the key insert error. But I get "Model backing the "DbContext" has changed..." I tried new migration, but had no changes pending. I've deleted the database and recreated it using code first, but I get this error. I still run the same method as above. the difference is that I run ´DbContext.BulkInsert(entities, new BulkInsertOptions { SqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity });´ instead of ´DbContext.Add(entity);´ in my DataService. Any ideas?

Martin Johansson Over a year ago

By the way, The error points to the DbContext of my new Database, not my old.

Martin Johansson Over a year ago

The error occured because when I installed BulkInsert it downgraded my Entity Framework to 6.0.0 for some reason. I updated EF and the error disappeared! And your solution worked smoothly, I got my whole table formatted and ready with correct Identity columns in my new DB. Thanks alot!

Andrii Fast · Accepted Answer · 2025-09-03 12:13:35Z

I needed to migrate from MsSql to Postgresql with Entity Framework C#. I wrote the code to transfer tables. This may not be the best solution but it works.


// I added this code to the ApplicationDbContext
private bool DbType { get; set; }
private string? ConnectStr { get; set; }
public ApplicationDbContext(bool dbTypeNpg, string connectionString, bool noTracking)
{
    DbType = dbTypeNpg;
    ConnectStr = connectionString;

    if (noTracking)
        ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        if (DbType == true)
            optionsBuilder.UseNpgsql(ConnectStr);
        else
            optionsBuilder.UseSqlServer(ConnectStr);
    }
}

/*-------------------------*/

// I separately added a class for data transfer
public static class SqlService
{
    public static void TransferDB()
    {
        try
        {
            TransferTables();
        }
        catch (Exception ex)
        { }
    }
    
    private static (ApplicationDbContext, ApplicationDbContext) GetTransferDBContext()
    {
        return (
            new ApplicationDbContext(false, "Data Source=;Initial Catalog=;User Id=;Password=;", true),
            new ApplicationDbContext(true, "Host=;Port=;Username=;Password=;Database=;", false)
        );
    }

    private static void TransferTables()
    {
        var db = GetTransferDBContext().Item1;
        var tables = db.Model.GetEntityTypes().Select(t => t.GetTableName().Replace("AspNet", "")).ToList();
        var completeTable = new string[] {nameof(db.Companies), nameof(db.Users), nameof(db.DataProtectionKeys) };
        tables = tables.Where(x => !completeTable.Contains(x)).ToList();
    
        var skip = 0; // can be used to skip tables that you have already processed
        var props = db.GetType().GetProperties().OrderBy(x => x.Name != "Users" && x.Name != "Roles").Skip(skip).ToList();
        foreach (var prop in props)
        {
            if (!tables.Contains(prop.Name))
                continue;
    
            var dbs = GetTransferDBContext();
    
            var _items1 = (IEnumerable<object>)prop.GetValue(dbs.Item1);
            var _items2 = prop.GetValue(dbs.Item2);
            var methodAdd = _items2.GetType().GetMethod("Add");
            var methodRemove = _items2.GetType().GetMethod("Remove");
    
            var total = _items1.Count();
            for (var i = 0; i < total; i++)
            {
                var item = _items1.Skip(i).FirstOrDefault()!;
                CheckProperty(item);
    
                try
                {
                    methodAdd.Invoke(_items2, [item]);
                    dbs.Item2.SaveChanges();
                }
                catch (Exception ex)
                {
                    if (ex.InnerException?.Message.Contains("duplicate key value violates unique constraint") == true)
                        methodRemove.Invoke(_items2, [item]);
                }
            }
        }
    }
    
    private static void CheckProperty(object obj)
    {
        if (obj == null)
            return;
    
        var props = obj.GetType().GetProperties();
        foreach (var prop in props)
        {
            if (prop.GetValue(obj) != null && (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?)))
            {
                var val = (DateTime)(prop.GetValue(obj) as DateTime?)!;
                val = DateTime.SpecifyKind(val, DateTimeKind.Utc);
                prop.SetValue(obj, val);
            }
        }
    }
}

Collectives™ on Stack Overflow

Entity Framework C# - Moving data from one database to another with similar structure

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related