c# - The DELETE statement conflicted with the SAME TABLE REFERENCE constraint with Entity Framework -

i have table self reference parentid fk id (pk).
using ef (code-first), i've set relationship follows:

this.hasoptional(t => t.parentvalue)     .withmany(t => t.childvalues)     .hasforeignkey(t => t.parentid); 

when try delete children , parent, delete commands ef issues database not in order expected them go - attempts delete parent record first.

i realize have couple of options here (neither of like):

1. delete child records first, full save/commit, , delete parent record. complexity of model , logic maintains it, isn't option - can't issue multiple commit commands whenever want.
2. dissolve relationship before deleting anything. seems more sensible solution, again, have issue separate commit update statement before deletes. want avoid multiple save/commit calls.
3. use trigger delete children prior deleting parent record. i'd avoid triggers , problematic nature as possible.

so question is.. there way enforce deletion of children before parent record? perhaps i'm missing sort of explicit way of telling ef needs take care of these children before parent? maybe there's way direct ef delete in descending order of ids? don't know.. thoughts?

i realize answer year old, find incomplete. in mind, self-referencing table used represent arbitrary depth.

for example, consider following structure:

/*    *  earth  *      europe  *          germany  *          ireland  *              belfast  *              dublin  *      south america  *          brazil  *              rio de janeiro  *          chile  *          argentina                   *                 */ 

the answer not solve how delete earth, or europe, structure above.

i submit following code alternative (modification of answer provided slauma, did job btw).

in mycontext class, add following methods:

public void deletemyentity(myentity entity) {     var target = myentities         .include(x => x.children)         .firstordefault(x => x.id == entity.id);      recursivedelete(target);      savechanges();  }  private void recursivedelete(myentity parent) {     if (parent.children != null)     {         var children = myentities             .include(x => x.children)             .where(x => x.parentid == parent.id);          foreach (var child in children)         {             recursivedelete(child);         }     }      myentities.remove(parent); } 

i populate data using code-first following class:

public class testobjectgraph {     public myentity rootentity()     {         var root = new myentity         {             name = "earth",             children =                 new list<myentity>                     {                         new myentity                         {                             name = "europe",                             children =                                 new list<myentity>                                 {                                     new myentity {name = "germany"},                                     new myentity                                     {                                         name = "ireland",                                         children =                                             new list<myentity>                                             {                                                 new myentity {name = "dublin"},                                                 new myentity {name = "belfast"}                                             }                                     }                                 }                         },                         new myentity                         {                             name = "south america",                             children =                                 new list<myentity>                                 {                                     new myentity                                     {                                         name = "brazil",                                         children = new list<myentity>                                         {                                             new myentity {name = "rio de janeiro"}                                         }                                     },                                     new myentity {name = "chile"},                                     new myentity {name = "argentina"}                                 }                         }                     }         };          return root;     } } 

which save database following code:

ctx.myentities.add(new testobjectgraph().rootentity()); 

then invoke deletes so:

using (var ctx = new mycontext()) {     var parent = ctx.myentities         .include(e => e.children)         .firstordefault();      var deleteme = parent.children.first();      ctx.deletemyentity(deleteme); } 

which results in database having structure so:

 /*    *  earth  *      south america  *          brazil  *              rio de janeiro  *          chile  *          argentina                   *                 */ 

where europe , of children deleted.

in above, specifying first child of root node, demonstrate using code can recursively delete node , of children anywhere in hierarchy.

if want test deleting everyting, can modify line this:

ctx.deletemyentity(parent); 

or whichever node want in tree.

obviously, won't bounty, post looking solution works self-referencing entities of arbitrary depth.

here full source, modified version of slauma's code selected answer:

using system; using system.collections.generic; using system.data.entity; using system.linq;  namespace efselfreference {     public class myentity     {         public int id { get; set; }         public string name { get; set; }          public int? parentid { get; set; }         public myentity parent { get; set; }          public icollection<myentity> children { get; set; }     }      public class mycontext : dbcontext     {         public dbset<myentity> myentities { get; set; }          protected override void onmodelcreating(dbmodelbuilder modelbuilder)         {             modelbuilder.entity<myentity>()                 .hasoptional(e => e.parent)                 .withmany(e => e.children)                 .hasforeignkey(e => e.parentid);         }           public void deletemyentity(myentity entity)         {             var target = myentities                 .include(x => x.children)                 .firstordefault(x => x.id == entity.id);              recursivedelete(target);              savechanges();          }          private void recursivedelete(myentity parent)         {             if (parent.children != null)             {                 var children = myentities                     .include(x => x.children)                     .where(x => x.parentid == parent.id);                  foreach (var child in children)                 {                     recursivedelete(child);                 }             }              myentities.remove(parent);         }     }      public class testobjectgraph     {         public myentity rootentity()         {             var root = new myentity             {                 name = "earth",                 children =                     new list<myentity>                     {                         new myentity                         {                             name = "europe",                             children =                                 new list<myentity>                                 {                                     new myentity {name = "germany"},                                     new myentity                                     {                                         name = "ireland",                                         children =                                             new list<myentity>                                             {                                                 new myentity {name = "dublin"},                                                 new myentity {name = "belfast"}                                             }                                     }                                 }                         },                         new myentity                         {                             name = "south america",                             children =                                 new list<myentity>                                 {                                     new myentity                                     {                                         name = "brazil",                                         children = new list<myentity>                                         {                                             new myentity {name = "rio de janeiro"}                                         }                                     },                                     new myentity {name = "chile"},                                     new myentity {name = "argentina"}                                 }                         }                     }             };              return root;         }     }      class program     {         static void main(string[] args)         {             database.setinitializer<mycontext>(                new dropcreatedatabasealways<mycontext>());             using (var ctx = new mycontext())             {                 ctx.database.initialize(false);                  ctx.myentities.add(new testobjectgraph().rootentity());                 ctx.savechanges();             }              using (var ctx = new mycontext())             {                 var parent = ctx.myentities                     .include(e => e.children)                     .firstordefault();                  var deleteme = parent.children.first();                  ctx.deletemyentity(deleteme);             }              console.writeline("completed....");             console.writeline("press key exit");             console.readkey();         }     } }