How to fix Entity Framework Core cascade delete error in a many-to-many relationship?

Question

In the application I am currently working on, I have a Member data model and an Address data model that can exist independently from each other. I do not want to delete the Address when a Member gets a new address. I only need to delete the relationship record from the many-to-many join table, MemberAddressJoin.

I am getting this error when I run the update-database command to build the database:

Failed executing DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [AddressMember] (
[AddressesId] int NOT NULL,
[MembersId] int NOT NULL,
CONSTRAINT [PK_AddressMember] PRIMARY KEY ([AddressesId], [MembersId]),
CONSTRAINT [FK_AddressMember_Addresses_AddressesId] FOREIGN KEY ([AddressesId]) REFERENCES [Addresses] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_AddressMember_Members_MembersId] FOREIGN KEY ([MembersId]) REFERENCES [Members] ([Id]) ON DELETE CASCADE
);

How do I configure Entity Framework Core to delete only the record from the MemberAddressJoin table and leave the Addresses and Members tables alone?

These are the data models in a many-to-many relationship:

using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace Application.Data.Models
{    
    public class Member
    {         
        [Key]
        public int Id { get; set; }
       
        [PersonalData]
        public string FirstName { get; set; }

        [PersonalData]
        public string MiddleName { get; set; }
        
        [PersonalData]
        public string LastName { get; set; }

        [PersonalData]
        public string PreferredName { get; set; }
        
        [PersonalData]
        public string Title { get; set; }

        [PersonalData]
        public int Age { get; set; }       

        // Many-to-many navigation
        public virtual ICollection<MemberAddressJoin> MemberAddressJoins { get; set; }                
        // Many-to-many skip navigation
        public virtual ICollection<Address> Addresses { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace Application.Data.Models
{
    public class MemberAddressJoin
    {
        [Key] 
        public int Id { get; set; }
        
        // FK Many-to-many
        public int? MemberId {get; set; }
        // FK Many-to-many navigation
        public virtual Member Member { get; set; }

        // FK Many-to-many
        public int? AddressId { get; set; }
        // FK Many-to-many navigation
        public virtual Address Address { get; set; }
    }
}

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Application.Data.Models
{
    [Table("Addresses")]
    public class Address
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string Line1 { get; set; }
       
        public string Line2 { get; set; }
        
        public string Line3 { get; set; }

        [Required]
        public string City { get; set; }

        [Required]
        // Many-to-one, Address is child
        public int CountyId { get; set; }
        public virtual County County { get; set; }

        //[Required]
        //// Many-to-one, Address is child
        //public State State { get; set; }
        
        [Required]
        public string Zipcode { get; set; }        

        [Required]
        public bool IsMailingAddress { get; set; }

        [Required]
        public bool IsResidenceAddress { get; set; }

        // One-to-one, Address is parent
        public virtual Location Location { get; set; }

        // Many-to-many
        public ICollection<MemberAddressJoin> MemberAddressJoins { get; set; }
        // Many-to-many skip navigation
        public virtual ICollection<Member> Members { get; set; }


        public override bool Equals(object o)
        {
            var other = o as Location;
            return other?.Name == Name;
        }

        public override int GetHashCode() => Name?.GetHashCode() ?? 0;

        public override string ToString() => Name;


        [NotMapped]
        public virtual string Name
        {
            get
            {
                string name = string.Empty;

                if (!string.IsNullOrEmpty(Line1))
                    name = Line1;

                if (!string.IsNullOrEmpty(Line2))
                    name = $"{name}, {Line2}";

                if (!string.IsNullOrEmpty(Line3))
                    name = $"{name}, {Line3}";

                if (!string.IsNullOrEmpty(City))
                    name = $"{name}, {City}";

                if (County.State is not null)
                    name = $"{name}, {County.State.Name}";

                if (!string.IsNullOrEmpty(Zipcode))
                    name = $"{name}, {Zipcode}";

                return name;
            }
        }

    }
}

Answer 1

I needed to make multiple changes to my data models to get rid of this error and to achieve the desired result. The following allows me to delete the relationship entity, MemberAddressJoin, without creating a cascading delete to either Member or Address.

For the join entity (MemberAddressJoin) I needed to:

• remove the nullable operator from the foreign keys ?.^
• remove the virtual accessor from the foreign key navigation.^
• add fluent api code to the entity configuration class (MemberAddressJoinConfig.cs) to turn of delete cascading for both the Member and Address entities.^^

For the Member data model class I needed to:

• add fluent api code to the entity configuration class (MemberConfig.cs) to designate the join table as MemberAddressJoins.^

For the Address data model class I needed to:

• add fluent api code to the entity configuration class (AddressConfig.cs) to designate the join table as MemberAddressJoins.^

This configuration allows me to delete the relationship entity, MemberAddressJoin, between Member and Address without either Member or Address being effected.

^ related to the error fix

^^ related to turning of cascading delete

using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace Application.Data.Models
{    
    public class Member
    {         
        [Key]
        public int Id { get; set; }
       
        [PersonalData]
        public string FirstName { get; set; }

        [PersonalData]
        public string MiddleName { get; set; }
        
        [PersonalData]
        public string LastName { get; set; }

        [PersonalData]
        public string PreferredName { get; set; }
        
        [PersonalData]
        public string Title { get; set; }

        [PersonalData]
        public int Age { get; set; }       

        // Many-to-many navigation
        public virtual ICollection<MemberAddressJoin> MemberAddressJoins { get; set; }                
        // Many-to-many skip navigation
        public virtual ICollection<Address> Addresses { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace Application.Data.Models
{
    public class MemberAddressJoin
    {
        [Key] 
        public int Id { get; set; }
        
        // FK Many-to-many
        public int MemberId {get; set; }
        // FK Many-to-many navigation
        public Member Member { get; set; }

        // FK Many-to-many
        public int AddressId { get; set; }
        // FK Many-to-many navigation
        public Address Address { get; set; }
    }
}

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Application.Data.Models
{
    [Table("Addresses")]
    public class Address
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string Line1 { get; set; }
       
        public string Line2 { get; set; }
        
        public string Line3 { get; set; }

        [Required]
        public string City { get; set; }

        [Required]
        // Many-to-one, Address is child
        public int CountyId { get; set; }
        public virtual County County { get; set; }

        //[Required]
        //// Many-to-one, Address is child
        //public State State { get; set; }
        
        [Required]
        public string Zipcode { get; set; }        

        [Required]
        public bool IsMailingAddress { get; set; }

        [Required]
        public bool IsResidenceAddress { get; set; }

        // One-to-one, Address is parent
        public virtual Location Location { get; set; }

        // Many-to-many
        public ICollection<MemberAddressJoin> MemberAddressJoins { get; set; }
        // Many-to-many skip navigation
        public virtual ICollection<Member> Members { get; set; }


        public override bool Equals(object o)
        {
            var other = o as Location;
            return other?.Name == Name;
        }

        public override int GetHashCode() => Name?.GetHashCode() ?? 0;

        public override string ToString() => Name;


        [NotMapped]
        public virtual string Name
        {
            get
            {
                string name = string.Empty;

                if (!string.IsNullOrEmpty(Line1))
                    name = Line1;

                if (!string.IsNullOrEmpty(Line2))
                    name = $"{name}, {Line2}";

                if (!string.IsNullOrEmpty(Line3))
                    name = $"{name}, {Line3}";

                if (!string.IsNullOrEmpty(City))
                    name = $"{name}, {City}";

                if (County.State is not null)
                    name = $"{name}, {County.State.Name}";

                if (!string.IsNullOrEmpty(Zipcode))
                    name = $"{name}, {Zipcode}";

                return name;
            }
        }

    }
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using PartyTime.Data.Models;

namespace PartyTime.Data.EntityConfigurations
{
    internal class MemberAddressConfig : IEntityTypeConfiguration<MemberAddressJoin>
    {
        public void Configure(EntityTypeBuilder<MemberAddressJoin> builder)
        {
            builder.HasKey(ma => new { ma.MemberId, ma.AddressId });

            builder.HasOne(ma => ma.Member)
                .WithMany(m => m.MemberAddresseJoins)
                .HasForeignKey(ma => ma.MemberId)
                .OnDelete(DeleteBehavior.NoAction);

            builder.HasOne(ma => ma.Address)
                .WithMany(a => a.MemberAddresseJoins)
                .HasForeignKey(ma => ma.AddressId)
                .OnDelete(DeleteBehavior.NoAction);
        }
    }
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using PartyTime.Data.Models;

namespace PartyTime.EntityConfigurations
{
    class AddressConfig : IEntityTypeConfiguration<Address>
    {
        public void Configure(EntityTypeBuilder<Address> builder)
        {
            builder.HasMany(x => x.Members)
                .WithMany(y => y.Addresses)
                .UsingEntity<MemberAddresJoins>();
        }
    }
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using PartyTime.Data.Models;

namespace PartyTime.EntityConfigurations
{
    class MemberConfig : IEntityTypeConfiguration<Member>
    {
        public void Configure(EntityTypeBuilder<Member> builder)
        {
            builder.HasMany(x => x.Addresses)
                .WithMany(y => y.Members)
                .UsingEntity<MemberAddresJoins>();

        }

    }
}

Answer 2

Juding from your message,it created another tabel named of AddressMember instead of MemberAddressJoin to hold the relationship between Member and Address(If you still have problem,please show the detailed message)

You could try configure as the document to avoid this

modelBuilder.Entity<Member>().HasMany(x=>x.Addresses).WithMany(y=>y.Members).UsingEntity<MemberAddressJoin>();

Check your migrationsnapshot file:

modelBuilder.Entity("xx.MemberAddressJoin", b =>
                {
                    b.HasOne("xx.Address", "Address")
                        .WithMany("MemberAddressJoins")
                        .HasForeignKey("AddressId")
                        .OnDelete(DeleteBehavior.Cascade)
                        .IsRequired();

                    b.HasOne("xx.Member", "Member")
                        .WithMany("MemberAddressJoins")
                        .HasForeignKey("MemberId")
                        .OnDelete(DeleteBehavior.Cascade)
                        .IsRequired();

                    b.Navigation("Address");

                    b.Navigation("Member");
                });

Set DeleteBehavior.Cascade here is OK,it won't delete Address or Member record when you delete MemberAddressJoin.When you delete the record of Address or Member,related MemberAddressJoin would also be deleted

And I noticed most of your properties are not nullable,you may have to set the nevigation property nullable to avoid cascading-delete,you could check this document for more details

The default for required relationships like this is Cascade.

Using anything other than cascade delete for required relationships will result in an exception when SaveChanges is called.