How to display grandparent when child is given-CodePudding

How to display grandparent when 'childItemNo' is given.

Below is my model: Any record (ItemNo) can become a grandparent, parent or child.

public partial class item
{      
    public string ItemNo { get; set; }         
    public string ParentItemNo { get; set; }     
}

Below query returns parent when childItemNo is given: Would like to display grandparent as well.

  var result =  DbContext.ItemTables.Where(p => p.ItemNo== childItemNo).Select(p => p.ParentItemNo).ToListAsync();

Thank you.

CodePudding user response：

If you have navigation properties, this is trivial:

Select(c => c.Parent.ParentItemNo)

Without them, you could go slightly more dirty:

Select(c => DbContext.ItemTables.First(p => p.ItemNo == c.ParentItemNo).ParentItemNo)

Or use a join

(from ch in db.ItemTables
join pa in db.ItemTables
on ch.ParentItemNo equals pa.ItemNo
where ch.ItemNo == childItemNo
select pa.ParentItemNo).First()

Or in method syntax:

db.ItemTables.Where(ch => ch.ItemNo == childItemNo).Join(
  db.ItemTables,
  l => l.ParentItemNo, //left is the child
  r => r.ItemNo,       //right is the parent
  (l, r) => r.ParentItemNo  //want the parent's parent
).First();