Fluent NHibernate: Query a many to many relation [duplicate]

This question already has answers here: Implementing "where not exists" with NHibernate QueryOver (2 answers) Closed 5 years ago.

I have these two objects

public class Product {
  public virtual int Id { get; set; }
  public virtual string Name { get; set; }
  public virtual IEnumerable<Store> Stores { get; set; }
}

public class Store {
  public virtual int Id { get; set; }
  public virtual string Name { get; set; }
  public virtual IEnumerable<Product> Products { get; set; }
}

And Fluent NHibernate creates three tables for me, one for products, on for stores and one for the relation between them.

How can I write a query that fetches all stores that's NOT related to a certain product?

In the store repository, I could do something like:

return GetAll().Where(x => !x.Products.Any(y => y.Id == productId));

... but that don't seem very efficient.

I have tried with the following line:

return Session.QueryOver<Store>().Where(x => !x.Products.Any(y => y.Id == productId)).List();

... but it almost seems like FH can't query over properties that is not a part of the queried object 开发者_运维百科(the product id is not in the Company table etc).

---

try this:

return Session.QueryOver<Store>().Where(x => x.Products.Where(y => y.Id == productId).Count() == 0).List();

---

This is a possible solution, imagine you have a comboBox where you select a product and you want a list with the Stores where you cannot find that product, the solution would be the below:

Product product = session.Query<Product>()
                    .Where(x => x.Id == ((Product)comboBoxProduct.SelectedItem).Id)
                    .SingleOrDefault();

IList<Store> stores = session.QueryOver<Store>()
                    .Right.JoinQueryOver<Product>(x => x.ProductsInStores)
                    .Where(y => y.Id != product.Id)
                    .List<Store>();

Note: "ProductsInStores" would be the third class that connects "Product" and "Store"

You should also declare the name of this third class in the Maps of Product and Store.

In Product mapping you should have:

HasManyToMany(x => x.Stores)
            .Cascade.All()
            .Table("ProductsInStores");

In Store mapping you should have:

HasManyToMany(x => x.Products)
            .Cascade.All()
            .Inverse()
            .Table("ProductsInStores");