如何使用NHibernate递归过滤分层结构表中特定仓库子节点下的StockableElement

Hey there! Let's fix this recursive filtering problem with NHibernate the right way—since in-memory filtering won't cut it for 1M+ records, we need to push the recursion logic down to the database level where it belongs.

1. HQL Recursive CTE Query (Recommended) #

If your database supports Common Table Expressions (CTE) (like SQL Server, PostgreSQL, MySQL 8+), this is the cleanest and most performant approach. We'll define a recursive CTE to fetch all sub-structures under your target warehouse, then join it to our main query to get the relevant StockableElements.

var queryStr = @"
with recursive StructureHierarchy as (
    -- Start with the target warehouse node
    select s.Id, s.ParentId
    from StructureModel s
    where s.Id = :warehouseId
    union all
    -- Recursively fetch all child structures
    select child.Id, child.ParentId
    from StructureModel child
    join StructureHierarchy parent on child.ParentId = parent.Id
)
select stockableItem
from ContainerModel container
inner join container.ContainerItems containerItem
inner join containerItem.StockableItem stockableItem
inner join container.ContainerStructure structure
inner join StructureHierarchy sh on structure.Id = sh.Id
where container.Company.ExternalCode = :companyExternalCode";

var query = unitOfWork.Session.CreateQuery(queryStr);
query.SetParameter("warehouseId", warehouseStructure.Id);
query.SetParameter("companyExternalCode", response.Filter.CompanyExternalCode);

// Get your filtered StockableElements directly from the database
var result = query.List<StockableElementModel>();

This query builds the entire hierarchy of structures under your warehouse first, then only pulls StockableElements linked to those structures—all logic runs in the database, so you avoid memory bloat and N+1 query issues.

2. CreateCriteria with Custom SQL Restriction #

If you prefer using CreateCriteria or need compatibility with older databases that don't support CTE (though I'd recommend upgrading if possible), you can embed a recursive check using SqlRestriction:

var queryCriteria = unitOfWork.Session.CreateCriteria(typeof(ContainerItemModel), "containerItem")
    .CreateAlias("containerItem.ParentContainer", "container")
    .CreateAlias("containerItem.StockableItem", "stockableItem")
    .CreateAlias("container.ContainerStructure", "structure")
    .CreateAlias("container.Company", "company")
    .Add(Restrictions.Eq("company.ExternalCode", response.Filter.CompanyExternalCode))
    // Add a recursive check to confirm the structure is in the warehouse's hierarchy
    .Add(Restrictions.SqlRestriction(
        @"exists (
            select 1 from (
                with recursive StructureHierarchy as (
                    select Id, ParentId from Structure where Id = ?
                    union all select c.Id, c.ParentId from Structure c join StructureHierarchy p on c.ParentId = p.Id
                )
                select * from StructureHierarchy where Id = structure.Id
            ) as subquery
        )",
        warehouseStructure.Id,
        NHibernateUtil.Guid // Adjust this to match your StructureId data type (e.g., Int32)
    ));

var result = queryCriteria.List<StockableElementModel>();

This does the same recursive hierarchy check as the CTE approach, but wraps it in an exists clause that integrates with your CreateCriteria setup.

Why Your Previous Attempts Failed #

Your original CreateCriteria and HQL queries only checked the immediate parent of each structure, not the full recursive hierarchy. NHibernate doesn't handle recursive filtering out of the box—you need to explicitly tell the database to traverse the parent-child chain all the way up to the warehouse root.

Quick Performance Tip #

Make sure you have indexes on Structure.ParentId and Structure.Id—this will drastically speed up the recursive hierarchy traversal, especially with large datasets.

内容的提问来源于stack exchange，提问作者Fabio Cavallari