LINQ Max Grandchild

Question

I have the following hierarchy of objects in Entity Framework Code First:

class Parent {
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Child> Children { get; set; }
}

class Child { 
    public int Id { get; set; }
    public int ParentId { get; set; }

    public virtual ICollection<Grandchild> Grandchildren { get; set; }
}

class Grandchild {
    public string Name { get; set; }
    public int Age { get; set; }
}

I want to retrieve a list of parents and the age of their oldest grandchild. If I have 2 parents in my database, each parent has 2 children, and each child has 2 children:

Parent 1
    Child 1
        Grandchild 1 - Age 10
        Grandchild 2 - Age 15
    Child 2
        Grandchild 3 - Age 3
        Grandchild 4 - Age 7
Parent 2
    Child 3
        Grandchild 5 - Age 1
        Grandchild 6 - Age 9
    Child 4
        Grandchild 7 - Age 18
        Grandchild 8 - Age 16

I want the query to return two objects:

var expected = new List<anon>
{
    new { Name = "Parent 1", MaxAge = 15 },
    new { Name = "Parent 2", MaxAge = 18 }
};

How can I write this in LINQ to Entities?

Edit
Also, is it possible to return the grandchilds name along with their age? Basically have the max age of the grandchild and who that grandchild is?

I'm assuming it's possible for a child not to have a grandchild (null)? — James Michael Hare, Mar 12 '12 at 14:10
Also you say LINQ to Entities, but also database, I assume you've read these entities in and have stored them in a List or something? — James Michael Hare, Mar 12 '12 at 14:11
@JamesMichaelHare It is possible that a Parent would not have any children or grandchildren. — Dismissile, Mar 12 '12 at 14:12
@JamesMichaelHare Well, I don't think it would ever be null. I think LINQ to Entities would see that there are no children and just make an empty list instead of a null list. — Dismissile, Mar 12 '12 at 14:13

score 3 · Accepted Answer · answered Mar 12 '12 at 14:13

3

from parent in db.Parents
select new
{
    Name = parent.Name,
    MaxAge = (
        from child in parent.Children
        from grandchild in child.Grandchildren
        select grandchild.Age)
        .Max()
};

answered Mar 12 '12 at 14:13

Steven

166,672
24
332
435

score 2 · Answer 2 · answered Mar 12 '12 at 14:15

You can do this:

var query = from p in parents
            select
            {
              Name = p.Name,
              MaxAge = p.SelectMany(parent => parent.children)
                .SelectMany(child => child.children)
                .Max(i => i.Age)
            };

LINQ Max Grandchild

2 Answers2