CODEWITHSUNDEEP
nhibernate
getit
getit

Reputation: 621

NHibernate criteria query multiple levels of child collections

I am having a lot of difficulty figuring out how to perform a query on 2 tables that don't have reference to each other in code, for example, I have 

Customer -> Product* where customer and product have reference to each other

and Inventory -> Product* where product does not have reference to Inventory

I would like to find all customer who dont have their product in inventory.

I have done this so far

var subQueryInv= DetachedCriteria.For<Inventory>();
        subQueryInv
            .Add(Restrictions.IsNotNull("Product.Id"))
            .SetProjection(Projections.Property<Inventory>(inv=> inv.Product.Id));

        var subQueryProd = DetachedCriteria.For<Product>();
        subQueryTire
            .Add(Subqueries.PropertyNotIn("Id", subQueryInv))
            .SetProjection(Projections.Property<Tire>(prod=> prod.Customer.Id));

        var subQueryCust= DetachedCriteria.For<Customer>();
        subQueryCust
            .Add(Subqueries.PropertyIn("Id", subQueryProd))
            .SetProjection(Projections.Property<TireSet>(cust => cust.Id));

That works, bt the query is very innefficient, it is generating SQL like this for the Inventory part ...WHERE Product.Id NOT IN (SELECT Inventory.ProductId FROM Inventory WHERE Inventory.ProductId IS NOT NULL)

So for each product record, it is querying the entire Inventory table. How can I get the subquery to have a reference to the parent Id like this:

    ...WHERE Product.Id NOT IN (SELECT Inventory.ProductId FROM Inventory WHERE Inventory.ProductId IS NOT NULL AND Inventory.ProductId = Product.Id)

?

Upvotes: 1

Views: 1275

Answers (1)

Firo
Firo

Reputation: 30803

you can use an alias to reference the main criteria

var subQueryInv= DetachedCriteria.For<Inventory>();
    .Add(Restrictions.IsNotNull("Product.Id"))
    .Add(Restrictions.PropertyEq("Product", "product"))
    .SetProjection(Projections.Property<Inventory>(inv => inv.Product.Id));

var subQueryProd = DetachedCriteria.For<Product>("product");

Upvotes: 2

Related Questions