linq: grandparent - parent - children query

Question

Hi,

I have an entity model and I've been looking to write a linq query that returns counts of children and parents for each grandparent.

I need to output 3 columns: Name of Grandparent | Count of Children | Count of Grandchildren

protected void Page_Load(object sender, EventArgs e)
{
     using (FamilyModel.TheConn myEntities = new FamilyModel.TheConn())
     {
          int TheUserID = 13; // will change later

          var myOutput = from gparents in myEntities.GrandParents
                     where gparents.UserID == TheUserID
                     select gparent.Name, // here's what's missing: the counts

          GridView1.DataSource = myOutput;
          GridView1.DataBind();

     }
}

I've been struggling with SelectMany, Groupby, joins.... I just don't get the result I need for this seemingly simple query. Any input would be greatly appreciated.

Thanks

Answer 1

var myOutput = from gparent in myEntities.GrandParents
               where gparent.UserID == TheUserID
               select new GrandParentViewModel
               {
                   Name = gparent.Name,
                   ChildrenCount = gparent.Children.Count(),
                   GrandChildrenCount = gparent.Children.SelectMany(c => c.GrandChildren).Count()
               };

That's assuming your Child entity has navigational property GrandChildren (actually the name Children world make more sense here - children of children = grandchildren).

In this case we project onto a GrandParentViewModel:

public class GrandParentViewModel
{
    public string Name { get; set; }
    public int ChildrenCount { get; set; }
    public int GrandChildrenCount { get; set; }
}