ASP.NET MVC cascade delete

Question

I have two models. Town:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace CpuRegistry.Models
{    
  public enum TownType
  {
      Город, Посёлок, Село
  }

  public class Town
  {
      public int ID { get; set; }

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

      [Required]
      public int RegionID { get; set; }

      public int? DistrictID { get; set; }

      [Required]
      public TownType TownType { get; set; }

      public virtual ICollection<District> Districts { get; set; }        
      public virtual ICollection<Primary> Primaries { get; set; }
  }
}

and District:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace CpuRegistry.Models
{
    public class District
    {
        public int ID { get; set; }

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

        [Required]
        public int RegionID { get; set; }

        public int? TownID { get; set; }

        public virtual ICollection<Town> Towns { get; set; }
        public virtual ICollection<Primary> Primaries { get; set; }
    }
}

Town can belong to District. Disctrict can belong to Town. Also District can have few towns, Town can have few districts. As you can see, I marked DistrictID and TownID with "?".

When I use command Add-Migration, Visual Studio create migration file, which contains:

CreateTable(
                "dbo.TownDistricts",
                c => new
                    {
                        Town_ID = c.Int(nullable: false),
                        District_ID = c.Int(nullable: false),
                    })
                .PrimaryKey(t => new { t.Town_ID, t.District_ID })
                .ForeignKey("dbo.Towns", t => t.Town_ID, cascadeDelete: true)
                .ForeignKey("dbo.Districts", t => t.District_ID, cascadeDelete: true)
                .Index(t => t.Town_ID)
                .Index(t => t.District_ID);

After using command Update-Database, I have next error:

Introducing FOREIGN KEY constraint 'FK_dbo.TownDistricts_dbo.Districts_District_ID' on table 'TownDistricts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I saw this link: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why? Quote from solution:

You must either make the Stage optional in at least one of the entities (i.e. remove the [Required] attribute from the Stage properties)

But I don't have [Required] attribute. Also I tried this solution:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Town>().HasRequired(t => t.Districts).WithMany().WillCascadeOnDelete(false);
            modelBuilder.Entity<District>().HasRequired(d => d.Towns).WithMany().WillCascadeOnDelete(false);
        }

And have error:

CpuRegistry.DataContexts.IdentityUserRole: : EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType. CpuRegistry.DataContexts.IdentityUserLogin: : EntityType 'IdentityUserLogin' has no key defined. Define the key for this EntityType. IdentityUserRoles: EntityType: EntitySet 'IdentityUserRoles' is based on type 'IdentityUserRole' that has no keys defined. IdentityUserLogins: EntityType: EntitySet 'IdentityUserLogins' is based on type 'IdentityUserLogin' that has no keys defined.

And when I open migration file, which creates user tables, I saw something similar to my code:

CreateTable(
                "dbo.AspNetUserRoles",
                c => new
                    {
                        UserId = c.String(nullable: false, maxLength: 128),
                        RoleId = c.String(nullable: false, maxLength: 128),
                    })
                .PrimaryKey(t => new { t.UserId, t.RoleId })
                .ForeignKey("dbo.AspNetRoles", t => t.RoleId, cascadeDelete: true)
                .ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true)
                .Index(t => t.UserId)
                .Index(t => t.RoleId);

But I cannot find, how developers solved this in models or something else.

Thanks for help!

Hoborg · Answer 1 · 2015-04-17T13:41:27.273

According to Configure Many-to-Many relationship using Code First Approach you do not need explicit foreign key declaration public int? TownID { get; set; } in class District and vice versa (in another class). It seems that Entity Framework is able to configure all necessary connections (additional table in database) just using declared navigation collections like public virtual ICollection<Town> Towns { get; set; }.

Addition. What do you mean when declare both single property public int? DistrictID { get; set; } and a collection public virtual ICollection<District> Districts { get; set; }. Do you confuse the concepts of many-to-many relationship in EF or did you mean that these properties denote something different? If they denote something different then you should use Fluent API to configure them properly.

Update. After OP's clarifications I suggest the following code.

