I am having trouble getting referential integrity dialled down enough
to allow my delete trigger
to fire.
I have a dependent entity with three FKs. I want it
to be deleted when any of the principal entities is deleted.
For principal entities Role and OrgUnit (see below) I can rely on conventions
to create the required one-many relationship and cascade delete does what I want, ie: Association is removed when either principal is deleted.
For Member, however, I have multiple cascade delete paths (not shown here) which SQL Server doesn't like, so I need
to use fluent API
to disable cascade 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 int 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 at fluent API code looks like this:
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 the 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, a Member, an OrgUnit, and an Association tying the three together all is fine. When I delete the Role, the Association gets cascade deleted as I expect. HOWEVER when I delete the Member I get an exception with a referential integrity error. I have tried setting ON CASCADE SET NULL because my memberid FK is nullable but SQL complains again about multiple cascade paths, so apparently I can cascade nothing in the Member-Association relationship.
To get this
to work I must add the following code
to Seed():
context.Database.SqlCommand("ALTER TABLE dbo.ACLEntries DROP CONSTRAINT member_aclentries");
As you can see, this drops the constraint created by the model builder.
QUESTION: this feels like a complete hack. Is there a way using fluent API for me
to say that referential integrity should NOT be checked, or otherwise
to get it
to relax enough for the Member delete
to work and allow the trigger
to be fired?
Thanks in advance for any help you can offer. Although fluent APIs may be "fluent" I find them far from intuitive.
