How to Delete in a many to many relationship?

Question

I have a many to many relationship:

Product has many Categories and Category has Many Products.

Say I have

Shopping Category  
Food Category

Product A - Shopping Category, Food Category  
Product B - Shopping Category

Now I delete Shopping Category. I want the Product A reference to be removed from Shopping Category and I want Product B to be removed completely.

I would end up with:

Product A - Food Category.

How do I do this in nhibernate (I am using fluent nhibernate).

I tried to use Cascade DeleteOrphan and AllDeleteOrphan but when I do that and delete Shopping both Product A and B get deleted.

public class CategoryMapping : ClassMap<Category>
{
    public CategoryMapping()
    {
        Id(x => x.Id).GeneratedBy.GuidComb();

        Map(x => x.Name).Not.Nullable().NvarcharWithMaxSize();
        HasManyToMany(x => x.Products).Cascade.DeleteOrphan();
    }
}


public class ProductMapping : ClassMap<Product>
{
    public ProductMapping()
    {
        Id(x => x.Id).GeneratedBy.GuidComb();
        Map(x => x.Name).Not.Nullable().NvarcharWithMaxSize();
        HasManyToMany(x => x.Categories);
    }
}

  unitOfWork.BeginTransaction();
  Category category =session.Load<Category>(id);
  session.Delete(category);
  unitOfWork.Commit();

score 2 · Accepted Answer · answered Mar 25 '12 at 21:15

I don't think this can be handled by mapping with existing data structure. I think you would need to write some manual code (*) or change data structure.

(*) Not 100% sure it works though...

unitOfWork.BeginTransaction();
Category category =session.Load<Category>(id);
var productsDel = category.Products.Where(p => p.Categories.Count == 1);
productsDel.ForEach(p => session.Delete(p));
session.Delete(category);
unitOfWork.Commit();

Other:

I'm also thinking about adding mapping for your cross-ref tables. Then you should be able to configure mapping so it will delete only records from that cross-ref table. You will need to verify if there are products without references and delete them periodically. (some periodic clean-up code, like running some stored procedure). I know this solutions smells bad :) There are still triggers and other SQL Server stuff... not good solutions anyway, but solutions.

score 0 · Answer 2 · answered Mar 15 '12 at 19:55

0

If you just want to remove the association between the two use Cascade.SaveUpdate()

Then just remove the entity from the collection and commit the transaction if you are using transactions if not you will need to do a Session.Flush

answered Mar 15 '12 at 19:55

Cole W

15,123
6
51
85

I don't understand. I thought saveUpdate would well resave it and I am not sure what you mean by remove the entity from the collection. I could have 1000's of products that need their reference to be remove when I delete the catergory so this seems to be what a cascade would be for then me having to do it myself. – chobo2 Mar 15 '12 at 22:15
I may have misunderstood your question but I can definitely see why `DeleteOrphan` and `AllDeleteOrphan` delete Product A in this instance. `SaveUpdate` will only delete the associations (the many to many table entries ... `product_category`) but will not delete `Product A`. This is something you would have to handle manually when using this cascade setting. I don't think there is a cascade that handles this (could be wrong though). – Cole W Mar 15 '12 at 22:36
Hmm I will have to try it but I thought DeleteOrphan was exactly for this situation. I don't see it as to me Product A is not an orphan as it still has a relationship to something. – chobo2 Mar 15 '12 at 23:27

How to Delete in a many to many relationship?

2 Answers2