database design - n:m with payload in Enitity Framework 4.x -

hi trying desperately create following relation in ef4.x

"one material made of many materials amount , each material can used in materials"

ideally convert material n:m material content payload, have translated to:

"material 1:n materialusage" , "materialusage m:1 material"

i have created 2 tables since have payload (certain amount)

'material' , 'materialusage'

in material have defined relation 'ismadeof' links 'materialusage.isusedin' , relation 'isusedfor' links 'materialusage.ismadeof'

in materialusage have aside of 2 above described filed 'content'.

now problem:

if delete material run error message saying within association 'materialmaterialusage', identifies relation "material.isusedfor <-> materialusage.ismadeof", relation in status 'deleted' , due multiplicity definition corresponding 'materialusage' record must in status 'deleted' well, not found.

my intention delete material , 'materialusages' identified thru 'material.ismadeof'. not include materials referenced 'materialusage' records have reference in 'isusedin' material deleted.

i hope have made myself clear enough understandable.

now trying find clear way so. guessing work referential integrity not familiar , therefor lost.

can me there - can change db-design no problem.

any highly appreciated!

i'll try halfbaked mix of answer , question. in ef 4.1 dbcontext api create following model classes (i hope understood description correctly):

public class material {     public int id { get; set; }     public string name { get; set; }     public icollection<materialusage> ismadeof { get; set; }     public icollection<materialusage> isusedfor { get; set; } }  public class materialusage {     public int id { get; set; }     public int content { get; set; }     public material isusedin { get; set; }     public material ismadeof { get; set; } } 

and derived context , mapping:

public class mycontext : dbcontext {     public dbset<material> materials { get; set; }     public dbset<materialusage> materialusages { get; set; }      protected override void onmodelcreating(dbmodelbuilder modelbuilder)     {         modelbuilder.entity<material>()             .hasmany(m => m.ismadeof)             .withrequired(m => m.isusedin)             .willcascadeondelete(false);          modelbuilder.entity<material>()             .hasmany(m => m.isusedfor)             .withrequired(m => m.ismadeof)             .willcascadeondelete(false);     } } 

i've set navigation properties in materialusage required because think materialusage cannot exist without reference materials. right? far can see necessary turn off cascading delete, otherwise ef complain multipe possible cascading delete paths not allowed.

now, create materials , relationships work:

using (var context = new mycontext()) {     var copper = new material { name = "copper" };     context.materials.add(copper);      var zinc = new material { name = "zinc" };     context.materials.add(zinc);      var brass = new material     {         name = "brass",         ismadeof = new list<materialusage>         {             new materialusage { content = 10, ismadeof = copper },             new materialusage { content = 20, ismadeof = zinc }         }     };     context.materials.add(brass);      context.savechanges(); } 

the result in database is:

table materials          table materialusages  id   name                id    content    isusedin_id    ismadeof_id ---------                ------------------------------------------- 1    brass               1     10         1              2 2    copper              2     20         1              3 3    zinc 

now, deleting difficult because material appears in both relationships. don't know how accomplish this:

my intention delete material , 'materialusages' identified thru 'material.ismadeof'

if understand correctly delete zinc:

var zinc = context.materials     .include(m => m.ismadeof)     .where(m => m.name == "zinc")     .single(); foreach (var usage in zinc.ismadeof.tolist())     context.materialusages.remove(usage); context.materials.remove(zinc); context.savechanges(); 

this doesn't work because zinc made of nothing (the ismadeof collection empty, loop above nothing). if remove zinc violate constraint namely zinc used for brass. (id = 2 in materialusages table cannot exist without zinc.)

i opinion must also delete materialusages identified material.isusedfor:

var zinc = context.materials     .include(m => m.ismadeof)     .include(m => m.isusedfor)     .where(m => m.name == "zinc")     .single(); foreach (var usage in zinc.ismadeof.tolist())     context.materialusages.remove(usage); foreach (var usage in zinc.isusedfor.tolist())     context.materialusages.remove(usage); context.materials.remove(zinc); context.savechanges(); 

this delete id = 3 in materials table , id = 2 in materialsusages table, fullfilling referential constraints now.

not sure if want.

edit

i believe see now: want have exception thrown due violated constraint when delete zinc. because: should not allowed delete material long used in material (zinc used in brass, therefore it's forbidden delete zinc long brass in database). ok, replacing zinc brass in example work indeed:

var brass = context.materials     .include(m => m.ismadeof)     .where(m => m.name == "brass")     .single(); foreach (var usage in brass.ismadeof.tolist())     context.materialusages.remove(usage); context.materials.remove(brass); context.savechanges(); 

it deletes both rows in materialusages table , brass in material table.

edit 2

if want check if material delete used other material test before try delete:

if (context.materials     .where(m => m.name == "brass")     .select(m => !m.isusedfor.any())     .single()) {     // code snippet above } else {     // "brass" cannot deleted since used other materials... }