How to write a dynamic Lambda expression to access an Nth Parent entity?

Question

I have an application using nhibernate and linq to execute queries in a database. Everything works fine, but I have the following model mapped (with auto-join):

public class A { public virtual int Id { get; set; } public virtual A ParentA { get; set; } } 

I have a method for getting some information, and this method takes a count integer parameter. I would like to know if there is a way to access count once the ParentA property creates a filter.

 protected void Proccess(int count, int id) { var query = session.Query<A>(); // I would like to access, the number of count variable in parentA property, something like: // for sample: if count is 3, I would like a query like: query.Where(x => x.ParentA.ParentA.ParentA.Id == id); // maybe something like this: for (int i = 0; i < count; i++) { query.Where(x => x.ParentA.ParentA.ParentA.Id == id); } var result = query.ToList(); // continue method... } 

Is there a way to create such a lambda expression?

Answer 1

Something like this should work:

 protected void Process(int count, int id) { var query = session.Query<A>().Where(BuildFilter(count,id)); var result = query.ToList(); } private static Expression<Func<A, bool>> BuildFilter(int count, int id) { var x = Expression.Parameter(typeof(A), "x"); Expression instance = x; if (count != 0) { var prop = typeof(A).GetProperty("ParentA"); while (count > 0) { instance = Expression.Property(instance, prop); count--; } } var instanceId = Expression.Property(instance, "Id"); var compareId = Expression.Constant(id); var body = Expression.Equal(instanceId, compareId); return Expression.Lambda<Func<A, bool>>(body, x); } 

Answer 2

Generated SQL will not be next to “good,” as Richard Deming's answer, but it has the advantage of being read by those who do not know the System.Linq.Expression namespace.

 protected List<A> Proccess(int count, int id) { var query = session.Query<A>() .Select(x => new Pair { Item = x, Ancestor = x }; Func<IQueryable<Pair>, IQueryable<Pair>> addNesting = q.Select(x => new Pair{ Item = x.Item, Ancestor = Ancestor.Parent }); foreach(var i in Enumerable.Range(0, count)) { query = addNesting(query); } return query.Where(x => x.Ancestor == id).Select(x => x.Item).ToList(); } private class Pair { public A Item {get;set;} public A Ancestor { get; set; } }