Altering EF Core Schema (onDelete) not working, constriant issue-CodePudding

I have a DB migration that I created many migrations ago that I'm now running into problems with (schema was not correct).

The original migration created a SeriesRelation, which allows a Series to be linked to another Series (that is not itself) with some extra metadata. The goal was when I removed any series in the relationship, the underlying Series wouldn't delete. However, because of this oversight, I ended up not being able to delete any Series with a SeriesRelation as a FK constraint is being thrown, with a lack of Cascade.

To address this, I want to change the FK_SeriesRelation_Series_SeriesId to Cascade deletion so that a Series can delete the SeriesRelation row when deleted, but any relation being removed wont delete the source Series rows.

I have tried with a custom migration, but it doesn't take (checked via Rider's Database tool). Any ideas?

Custom Migration

migrationBuilder.DropForeignKey(
                name: "FK_SeriesRelation_Series_SeriesId",
                table: "SeriesRelation");
            migrationBuilder.AddForeignKey(
                name: "FK_SeriesRelation_Series_SeriesId",
                table: "SeriesRelation",
                column: "SeriesId",
                principalTable: "Series",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);

Original Migration

migrationBuilder.CreateTable(
                name: "SeriesRelation",
                columns: table => new
                {
                    Id = table.Column<int>(type: "INTEGER", nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    RelationKind = table.Column<int>(type: "INTEGER", nullable: false),
                    TargetSeriesId = table.Column<int>(type: "INTEGER", nullable: false),
                    SeriesId = table.Column<int>(type: "INTEGER", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_SeriesRelation", x => x.Id);
                    table.ForeignKey(
                        name: "FK_SeriesRelation_Series_SeriesId",
                        column: x => x.SeriesId,
                        principalTable: "Series",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_SeriesRelation_Series_TargetSeriesId",
                        column: x => x.TargetSeriesId,
                        principalTable: "Series",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_SeriesRelation_SeriesId",
                table: "SeriesRelation",
                column: "SeriesId");

            migrationBuilder.CreateIndex(
                name: "IX_SeriesRelation_TargetSeriesId",
                table: "SeriesRelation",
                column: "TargetSeriesId");

builder.Entity<SeriesRelation>()
                .HasOne(pt => pt.Series)
                .WithMany(p => p.Relations)
                .HasForeignKey(pt => pt.SeriesId)
                .OnDelete(DeleteBehavior.ClientCascade);

            builder.Entity<SeriesRelation>()
                .HasOne(pt => pt.TargetSeries)
                .WithMany(t => t.RelationOf)
                .HasForeignKey(pt => pt.TargetSeriesId);

In case it's relevant, the classes in question can be found here:

Series: https://github.com/Kareadita/Kavita/blob/develop/API/Entities/Series.cs

Original Migration: https://github.com/Kareadita/Kavita/blob/develop/API/Data/Migrations/20220421214448_SeriesRelations.cs

Edit: Fluent API:

builder.Entity<SeriesRelation>()
            .HasOne(pt => pt.Series)
            .WithMany(p => p.Relations)
            .HasForeignKey(pt => pt.SeriesId)
            .OnDelete(DeleteBehavior.ClientCascade);

        builder.Entity<SeriesRelation>()
            .HasOne(pt => pt.TargetSeries)
            .WithMany(t => t.RelationOf)
            .HasForeignKey(pt => pt.TargetSeriesId)
            .OnDelete(DeleteBehavior.Cascade); // I added this OnDelete, but it doesn't work

CodePudding user response：

I think I solved this, but not with EF. What I did was included in the JOIN to pull back the Relation, so when I performed the delete, the cascades took effect:

return await _context.Series
        .Include(s => s.Volumes)
        .Include(s => s.Metadata)
        .ThenInclude(m => m.CollectionTags)
        .Include(s => s.Relations)
        .Where(s => seriesIds.Contains(s.Id))
        .AsSplitQuery()
        .ToListAsync();

I also put this in my DataContext:

builder.Entity<SeriesRelation>()
            .HasOne(pt => pt.Series)
            .WithMany(p => p.Relations)
            .HasForeignKey(pt => pt.SeriesId)
            .OnDelete(DeleteBehavior.ClientCascade);

        builder.Entity<SeriesRelation>()
            .HasOne(pt => pt.TargetSeries)
            .WithMany(t => t.RelationOf)
            .HasForeignKey(pt => pt.TargetSeriesId)
            .OnDelete(DeleteBehavior.ClientCascade);

This ended up solving it for me. Thank you to those who commented and helped me find the end solution.