Entity Framework 6.1.3: Projection - load children of children directly

Question

My objective is to make only one trip to the database, and get Orders with a filtered child collection.

To achieve this, I've used a projection:

using (var db = new context())
            {
                var query = from o in db.Orders
                           select
                               new
                               {
                                   Order = o,
                                   Events = o.Events.Where(
                                       e => e.SomeBool 
                                       && e.SomeBool2
                                   ),

                                   EventsGroups = o.Events.Where(
                                       e => e.SomeBool 
                                       && e.SomeBool2
                                   ).Select(e => e.Groups),

                               };
            }

The issue here is that the child collection, "Groups" on events, is not loaded. To solve this, I have loaded it as another property "EventsGroups" in the query, which I can then put together with the Events afterwards.

My Question: Is there a way to load the child, "Groups" onto the "Event" directly, so I do not have to get them as another property?

Along the lines of

Events = o.Events.Where(
e => e.SomeBool 
&& e.SomeBool2
).Include(e => e.Groups), //this cannot be done this way

---

Why am i using a projection and not eager loading:

https://msdn.microsoft.com/en-us/magazine/hh205756.aspx

Filtering include items in LINQ and Entity Framework

The underlying classes:

public class Order
{
    public Order()
    {
        Events = new HashSet<Event>();
    }

    public int Id { get; set; }

    public virtual ICollection<Event> Events { get; set; }
}

public class Event
{
    public Event()
    {
        Groups = new HashSet<Group>();
    }

    public int Id { get; set; }

    public bool SomeBool { get; set; }

    public bool SomeBool2 { get; set; }

    public virtual ICollection<Group> Groups { get; set; }
}

public class Group
{
    public Group()
    {
        Events = new HashSet<Event>();
    }

    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Event> Events { get; set; }
}

Answer 1

I believe this should work for you:

using (var db = new context())
{
  var query = db.Orders
    .Include(o=>o.Events)
    .Include(o=>o.Events.Select(e=>e.Groups))
    .Select(o=>new
    {
      Order = o,
      Events = o.Events.Where(e => e.SomeBool && e.SomeBool2)
    });
}

if not, this will:

using (var db = new context())
{
  var query = db.Orders
    .Include(o=>o.Events)
    .Include(o=>o.Events.Select(e=>e.Groups))
    .Select(o=>new Order
    {
      Id=o.Id,
      Events = o.Events.Where(e => e.SomeBool && e.SomeBool2).ToList()
    });
}

Answer 2

you can to load everything by setting Configuration.LazyLoadingEnabled = true; , without need for any Include , of course... perfoamnce must set in mind.. depends..

    public MyDatabaseContext(string databaseName)
        : base(databaseName)
    {
        Configuration.LazyLoadingEnabled = true;
    }

Answer 3

Since Include cannot be combined with anonymous projection, you need to project Event.Groups the same way you project Order.Events, i.e. using another anonymous type projection:

var query = from o in db.Orders
            select new
            {
                Order = o,
                Events = (from e in o.Events
                          where e.SomeBool && e.SomeBool2
                          select new
                          {
                              Event = e,
                              Groups = e.Groups,
                          }),
            };