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();

Answer 1

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.