Removing many to many associations in NHibernate

Question

I have a many to many relationship using NHibernate.

Is there an easier way of removing the category association from all products without creating an class for the Join Table?

I'd like the SQL to look like

DELETE FROM ProductCategories WHERE CategoryId = 123

and here's the code we're using to delete the associations

        DetachedCriteria fetchCriteria = DetachedCriteria.For<Product>()
            .CreateAlias("Categories", "categories")
            .Add(Restrictions.Eq("categories.Id", category.Id));

        ICollection<Product> products = productRepo.FindAll(fetchCriteria);

        foreach(var product in products)
        {
            product.Categories.Remove(category);
            productRepo.Save(product);
        }

A Product has a set of Categories

public class Product{
    public ISet<Category> Categories
    {
        get;set;
    }
}

A Category has an Id Guid property

public class Category {
    public Guid Id {get;set;}
    public string Name {get;set;}
}

Many thanks :o)

Answer 1

Have you tried using the ISession.Delete(query) method? It takes a HQL query, loads the objects then deletes them.

session.Delete("from Product p join p.Categories c where c.id = :category", category, NHibernateUtil.Entity(typeof(Category)));

My HQL is a but rusty, so excuse me if the query is not quite right.

Another option if you're not keen on the idea of preloading all the objects, is to use session.CreateSQLQuery and just pass in a straight SQL statement to do the delete. NHibernate will execute that against the server no questions asked.