I am having problems with the referential integrity set, typed enough so that I can fire the delete trigger.
I have a dependent object with three FKs. I want to be deleted when any of the main objects were deleted.
For the main Role and OrgUnit objects (see below), I can rely on conventions to create the required one-to-many relationship, and cascading deletion does what I want, that is, the association is deleted when any of the main participants are deleted.
However, for the Member member, I have several cascading deletion paths (not shown here) that SQL Server doesn't like, so I need to use the free API to disable cascading deletes.
Here is my (simplified) model:
public class Association { public int id { get; set; } public int roleid { get; set; } public virtual Role role { get; set; } public int? memberid { get; set; } public virtual Member member { get; set; } public int orgunitid { get; set; } public virtual OrgUnit orgunit { get; set; } } public class Role { public int id { get; set; } public virtual ICollection<Association> associations { get; set; } } public class Member { public int id { get; set; } public virtual ICollection<Association> associations { get; set; } } public class Organization { public int id { get; set; } public virtual ICollection<Association> associations { get; set; } }
My first run in the free API is as follows:
protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder) { DbDatabase.SetInitializer<ConfDB_Model>(new ConfDBInitializer()); modelBuilder.Entity<Member>() .HasMany(m=>m.assocations) .WithOptional(a=>a.member) .HasForeignKey(a=>a.memberId) .WillCascadeOnDelete(false); }
My seed function creates a delete trigger:
protected override void Seed(ConfDB_Model context) { context.Database.SqlCommand("CREATE TRIGGER MemberAssocTrigger ON dbo.Members FOR DELETE AS DELETE Assocations FROM Associations, deleted WHERE Associations.memberId = deleted.id"); }
PROBLEM - When I run this, create a role, member, OrgUnit and an association that ties everyone together. When I remove a role, the association cascades as I expect it, just like OrgUnit. - HOWEVER - when I delete Member, I get an exception with a referential integrity error. I tried setting ON CASCADE SET NULL because my FK member is NULL, but SQL again complains about several cascading paths, so apparently I can't cascade anything in Member-Association relationships.
To make this work, I have to add the following code to Seed ():
context.Database.SqlCommand("ALTER TABLE dbo.ACLEntries DROP CONSTRAINT member_associations");
As you can see, this reduces the constraint created by the model constructor.
QUESTION: this looks like a complete hack. Is there a way to use the free API for me to say that referential integrity should NOT be verified, or else to make it relax enough to remove the member to work and allow the trigger to start?
Thanks in advance for any help you can offer. Although fluent APIs can be fluent, I find them far from intuitive.