Filtering entities by related entity in hierarchy table

Question

I have this relation between my tables:

The Structures table is a multi level hierarchy table where ParentId is related to the StructureId of the parent. Only the root element has the ParentId field with null value (formally it is the warehouse).

I would like to get all StockabelElement that are located in any child of a specific warehouse (the Stucture that has the ParentId null) using nHibernate.

I tried differents approach.

Trying with CreateCriteria this is my code:

  var queryCriteria = unitOfWork.Session.CreateCriteria(typeof(ContainerItemModel), "containerItem");
                    queryCriteria.CreateAlias("containerItem.ParentContainer", "container");
                    queryCriteria.CreateAlias("containerItem.StockableItem", "stockableItem");
                    queryCriteria.CreateAlias("container.ContainerStructure", "structure");
                    queryCriteria.CreateAlias("structure.Parent", "parentStructure");
                    queryCriteria.CreateAlias("container.Company", "company");
                    queryCriteria.Add(Restrictions.Eq("company.ExternalCode", response.Filter.CompanyExternalCode));

And I tryed to filter the warehouse with this:

 queryCriteria.Add( Restrictions.And( Restrictions.IsNull("parentStructure.Parent") , Restrictions.Eq("parentStructure.Id", warehouseStructure.Id)));

But this doesn't work (obviously) because it filter the first level parent of the current structure (that is not the warehouse). I don't know how to make a recursion (if possible) here.

Then i tried with HQL and this is my code:

                 var queryStr = @"
from ContainerModel as container 
inner join container.ContainerItems as containerItem 
inner join containerItem.StockableItem as stockableItem
inner join container.ContainerStructure as structure
left join fetch structure.Parent as parentStructure
where container.Company.ExternalCode = :companyExternalCode";


                    var query = unitOfWork.Session.CreateQuery(queryStr);

                    query.SetParameter("companyExternalCode", response.Filter.CompanyExternalCode);

But i have same issue as before... I don't know how to filter my warehouse using a recursive way.

I can do it with in memory Linq using an extension that get the root structure from a child structure but I dont want to do it in memory because in production we will have over 1 million of row in ContainerItems table... and I dont think is a good idea to load all. If I use Lazy Loading I have to execute thousand of queries after and this would be a problem.

This is the linq approach that works but it cannot be the solution.

  public static IEnumerable<T> GetParentsHierarchy<T>(this T child, Func<T, T> parentSelector)
        {
            var currentObject = child;
            while (parentSelector(currentObject) != null)
            {
                yield return currentObject;
                currentObject = parentSelector(currentObject);
            }

            //this is the ancestor parent
            yield return currentObject;
        }

This is the extension that I use to get the root and the I can use it in Linq like that:

List.Where(structure => structure.GetParentsHierarchy(s => s.Parent).SingleOrDefault(s => s.StructureType == StructureType.Warehouse).Id == warehouseStructure.Id).ToList();

I hope my goal is understandable.

linq (and presumably nhibernate) both generate SQL code, then read the data into data structures in memory. Do you have any reason to believe that nhibernate will do this more efficiently than linq? Or that you could do it any more efficiently in custom code? The linq version works, it is easily understandable. You should at least bench-mark it against production data before rejecting it. — Nicholas Hunter, Apr 09 '21 at 14:27
Thanks for your comment @Nicholas. If I use linq, when I access to the leaf entity, this produces at least 2 query (one for the structure and one for the container item). In my test with 100 container item (in production it would be over 1 million) I see something like 200 queries... I cannot use this solution in production because DB Admin would kill me if my application do 2 million of queries more time per day. — Fabio Cavallari, Apr 12 '21 at 07:29
Maybe this will help: [Linq to Entities - eager loading using Include()](https://stackoverflow.com/questions/3186009/linq-to-entities-eager-loading-using-include) — Nicholas Hunter, Apr 12 '21 at 11:40

score 0 · Answer 1 · answered Apr 13 '21 at 06:57

I think that is not possible to do what I want in this way. I solved my problem executing a LINQ recursive query on parent Structure collecting all childrend identifier. After I simply added a restriction on my Criteria using Restriction.In with the structures identifier collection.

query.Add( Restrictions.In("structure.Id", childStructures.Select(s=>s.Id).ToArray()));

Filtering entities by related entity in hierarchy table

1 Answers1