C# – How to return many Child Collections Count() in a single entity framework query

centity-frameworklinq-to-entitiesnet

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.

Best Answer

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() }).ToList();
parentQuery.ForEach(p => p.Parent.ChildCount = p.ChildCount);
var result = return parentQuery.Select(p => p.Parent);

Short and you have your property populated.

Related Topic