n:m with payload in Entity Framework 4.x

I am trying to create the following relation in EF4.x

"one Material is made of many Materials to a certain amount and each Material can be used in Materials"

ideally that would convert to Material n:m Material but the Content is a payload, so I have translated that to:

"Material 1:n MaterialUsage" and "MaterialUsage m:1 Material"

I have created two tables since I have a payload (certain amount)

'Material' and 'MaterialUsage'

in Material I have defined a relation 'IsMadeOf' which links to 'Ma开发者_StackOverflowterialUsage.IsUsedIn' and a relation 'IsUsedFor' which links to 'MaterialUsage.IsMadeOf'

in MaterialUsage I have aside of the two above described the filed 'Content'.

Now to my problem:

If I delete a Material I run into an error message essentially saying that within the Association 'MaterialMaterialUsage', which identifies the relation "Material.IsUsedFor <-> MaterialUsage.IsMadeOf", a relation is in status 'Deleted' and due to the multiplicity definition a corresponding 'MaterialUsage' record must be in status 'Deleted' as well, which was not found.

My intention is however to delete the material and all 'MaterialUsages' which are identified thru 'Material.IsMadeOf'. Which does not include the materials referenced but only the 'MaterialUsage' records that have a reference in 'IsUsedIN' to the Material to be deleted.

Now I am trying to find a clear way to do so. I am guessing it could work with referential integrity but I am not too familiar with that and therefor I am lost.

I can change the DB-Design no problem.

---

I'll try a halfbaked mix of answer and question. In EF 4.1 with DbContext API I would create the following model classes (I hope I understood your 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 this derived context and 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 the navigation properties in MaterialUsage to Required because I think that a MaterialUsage cannot exist without a reference to the materials. Is that right? As far as I can see it is necessary to turn off cascading delete, otherwise EF will complain about multipe possible cascading delete paths which are not allowed.

Now, to create materials and their relationships something like this would 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 the 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 is difficult because Material appears in both relationships. Especially I don't know how you could accomplish this:

My intention is however to delete the material and all 'MaterialUsages' which are identified thru 'Material.IsMadeOf'

If I understand this correctly you would like to do something like this to 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 is made of nothing (the IsMadeOf collection empty, so the loop above does nothing). But if you remove zinc now you violate a constraint namely that zinc is used for brass. (Id = 2 in the MaterialUsages table cannot exist without zinc.)

I my opinion you must also delete the MaterialUsages which are identified by 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 would delete Id = 3 in the Materials table and also Id = 2 in the MaterialsUsages table, fullfilling the referential constraints now.

Not sure if this is what you want.

Edit

I believe I see now: You actually want to have the exception which is thrown due to the violated constraint when you would delete zinc. Because: It should not be allowed to delete a material as long as it is used in another material (zinc is used in brass, therefore it's forbidden to delete zinc as long as brass is in the database). OK, then replacing zinc by brass in the example would 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 just deletes both rows in the MaterialUsages table and brass in the Material table.

Edit 2

If you want to check if the material to delete is used for any other material you could test this before you actually try to delete:

if (context.Materials
    .Where(m => m.Name == "Brass")
    .Select(m => !m.IsUsedFor.Any())
    .Single())
{
    // the code snippet above
}
else
{
    // "Brass" cannot be deleted since it is used for other materials...
}