EF核心/ Sqlite一对多关系由于唯一索引约束而失败 [英] EF Core / Sqlite one-to-many relationship failing on Unique Index Constraint

问题描述

上下文是每个 Car 有一个对应的 CarBrand 。现在我的班级如下所示：

The context is each Car has a corresponding CarBrand. Now my classes are as shown below:

public class Car
{
    public int CarId { get; set; }
    public int CarBrandId { get; set; }
    public CarBrand CarBrand { get; set; }
}

public class CarBrand
{
    public int CarBrandId { get; set; }
    public string Name { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Car> Cars { get; set; }
    public DbSet<CarBrand> CarBrands { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(@"Data Source = MyDatabase.sqlite");
    }
}

这是我的代码的示例执行... / p>

Here's a sample execution of my code...

class Program
{
    static void Main(string[] args)
    {
        AlwaysCreateNewDatabase();

        //1st transaction
        using (var context = new MyContext())
        {
            var honda = new CarBrand() { Name = "Honda" };
            var car1 = new Car() { CarBrand = honda };
            context.Cars.Add(car1);
            context.SaveChanges();
        }

        //2nd transaction
        using (var context = new MyContext())
        {
            var honda = GetCarBrand(1);
            var car2 = new Car() { CarBrand = honda };
            context.Cars.Add(car2);
            context.SaveChanges(); // exception happens here...
        }
    }

    static void AlwaysCreateNewDatabase()
    {
        using (var context = new MyContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }
    }

    static CarBrand GetCarBrand(int Id)
    {
        using (var context = new MyContext())
        {
            return context.CarBrands.Find(Id);
        }
    }
}

问题是我得到唯一约束失败：将 car2 与相同的 CarBrand 异常c $ c> 本田。

The problem is I get 'UNIQUE constraint failed: CarBrands.CarBrandId' exception when car2 is being added to the database with the same CarBrand honda.

我希望它在第二次交易期间执行context.SaveChanges（），它将添加 car2 并设置其与 CarBrand 的关系

What I expect it to do is during 2nd transaction's context.SaveChanges(), it will add car2 and set it's relationship with CarBrand appropriately but I get an exception instead.

编辑：我真的需要在不同的上下文/事务中获取我的CarBrand实例。

I really need to get my CarBrand instance in a different context/transaction.

        //really need to get CarBrand instance from different context/transaction
        CarBrand hondaDb = null;
        using (var context = new MyContext())
        {
            hondaDb = context.CarBrands.First(x => x.Name == "Honda");
        }

        //2nd transaction
        using (var context = new MyContext())
        {
            var car2 = new Car() { CarBrand = hondaDb };
            context.Cars.Add(car2);
            context.SaveChanges(); // exception happens here...
        }

推荐答案

问题是 Add 方法级联：

The problem is that Add method cascades:

开始跟踪给定实体， 已添加状态下尚未跟踪的任何其他可达实体，以便在 SaveChanges <时将其插入数据库中/ code>被调用。

Begins tracking the given entity, and any other reachable entities that are not already being tracked, in the Added state such that they will be inserted into the database when SaveChanges is called.

有很多方法可以实现目标，但是最灵活（我想首选）是将 Add 方法调用替换为 ChangeTracker.TrackGraph 方法：

There are many ways to achieve the goal, but the most flexible (and I guess the preferred) is to replace the Add method call with the ChangeTracker.TrackGraph method:

开始通过遍历其导航属性来跟踪一个实体以及任何可到达的实体。遍历是递归的，因此任何发现的实体的导航属性也将被扫描。为每个发现的实体调用指定的回调，并且必须设置应跟踪每个实体的状态。如果未设置状态，则该实体将保持未跟踪状态。
此方法设计用于断开连接的场景，其中使用上下文的一个实例检索实体，然后使用上下文的另一个实例保存更改。服务，其中一个服务调用从数据库中检索实体，而另一个服务调用保留对实体的任何更改。每个服务调用都使用一个新的上下文实例，该实例在调用完成后就被释放。
如果发现一个已经由上下文跟踪的实体，则不会处理该实体（并且不会遍历其导航属性）。

Begins tracking an entity and any entities that are reachable by traversing it's navigation properties. Traversal is recursive so the navigation properties of any discovered entities will also be scanned. The specified callback is called for each discovered entity and must set the State that each entity should be tracked in. If no state is set, the entity remains untracked. This method is designed for use in disconnected scenarios where entities are retrieved using one instance of the context and then changes are saved using a different instance of the context. An example of this is a web service where one service call retrieves entities from the database and another service call persists any changes to the entities. Each service call uses a new instance of the context that is disposed when the call is complete. If an entity is discovered that is already tracked by the context, that entity is not processed (and it's navigation properties are not traversed).

因此，代替 context.Cars.Add（car2）; 使用以下命令（它很通用，应该在几乎所有情况下都可以使用）：

So instead of context.Cars.Add(car2); you could use the following (it's pretty generic and should work in almost all scenarios):

context.ChangeTracker.TrackGraph(car2, node =>
    node.Entry.State = !node.Entry.IsKeySet ? EntityState.Added : EntityState.Unchanged);

这篇关于EF核心/ Sqlite一对多关系由于唯一索引约束而失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！