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?

Possible duplicate of [Entity Framework recursively include collection for each entity from included collection](http://stackoverflow.com/questions/24945115/entity-framework-recursively-include-collection-for-each-entity-from-included-co) — Joshua Shearer, Jan 15 '16 at 16:02
I can understand not wanting to expose `IQueryable`, but I don't see the logic in not exposing `IEnumerable`. For a start, both your alternatives implement it. — Charles Mager, Jan 15 '16 at 16:14
Also, your example doesn't compile (collection has no name, your selector doesn't specify any properties). It looks like your requirement could be met my just returning `dbContext.Parents.Include(x => x.Children).Where(x => x.Name.Contains("test"))`, so it perhaps doesn't really illustrate your point. — Charles Mager, Jan 15 '16 at 16:18
Why not just wrap the `IEnumerable` so that it can't be cast back to a queryable type? `.AsEnumerable().Skip(0)` for example, though one could easily provide a single-call method to do the same. — Jon Hanna, Jan 15 '16 at 16:31
@JoshuaShearer, it is not a duplicate. I know how to include children. The question is about how to do a single trip to the database WITHOUT making the return type something that can modify the query. — adam0101, Jan 15 '16 at 16:58
@CharlesMager, implementing IEnumerable and setting it as the return type are two different things. EF only hits the database once you starting iterating over the collection. If you allow IEnumerable to be the return type, a developer could alter the query outside the service before the collection is iterated. — adam0101, Jan 15 '16 at 17:01
@adam0101 that's not true. And query tacked on to the `IEnumerable` will be done in memory. See [this related question](http://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet). — Charles Mager, Jan 15 '16 at 17:28
@CharlesMager, no I can take an `IEnumerable`, cast it back to IQueryable using `AsQueryable()`, add additional criteria, call `.ToList()` and I can see the criteria in the SQL using SQL Profiler. Perhaps that link only pertains to Linq-To-Sql, not EF? — adam0101, Jan 15 '16 at 18:06
Actually, after testing it, it does apply to EF too, but only if a developer doesn't cast it back to an IQueryable. — adam0101, Jan 15 '16 at 18:17
@JonHanna, that's an option, but it's easier to enforce if you require a fully executed collection as the return type. That way you don't have to know the implementation details and could even write a custom check-in policy. — adam0101, Jan 18 '16 at 14:39
@JonHanna, how else would a code reviewer be able to determine if the IEnumerable represents a collection that cannot be cast back into IQueryable? If you use a type that definitely can't, then you remove all doubt. — adam0101, Jan 18 '16 at 20:38
Then call the wrapper `TypeThatDefinitelyCantBeCastBackToIQuery`. Better than wasting time and memory memory on collections you don't need. — Jon Hanna, Jan 18 '16 at 22:06

ocuenca · Accepted Answer · 2016-01-15T16:55:07.670

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.

Please, the person who downvote, could you explain why to improve my answer? — ocuenca, Jan 15 '16 at 17:34
Maybe, *maybe* because `ProjectTo` returns an `IQueryable`, which the OP doesn't want. But I think that's nitpicking. — Gert Arnold, Jan 15 '16 at 21:30

score 1 · Answer 2 · answered Oct 06 '19 at 17:01

You can do this with plain ol' EF and no AutoMapper using an Anon query internally, but still get strongly typed objects returned in the end result:

public class Human
{
    public int Id { get; set; }
    public ICollection<Human> Children { get; set; }
    public Human Parent { get; set; }
    [ForeignKey("Parent")]
    public int ParentId { get; set; }
}

var family = await db.Humans
    .Where(h => h.SomeCriteriaForParent == criteria)
    .Select(h => new {
        H = h,
        HH = h.Children
    })
    .SelectMany(x => x.HH.Concat(new[] { x.H }))
    .ToArrayAsync();

If for example your criteria was Id and your Id was say, 5, you'd get the parent with Id 5 and all of its children, in a single Human[] array.

Important note on the SelectMany: You may want to use Select/GroupBy here instead.

If you use SelectMany, and your criteria select only one parent: You will get the parent and all of its children in a single array. (This was my use-case.)

If you use SelectMany, and your criteria select multiple parents: You will get the parents and all their children in a single array.

If above and parents who are children (grandparents/grandchildren) meet the criteria: You will get the full family tree in a single array and you will likely get dupes if you don't add .Distinct().

If you use Select: You will get sets of children separated out by parent, into multiple arrays. This was not my use case, but I suspect if it's yours what you actually want here is a GroupBy.

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

2 Answers2