Return parent and children in single entity framework query WITHOUT returning IQueryable or IEnumerable?

Question

We have a rule to not expose IQueryable<T> or IEnumerable<T> outside of the service layer so downstream code can't modify the query going to the database. This means that the types we return are like IList or ICollection.

I'm wondering how I might write a linq query to grab a parent and it's children in a single trip to the database without the child collections being defined as IQueryable or IEnumerable.

For example, suppose the type being returned from the service is ICollection<Parent> where Parent is defined as this:

public class Parent
{
    public int ParentId { get; set; }
    public string ParentName { get; set; }
    public ICollection<Child> Children { get; set; }
}

If I define the query as this...

from p in dbContext.Parents
where p.Name.Contains("test")
select new Parent
{
    Children =from c in dbContext.Children
    where c.ParentId == p.ParentId
    select c;
}

It doesn't compile because IQueryable doesn't implement ICollection. If I add .ToList() to the parent and child collections, it compiles, but now it will do a separate trip to the database for each parent to get it's children.

As I'm writing this question it occurred to me that maybe the answer is to write the Linq query to select into anonymous types and then map it to the actual types to return. We use AutoMapper which could assist in the mapping. Any reason why this wouldn't work? Would I have to call .ToList() before mapping the objects to ensure I don't run into the same problem while mapping?

Answer 1

If you have a navigation property in your Parent entity like this:

public class Parent
{
    public int ParentId { get; set; }
    public string ParentName { get; set; }
    public virtual ICollection<Child> Children { get; set; }
   //..
}

Then I suggest you create two new classes in your service layer (could be ParentViewModel, and ChildViewModel) to save the result of your query. In both classes declare only the properties that you need in your presentation layer. Then map your entities with your ViewModel classes using Automapper.

After that you can do a query like this:

var query =dbContext.Parents
                    .Include(p=>p.Children) // load the related entities as part of the query
                    .Where(p=>p.ParentName.Contains("test"))
                    .ProjectTo<ParentViewModel>();

Use ProjectTo extension method of Automapper.

As you can see in the link I quoted above, Automapper supports Nested Mappings, so if you have in your ParentViewModel a property of type ICollection<ChildViewModel> and you also mapped Child entity with its ViewModel, then Automapper will helpfully automatically attempt to map from one type to the other.

All this is going to happen in one round trip to your Database, because ProjectTo is an IQueryable<TEntity> extension method and it's translated to a Select.