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)
Best Answer
Have you tried using the
ISession.Delete(query)
method? It takes a HQL query, loads the objects then deletes them.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.