How to return many Child Collections Count() in a single entity framework query.

Question

I have two entities, Parent and Child, in Entity Framework.

The parent has a collection of Child entities.

In my query, I want to return only the Parent entities (the fully typed EF types) and also the Count() of the Child entities (this could be set to a property on the Parent), but I only want to do this in one call to the database, without writing a custom S-Proc. Is this possible?

Essential, I want to turn this into a single query:

EFContext content = new EFContext();
IQueryable<Parent> parentQuery = context.Parent.Select();
foreach(Parent parent in parentQuery)
{
  parent.NoChildItems = parent.Childs.Count();
}

When I activate the enumerator on this this, it calls the database for the list, and again for each Count() query. I return approx 100 items each time and so would rather not make 100 seperate calls just for the number of child items.

Thanks for any help.

Answer 1

This should work:

IQueryable parentQuery = context.Parent.Select(p => new { Parent = p, ChildCount = p.Childs.Count() });

EDIT

If you define:

public class ParentModel
{
    public Task Parent { get; set; }
    public int ChildCount { get; set; }
}

you can use

IQueryable parentQuery = context.Parent.Select(p => new ParentModel { Parent = p, ChildCount = p.Childs.Count() });

EDIT

You can also do:

var parentQuery = context.Parent.Select(p => new { Parent = p, ChildCount = p.Childs.Count() });
parentQuery.ForEach(p => p.Parent.ChildCount = p.ChildCount);
var result = return parentQuery.Select(p => p.Parent);

Short and you have your property populated.