public class TestEfContext : ApplicationDbContext {
    public DbSet<Town> Towns { get; set; }
    public DbSet<District> Districts { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Town>()
            .HasMany<District>(x => x.Districts)
            .WithOptional(x => x.Town);

        modelBuilder.Entity<District>()
            .HasMany<Town>(x => x.Towns)
            .WithOptional(x => x.District);
    }
}

public class Town {
    [Key]
    public int Id { get; set; }

    public District District { get; set; }

    public virtual ICollection<District> Districts { get; set; }

    public override string ToString() {
        return Id.ToString();
    }
}

public class District {
    [Key]
    public int Id { get; set; }

    public Town Town { get; set; }

    public virtual ICollection<Town> Towns { get; set; }

    public override string ToString() {
        return Id.ToString();
    }
}

Thus

using (var ctx = new TestEfContext()) {
            ctx.Towns.First().District = ctx.Districts.First();
        }

entails that the Town1 will have District = Distr1 and empty Districts collection while the Distr1 will have Town = null and Towns collection with one element - Town1.

`public int? DistrictID` means, that Town can belong to District (be a part of district). `public virtual ICollection Districts` means, that Town can have districts. In human language: regions are devided by towns and districts. Usually, district include many towns. But some big towns do not belong to any district and are divided by districts. — Sergey Shambal, Apr 17 '15 at 11:22
I drew a [diagram](https://drive.google.com/file/d/0B1bFjCWahknXSVhSRkczTWx2blE/view?usp=sharing). :) — Sergey Shambal, Apr 17 '15 at 11:34
@SergeyShambal I have updated my answer after your explanations. Is it the thing what you needed? If yes then don't you think that such design (one-to-many and reverse many-to-one) is not looking very well? — Hoborg, Apr 17 '15 at 13:44
I tried your solution and the result looks like what I need. Thank you very much. Yes, I think that such design is not a good idea. But diagram in my previous message is from real life, and I don't know how to describe it in another way. — Sergey Shambal, Apr 18 '15 at 09:55
After few days of development I got some problem. When I need to set Town in District model via DropDownList, model does not pass validation, because DropDownList contains ID's of towns, but model await for full class Town. I combined your solution with my previous variant. The result is in my next answer. — Sergey Shambal, Apr 25 '15 at 14:28

score 0 · Accepted Answer · answered Apr 25 '15 at 14:35

So, the final classes, which solves my first question and problems from messages above, are:

namespace CpuRegistry.DataContexts
{
    public class CpuRegistryDb : IdentityDbContext<ApplicationUser>
    {
        public CpuRegistryDb()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }

        public static CpuRegistryDb Create()
        {
            return new CpuRegistryDb();
        }

        public DbSet<Region> Regions { get; set; }
        public DbSet<District> Districts { get; set; }
        public DbSet<Town> Towns { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Town>().HasMany<District>(t => t.Districts).WithOptional(d => d.Town);
            modelBuilder.Entity<District>().HasMany<Town>(d => d.Towns).WithOptional(t => t.District);            
        }
    }
}

namespace CpuRegistry.Models
{
    public enum TownType
    {
        Город, Посёлок, Село
    }

    public class Town
    {
        public int ID { get; set; }

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

        [Required]
        public int RegionID { get; set; }

        public int? DistrictID { get; set; }

        [Required]
        public TownType TownType { get; set; }

        public virtual ICollection<District> Districts { get; set; }
        public virtual Region Region { get; set; }
        public virtual District District { get; set; }

        public override string ToString()
        {
            return ID.ToString();
        }
    }

    public class District
    {
        public int ID { get; set; }

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

        [Required]
        public int RegionID { get; set; }

        public int? TownID { get; set; }

        public virtual ICollection<Town> Towns { get; set; }
        public virtual Region Region { get; set; }
        public virtual Town Town { get; set; }

        public override string ToString()
        {
            return ID.ToString();
        }
    }
}

Thanks @Hoborg for help.

ASP.NET MVC cascade delete

2 Answers2