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.
