Copy an entire database from PostgreSQL to MariaDB with EF Core

Question

I need to migrate an existing production .NET Core Web API from using EF Core with the PostgreSQL provider to the MariaDB provider.

The proccess of changing the actual provider simple: install the Pomelo.EntityFrameworkCore.MySql nuget package, when initializing the DbContext in dependency injection, replace the UseNpgsql(...) with UseMysql(...) and boom! done.

The problem is how to migrate the existing data?

The biggest issue I've ran into is that the schema isn't a 1:1 match between PostgreSQL and MariaDB. For example, while PostgreSQL supports string arrays, MariaDB doesn't. So I need to use a ValueConverter in EF Core. That's all good, but it makes the two databases event harder to migrate using regular SQL scripts. Due to these issues, I've decided to try migrating the data using EF Core. However, that proved to be a beast of its own.

How to copy the entire database (including circular relationships) with EF Core?

Here's an example of what my entities look like:

public class User
{
    [Key]
    public Guid UserId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public List<string> Claims { get; set; } = new List<string>();

    public User Employer { get; set; }

    public List<User> Employees { get; set; } = new List<User>();
}

public class Order
{
    [Key]
    public Guid OrderId { get; set; }
    public DateTime Created { get; set; }

    public User Creator { get; set; }
    public User AssignedHandler { get; set; }
    public List<OrderState> States { get; set; } = new List<OrderState>();
}

public class OrderState
{
    [Key]
    public Guid OrderStateId { get; set; }

    public User CreatedBy { get; set; }

    public DateTime Timestamp { get; set; }
    public OrderStatus Status { get; set; }
    public string Message { get; set; }
}

[JsonConverter(typeof(JsonStringEnumConverter))]
public enum OrderStatus
{
    Created = 1,
    Processing = 2,
    Canceled = 3,
    Completed = 4
}

Original idea

Here's how I'm trying to migrate the data

PostgreSqlDbConext _sourceDb; //old context, connects to existing db
MySqlSqlDbConext _targetDb; //new context, connects to an empty new db

var data = _sourceDb.Orders.AsNoTracking()
    .Include(a => a.Creator)
    .Include(a => a.AssignedHandler)
    .Include(a => a.States)
    .ToList();

_targetDb.Orders.AddRange(data);
_targetDb.SaveChanges();

But I get this exception. From what I understand, since the User with ID 07744349-7a0e-4128-a878-9a30e126e5f8 is a creator of multiple orders and I'm selecting it from the source with AsNoTracking(), I'm basically trying to create the same user twice and that causes the problem.

System.InvalidOperationException: The instance of entity type 'User' cannot be tracked because another instance with the key value '{UserId: 07744349-7a0e-4128-a878-9a30e126e5f8}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.

Also tried this

I've also tried detaching all the entities using information from this guide: Cloning the Entity object and all related children using the Entity Framework

var data = _sourceDb.Orders.AsNoTracking()
    .Include(a => a.Creator)
    .Include(a => a.AssignedHandler)
    .Include(a => a.States)
    .ToList();

foreach (var item in data)
{
    var cloned = item.Clone();
    cloned.ClearEntityReference(false);
    _targetDb.Entry(cloned).State = EntityState.Detached;
    _targetDb.Orders.Add(cloned);
}

_targetDb.SaveChanges();

Which still ends with the following error:

The instance of entity type 'User' cannot be tracked because another instance with the key value '{UserId: b824fe09-e80d-4f16-a620-e72592f1a1ad}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.

Removing the include statements makes the error go away, but I lose all the relationships in the proccess. Basically, all orders will be imported, but with no relation to their Creators, AssignedHandlers or States.

What to do?

Any helpful ideas are appreciated deeply!

Answer 1

I found two solutions for this issue.

1. Use Entity Framework Extensions

Using the Entity Framework Extensions' BulkInsert method copies all the data, including the related entities and is very easy to use. I've ended up using these parameters with it:

• InsertKeepIdentity = true - to keep my existing IDs
• IncludeGraph = true - to include related entities

Here's what the copy code looks like:

using Z.EntityFramework.Extensions;

var items = _sourceDb.Orders
    .Include(a => a.Creator)
    .Include(a => a.AssignedHandler)
    .Include(a => a.States);

_targetDb.BulkInsert(items, o =>
{
    o.InsertKeepIdentity = true;
    o.IncludeGraph = true;
});

2. Use linq2db.EntityFrameworkCore as suggested by @SvyatoslavDanyliv in the comments.

For this to work, I needed to add the foreign key IDs to my models. Navigation properties weren't enough, as mentioned in this GitHub issue.

Edited entities:

public class User
{
    [Key]
    public Guid UserId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public List<string> Claims { get; set; } = new List<string>();

    public Guid EmployerId { get; set; }
    public User Employer { get; set; }

    public List<User> Employees { get; set; } = new List<User>();
}

public class Order
{
    [Key]
    public Guid OrderId { get; set; }
    public DateTime Created { get; set; }

    public Guid CreatorId { get; set; }
    public User Creator { get; set; }

    public Guid AssignedHandlerId { get; set; }
    public User AssignedHandler { get; set; }

    public List<OrderState> States { get; set; } = new List<OrderState>();
}

public class OrderState
{
    [Key]
    public Guid OrderStateId { get; set; }

    public Guid CreatedById { get; set; }
    public User CreatedBy { get; set; }

    public DateTime Timestamp { get; set; }
    public OrderStatus Status { get; set; }
    public string Message { get; set; }
}

Copy

using LinqToDB.EntityFrameworkCore;
using LinqToDB.Data;

var options = new BulkCopyOptions { KeepIdentity = true };

_targetDb.BulkCopy(options, _sourceDb.Users.AsEnumerable());
_targetDb.BulkCopy(options, _sourceDb.Orders.AsEnumerable());
_targetDb.BulkCopy(options, _sourceDb.OrderStates.AsEnumerable());