I have an issue with cascading delete. I would like to use it if possible but in some cases it is not working for me. I have set up my DbContext to look like this:
public class DatabaseContext : IdentityDbContext<User>
{
// Define our tables
public DbSet<Company> Companies { get; set; }
public DbSet<Center> Centers { get; set; }
public DbSet<Collection> Collections { get; set; }
public DbSet<UserCenter> UserCenters { get; set; }
public DbSet<Journal> Journal { get; set; }
/// <summary>
/// static constructor (only gets called once)
/// </summary>
static DatabaseContext()
{
// Create the database and insert our records
Database.SetInitializer<DatabaseContext>(new DatabaseInitializer());
}
/// <summary>
/// Default constructor
/// </summary>
public DatabaseContext()
: base("DefaultConnection")
{
// Write our SQL to the debug window
this.Database.Log = s => Debug.WriteLine(s);
// Disable Lazy Loading
base.Configuration.LazyLoadingEnabled = false;
}
// Public static method for OWIN
public static DatabaseContext Create()
{
return new DatabaseContext();
}
/// <summary>
/// Overrides the inherited OnModelCreated method.
/// </summary>
/// <param name="modelBuilder">The DbModelBuilder</param>
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Change default table names for Identity Framework
modelBuilder.Entity<IdentityRole>().ToTable("Roles");
modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles");
modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims");
modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogins");
// Create mappings for Identity Framework
modelBuilder.Entity<IdentityUserLogin>().HasKey(m => new { m.UserId, m.ProviderKey });
modelBuilder.Entity<IdentityRole>().HasKey<string>(m => m.Id);
modelBuilder.Entity<IdentityUserRole>().HasKey(m => new { m.RoleId, m.UserId });
modelBuilder.Entity<User>().HasMany(m => m.Logins).WithRequired().HasForeignKey(m => m.UserId);
modelBuilder.Entity<IdentityRole>().HasMany(m => m.Users).WithRequired().HasForeignKey(m => m.RoleId);
// Create our mappings
modelBuilder.Entity<UserCenter>().HasKey(m => new { m.UserId, m.CenterId });
modelBuilder.Entity<Center>().HasRequired(m => m.Company).WithMany(m => m.Centers).HasForeignKey(m => m.CompanyId);
modelBuilder.Entity<Center>().HasMany(m => m.Collections).WithRequired().HasForeignKey(m => m.CenterId);
modelBuilder.Entity<Company>().HasMany(m => m.Members).WithOptional().HasForeignKey(m => m.CompanyId).WillCascadeOnDelete(false);
// Archive relationships
modelBuilder.Entity<User>().HasOptional(m => m.CreatedBy).WithMany().HasForeignKey(m => m.CreatedById).WillCascadeOnDelete(false);
modelBuilder.Entity<User>().HasOptional(m => m.ModifiedBy).WithMany().HasForeignKey(m => m.ModifiedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Company>().HasRequired(m => m.CreatedBy).WithMany().HasForeignKey(m => m.CreatedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Company>().HasRequired(m => m.ModifiedBy).WithMany().HasForeignKey(m => m.ModifiedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Collection>().HasRequired(m => m.CreatedBy).WithMany().HasForeignKey(m => m.CreatedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Collection>().HasRequired(m => m.ModifiedBy).WithMany().HasForeignKey(m => m.ModifiedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Center>().HasRequired(m => m.CreatedBy).WithMany().HasForeignKey(m => m.CreatedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Center>().HasRequired(m => m.ModifiedBy).WithMany().HasForeignKey(m => m.ModifiedById).WillCascadeOnDelete(false);
modelBuilder.Entity<Journal>().HasRequired(m => m.User).WithMany().HasForeignKey(m => m.UserId).WillCascadeOnDelete(false);
}
}
As you can see, there are some archive mappings that I have that I have disabled Cascade on delete from. This is by design because I don't want to delete an item and it delete the user that created / modified it :)
If I delete a Center and it has some Collections, it deletes fine and the collections are removed. Same goes for if I delete a User and he has some UserCenters both will be deleted fine, but if I try to delete a Company (which has Users and the users have UserCenters) it fails and I get an exception:
exceptionMessage=The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Users_dbo.Companies_CompanyId". The conflict occurred in database "melanite", table "dbo.Users", column 'CompanyId'. The statement has been terminated.
I can see that it is moaning at the CompanyId field for the User, so I changed this line:
modelBuilder.Entity<Company>().HasMany(m => m.Members).WithOptional().HasForeignKey(m => m.CompanyId).WillCascadeOnDelete(false);
Because if I delete a User, I don't want it to delete the Company that the User belongs to. But, on the other hand, if I delete a Company I do want it to delete the Users...
Does anyone know how I can fix my issue?
