NHibernate: Using value tables for optimization AND dynamic join
- by Kostya
Hi all,
My situation is next: there are to entities with many-to-many relation, f.e. Products and Categories. Also, categories has hierachial structure, like a tree. There is need to select all products that depends to some concrete category with all its childs (branch). So, I use following sql statement to do that:
SELECT * FROM Products p
WHERE p.ID IN (
SELECT DISTINCT pc.ProductID FROM ProductsCategories pc
INNER JOIN Categories c ON c.ID = pc.CategoryID
WHERE c.TLeft >= 1 AND c.TRight <= 33378
)
But with big set of data this query executes very long and I found some solution to optimize it, look at here:
DECLARE @CatProducts TABLE (
ProductID int NOT NULL
)
INSERT INTO @CatProducts
SELECT DISTINCT pc.ProductID FROM ProductsCategories pc
INNER JOIN Categories c ON c.ID = pc.CategoryID
WHERE c.TLeft >= 1 AND c.TRight <= 33378
SELECT * FROM Products p
INNER JOIN @CatProducts cp ON cp.ProductID = p.ID
This query executes very fast but I don't know how to do that with NHIbernate. Note, that I need use only ICriteria because of dynamic filtering\ordering.
If some one knows a solution for that, it will be fantastic. But I'll pleasure to any suggestions of course.
Thank you ahead,
Kostya
