Cascading Referential Integrity Constraints for many to many relationships in EF Core

0

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

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:

  • When User deleted all devices and photos related to the user are also removed
  • When Device or Photo deleted all PhotoDevice records related to the deleted entity are removed too
c#
entity-framework
asp.net-core
entity-framework-core
many-to-many

2 Answers

1

What lauxjpn said is correct, but please note that when you add .OnDelete(DeleteBehavior.Restrict); to your context after that, please remember to delete all your previous migrations, otherwise it will still report an error.

answered on Stack Overflow Jul 22, 2020 by Yinqiu
0

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.

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

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:

answered on Stack Overflow Jul 21, 2020 by lauxjpn • edited Jul 21, 2020 by lauxjpn

User contributions licensed under CC BY-SA 3.0