使用EF Core将整个数据库从PostgreSQL复制到MariaDB

ht4b089n  于 2022-11-08  发布在  PostgreSQL
关注(0)|答案(1)|浏览(208)

我需要将现有的生产 .NET Core Web API 从使用EF Core和PostgreSQL提供程序迁移到MariaDB提供程序。
更改实际提供程序的过程很简单:安装Pomelo.EntityFrameworkCore.MySql nuget包,在依赖注入中初始化DbContext时,将UseNpgsql(...)替换为UseMysql(...),然后boom!done。
问题是如何迁移现有数据
我遇到的最大问题是PostgreSQL和MariaDB之间的模式不是1:1匹配。例如,PostgreSQL支持字符串数组,而MariaDB不支持。所以我需要在EF Core中使用ValueConverter。这一切都很好,但它使两个数据库事件更难使用常规SQL脚本迁移。由于这些问题,我决定尝试使用EF Core迁移数据。但事实证明,这是一个野兽。

如何使用EF Core复制整个数据库(包括循环关系)?

下面是我的实体的一个示例:

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
}

原创创意

下面是我如何尝试迁移数据

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();

但我遇到了这个异常。据我所知,由于ID为 *07744349- 7a 0 e-4128-a878- 9a 30 e126 e5 f8 * 的用户是多个订单的创建者,而我从源代码AsNoTracking()中选择了它,所以我基本上是在尝试创建同一个用户两次,这就导致了问题。

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.

"也试过这个"
我还尝试使用本指南中的信息分离所有实体: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();

它仍然以以下错误结束:

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.

删除include语句会使错误消失,但我会丢失进程中的所有关系。基本上,所有订单都将被导入,但与它们的创建者、AssignedHandlers或状态没有关系。
"该怎么办"
任何有益的想法都将受到深深的赞赏!

ltqd579y

ltqd579y1#

对于这个问题,我找到了两个解决方案。

1.使用Entity Framework Extensions

使用Entity Framework Extensions的BulkInsert方法可以复制所有数据,包括相关实体,并且非常容易使用。我最终使用了这些参数:

  • InsertKeepIdentity = true-保留我现有的ID
  • IncludeGraph = true-包括相关实体

复制代码如下所示:

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.按照@SvyatoslavDanyliv在评论中的建议使用linq2db.EntityFrameworkCore

为了让这个功能正常工作,我需要在我的模型中添加外键ID。导航属性是不够的,正如GitHub中提到的。
编辑的实体:

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; }
}

复制

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());

相关问题