entity framework - Why can't SQL Server handle cascading deletes for two foreign keys pointing to the same table? -

i'm using entity framework - code first, , i've run issue have disable cascading deletes particular foreign key.

here entity class:

public class challengematch {     public int id { get; set; }     public int challengerclubmemberid { get; set; }     public int challengeeclubmemberid { get; set; }     public bool challengerwon { get; set; }     public string score { get; set; }      public virtual clubmember challengerclubmember { get; set; }     public virtual clubmember challengeeclubmember { get; set; } } 

if allow code first generate database table default settings (which includes cascading deletes), throw exception when tries generate database objects.

if add following code dbcontext class, no longer exception, cascading deletes somewhat work:

protected override void onmodelcreating(dbmodelbuilder modelbuilder) {     modelbuilder.entity<challengematch>()         .hasrequired(cm => cm.challengeeclubmember)         .withmany()         .hasforeignkey(cm => cm.challengeeclubmemberid)         .willcascadeondelete(false); } 

because of this, have write code hand. specifically, if i'm delete clubmember 13, have delete challengematch challengeeclubmemberid 13.

what don't understand why of should necessary. why can't sql server handle cascading delete if there 2 foreign keys pointing same table? can't think of reason why fail.

it should simple three-step process (psuedo code):

1. delete challenge matches challengerclubmemberid == 13
2. delete challenge matches challengeeclubmemberid == 13
3. delete club member id = 13.

why can't sql server or why choose not to?

the problem configuration allows multiple cascade delete paths. can happen if both properties point same clubmember. sql server doesn't allow that. more internal implementation of sql server , more details can found in this answer. think check simple , safe solution avoid race conditions during deletes cascading in parallel.