Entity Framework Fluent API - Cascade Delete - With Interface

Question

Entity Framework Fluent API - Cascade Delete - With Interface

I've got the following relationships defined in my data model / Entity Framework-driven (EFCore) database:

A Person.cs which implements the IRecord interface. A Person has a List of ExternalId.cs objects.
The ExternalId.cs class has an inverse property back to the IRecord interface. This is a one-to-many relationship (one IRecord to many ExternalId).

The desired behavior is that when the IRecord object is deleted, the dependent ExternalIds are too.

Other models/tables in my database also implement this interface (Regulation.cs, Organization.cs, etc.) and the ExternalIds they reference subject to the same OnDelete behavior.

This is what I set up in ApplicationDbContext.cs:

modelBuilder.Entity<Customer>()
    .HasMany(rec => rec.ExternalIds)
    .WithOne(extId => (Customer)extId.Record)
    .OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<Person>()
    .HasMany(rec => rec.ExternalIds)
    .WithOne(extId => (Person)extId.Record)
    .OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<Organization>()
    .HasMany(rec => rec.ExternalIds)
    .WithOne(extId => (Organization)extId.Record)
    .OnDelete(DeleteBehavior.Cascade);

What am I doing wrong that this is not working as expected?

These are the different models involved above:

IRecord:

public interface IRecord {
    int Id { get; set; }
}

ExternalId:

public class ExternalId : IRecord
{
    public int Id { get; set; }

    [InverseProperty("ExternalIds")]
    public IRecord Record { get; set; }

    public string Service { get; set; } // Comes from DataSource Enum

    public string Purpose { get; set; } // Comes from IdType Enum

    public string Value { get; set; } // Value
}

Person

public class Person: IRecord
{
    #region Properties
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    [DataType(DataType.Date)]
    public DateTime? DateOfBirth { get; set; }

    public int ContactInformationId { get; set; }
    public virtual ContactInformation ContactInformation { get; set; }

    public string PictureURL { get; set; }

    public string Title { get; set; }

    public List<ExternalId> ExternalIds { get; set; }
}

These are the table names:

public DbSet<ExternalId> Identifiers { get; set; }
public DbSet<Person> People { get; set; }

When I attempt to delete the Person objects in my database (via _context.People.RemoveRange(peopleToDelete)) I get the following error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_Identifiers_People_PersonId". The conflict occurred in database "OurGov", table "dbo.Identifiers", column 'PersonId'.
The DELETE statement conflicted with the REFERENCE constraint "FK_Identifiers_People_PersonId". The conflict occurred in database "OurGov", table "dbo.Identifiers", column 'PersonId'.

Did I set the relationships up incorrectly? I feel like this is due to my relationship property being an interface that I had to cast.

Update - 2020-05-05

I notice that my SQL Table that was generated has three columns, PersonId, RegulationId, RecordId. The Person class has its id stored in PersonId as opposed to RecordId. I can see this was generated in one of my migrations awhile back.

I suspect I may have Added a migration and Updated the database prior to adding the inverse property and/or marking Person class (as well as the Regulation class) as implementing the Record interface.

How can I clean this up to drop those columns and have all of my IRecord objects referenced in RecordId?

c#

sql-server

entity-framework

entity-framework-core

asked on Stack Overflow May 4, 2020 by

Taylor C. White • edited May 5, 2020 by

Taylor C. White

1 Answer

That model generated an ON DELETE CASCADE Foreign Key for me. eg

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.SqlServer;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading.Tasks;

namespace EfCore3Test
{
    public interface IRecord
    {
        int Id { get; set; }
    }
    public class Person : IRecord
    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        [DataType(DataType.Date)]
        public DateTime? DateOfBirth { get; set; }

        public int ContactInformationId { get; set; }
        // public virtual ContactInformation ContactInformation { get; set; }

        public string PictureURL { get; set; }

        public string Title { get; set; }

        public List<ExternalId> ExternalIds { get; set; }
    }
    public class ExternalId : IRecord
    {
        public int Id { get; set; }

        [InverseProperty("ExternalIds")]
        public IRecord Record { get; set; }

        public string Service { get; set; } // Comes from DataSource Enum

        public string Purpose { get; set; } // Comes from IdType Enum

        public string Value { get; set; } // Value
    }

    public class Db : DbContext
    {
        public DbSet<ExternalId> Identifiers { get; set; }
        public DbSet<Person> People { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Person>()
                        .HasMany(rec => rec.ExternalIds)
                        .WithOne(extId => (Person)extId.Record)
                        .OnDelete(DeleteBehavior.Cascade);
        }
        private static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
        {
            builder.AddFilter((category, level) =>
               category == DbLoggerCategory.Database.Command.Name
               && level == LogLevel.Information).AddConsole();
        });


        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(loggerFactory)
                          .UseSqlServer("Server=.;database=EfCore3Test;Integrated Security=true",
                                        o => o.UseRelationalNulls());

            base.OnConfiguring(optionsBuilder);
        }
    }





    class Program
    {

        static void Main(string[] args)
        {


            using var db = new Db();

            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();
        }
    }
}

outputs

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [EfCore3Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [EfCore3Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (121ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [EfCore3Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [EfCore3Test] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [People] (
          [Id] int NOT NULL IDENTITY,
          [FirstName] nvarchar(max) NULL,
          [LastName] nvarchar(max) NULL,
          [DateOfBirth] datetime2 NULL,
          [ContactInformationId] int NOT NULL,
          [PictureURL] nvarchar(max) NULL,
          [Title] nvarchar(max) NULL,
          CONSTRAINT [PK_People] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Identifiers] (
          [Id] int NOT NULL IDENTITY,
          [RecordId] int NULL,
          [Service] nvarchar(max) NULL,
          [Purpose] nvarchar(max) NULL,
          [Value] nvarchar(max) NULL,
          CONSTRAINT [PK_Identifiers] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Identifiers_People_RecordId] FOREIGN KEY ([RecordId]) REFERENCES [People] ([Id]) ON DELETE CASCADE
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_Identifiers_RecordId] ON [Identifiers] ([RecordId]);

answered on Stack Overflow May 4, 2020 by

David Browne - Microsoft

User contributions licensed under CC BY-SA 3.0