Conditional IQueryable Selecting (MVC .NET EF)

Question

I am trying to select multiple children when loading some data but I would like to put conditional statements around the includes.

At the moment I have many different selects in different methods working with 1 specific grandparent object but would like to put them into one select in a

ShapeResults(this IQueryable<SpecificObject>, bool includeParent, bool includeChildren)

method and all my methods point to that with conditions / filters.

What I am doing and works great:

var query = context.Grandparent.Select(i => new GrandparentObject
{
    GrandparentProp1 = i.GrandparentProp1 ,
    Parents = i.Parents.Select(j => new ParentObject
    {
        ParentProp1 = j.ParentProp1,
        Children = j.Children.Select(k => new ChildObject
        {
            ChildProp1 = k.ChildProp1,
        }
    }
}

what I would in essence like to do

var query = context.Grandparent.Select(i => new GrandparentObject
{
    GrandparentProp1 = i.GrandparentProp1,
--> if (IncludeParents)
    Parents = i.Parents.Select(j => new ParentObject
    {
        ParentProp1 = j.ParentProp1,
    --> if (IncludeParentsChildren)
        Children = j.Children.Select(k => new ChildObject
        {
            ChildProp1 = k.ChildProp1,
        }
    }
}

Thanks in advance!

Answer 1

Adam, I've been working on something similar and would recommend something like this:

var query = context.Grandparent
                   .Select(poco => new 
                    {
                        poco     = poco,
                        parents  = poco.Parents.Where(x => includeParents)
                        children = poco.Children.Where(x => includeChildren) 
                    })
                   .Select(x => new GrandparentObject
{
    GrandparentProp1 = x.poco.GrandparentProp1,
    Parents          = x.parents.Select(parent => new ParentObject
                       {
                           ParentProp1 = parent .ParentProp1,
                           Children    = x.children.Select(child => new ChildObject
                                         {
                                             ChildProp1 = child.ChildProp1,
                                         }
                       }
}

I found using a Ternary operator proved difficult. Using an additional select statement for your conditional joins seems to be the best approach.

Important Note: .Where(x => includeChildren) vs .Where(x => false) will produce a different SQL query.

EF will still execute a join that returns nothing if a variable (includeChildren) is used, but will skip the join altogether if a constant (false) is used.

Answer 2

How about using the conditional operator like this?

var query = context.Grandparent.Select(i => new GrandparentObject
{
    GrandparentProp1 = i.GrandparentProp1,
    Parents = includeParents
        ? i.Parents.Select(j => new ParentObject
          {
              ParentProp1 = j.ParentProp1,
              Children = includeChildren
                  ? j.Children.Select(k => new ChildObject
                    {
                         ChildProp1 = k.ChildProp1
                    }
                  : Enumerable.Empty<Child>()
          }
        : Enumerable.Empty<Parent>()
};

You could also use null in place of Enumerable.Empty<TResult>(), depending on what you want the semantics of the returned results to be.

---

EDIT: Just realized I was completely overthinking how complex this needs to be. Try it like this:

var query = context.Grandparent.Select(i => new GrandparentObject
{
    GrandparentProp1 = i.GrandparentProp1 ,
    Parents = i.Parents
        .Where(_ => includeParents)
        .Select(j => new ParentObject
    {
        ParentProp1 = j.ParentProp1,
        Children = j.Children
            .Where(_ => includeChildren)
            .Select(k => new ChildObject
        {
            ChildProp1 = k.ChildProp1,
        }
    }
}