EF Core中多对多关系的级联引用完整性约束 [英] Cascading Referential Integrity Constraints for many to many relationships in EF Core

问题描述

我正在将EF Core 3.1.5与Asp.Net Core 3.1结合使用。当我尝试添加迁移时，出现下一个错误：

I'm using EF Core 3.1.5 with Asp.Net Core 3.1. When I'm trying to Add-Migration I get next error:

Microsoft.Data.SqlClient.SqlException（0x80131904）：引入了FOREIGN KEY约束'FK_PhotoDevice_Device_DeviceId'表 PhotoDevice上的内容可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_PhotoDevice_Device_DeviceId' on table 'PhotoDevice' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

我有下一个实体和配置：

I have next entities and configuration:

public class User {
   [Key]
   public long Id { get; set; }
   // Fields
   public IEnumerable<Device> Devices { get; set; }
   public IEnumerable<Photo> Photos { get; set; }
}

public class Photo {
   [Key]
   public long Id { get; set; }
   // Fields
   [ForeignKey("User")]
   public long UserRef { get; set; }
   public User User { get; set; }
   public IEnumerable<PhotoDevice> PhotoDevices { get; set; }
}

public class Device {
   [Key]
   public long Id { get; set; }
   // Fields
   [ForeignKey("User")]
   public long UserRef { get; set; }
   public User User { get; set; }
   public IEnumerable<PhotoDevice> PhotoDevices { get; set; }
}

public class PhotoDevice {
{
   public long? PhotoRef { get; set; }
   public Photo Photo { get; set; }

   public long? DeviceRef { get; set; }
   public Device Device { get; set; }
}

public class AppDbContext : DbContext
{
   public DbSet<User> Users { get; set; }
   public DbSet<Photo> Photos { get; set; }
   public DbSet<Device> Devices { get; set; }
   public DbSet<PhotoDevice> PhotoDevices { get; set; }
   public AppDbContext(DbContextOptions<AppDbContext> options)
   : base(options)
   {
      Database.Migrate();
   }

   protected override void OnModelCreating(ModelBuilder builder)
   {
      builder.Entity<Photo>()
         .HasOne(photo => photo.User)
         .WithMany(user => user.Photos);

      builder.Entity<Device>()
         .HasOne(device => device.User)
         .WithMany(user => user.Devices);

      builder.Entity<PhotoDevice>()
         .HasKey(bc => new { bc.PhotoRef, bc.DeviceRef });
      builder.Entity<PhotoDevice>()
         .HasOne(bc => bc.Photo)
         .WithMany(b => b.PhotoDevices)
         .HasForeignKey(bc => bc.PhotoRef);
      builder.Entity<PhotoDevice>()
         .HasOne(bc => bc.Device)
         .WithMany(c => c.PhotoDevices)
         .HasForeignKey(bc => bc.DeviceRef);
   }
}

我发现问题与级联连接的唯一性无关。但是现在我很困惑。因为我认为这种结构是必要的，所以我不想删除任何FK。我该如何解决该问题？如果需要下一个逻辑：

I found that problem is related to not uniqueness of cascading connections. But now I'm confused. Because I think that this structure is necessary and I don't want to drop any FK. How can I fix the problem If I need next logic:

• 用户删除时，与该用户相关的所有设备和照片也将被删除


• 当设备或照片删除时，与删除的实体相关的所有PhotoDevice记录也将被删除

推荐答案

您不需要删除任何外键。只需使用 OnDelete（DeleteBehavior）来明确指定所需的级联行为。

You don't need to drop any of your foreign keys. Just use OnDelete(DeleteBehavior) to explicitly specify what kind of cascading behavior you need.

例如，以下结果将导致模型创建成功，但是对于您的实际应用程序，您需要确定自己在哪里以及如何打破级联：

For example, the following would result in your model being created successfully, but for your real application, you need to decide yourself where and how to break the cascade:

builder.Entity<PhotoDevice>()
    .HasOne(bc => bc.Photo)
    .WithMany(b => b.PhotoDevices)
    .HasForeignKey(bc => bc.PhotoRef)
    .OnDelete(DeleteBehavior.Restrict); // <-- define cascading behavior

有关更多信息，请参见关系：级联删除和层叠删除。

For further information, see Relationships: Cascade delete and Cascade Delete.

这不是EF核心，而是 SQL Server限制（因此SQL Server也会抛出异常）。

This is not an EF Core, but a SQL Server limitation (therefore the exception is also thrown by SQL Server).

以下是解决此限制的更多资源，并显示了如何使用 INSTEAD OF 触发器解决（如果无法打破级联）您可以使用的一个选项：

Here are further resources, that deal with this limitation and show how to work around it, by using INSTEAD OF trigger, if breaking the cascade is not an option you can live with:

• 解决SQL Server多个级联路径问题wi触发器


• 避免循环/多个更新路径的首选设计

• Solving the SQL Server Multiple Cascade Path Issue with a Trigger
• Preferred design to avoid circular/multiple update paths

这篇关于EF Core中多对多关系的级联引用完整性约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！