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; }
}

Do you mean inside the projection? I do not believe Include exists there http://imgur.com/ZOOXx0E — Archigo, Apr 18 '16 at 12:18

score 1 · Accepted Answer · answered Apr 18 '16 at 12:03

1

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,
                          }),
            };

answered Apr 18 '16 at 12:03

Ivan Stoev

195,425
15
312
343

This is much more beatiful than my way of doing it, and easier to put together. I suspect this is a close as I can get to avoiding loops to construct the DTO objects from the anynomous objects. – Archigo Apr 18 '16 at 12:36
If you do have a DTO objects, you can project directly to them instead of anonymous types - EF supports that. – Ivan Stoev Apr 18 '16 at 12:50

score 0 · Answer 2 · answered Apr 18 '16 at 12:11

0

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;
    }

answered Apr 18 '16 at 12:11

Zakos

1,492
2
22
41

This breaks with my objective of only having one trip to the database – Archigo Apr 18 '16 at 12:29

Robert McKee · Answer 3 · 2016-04-18T14:48:03.497

0

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()
    });
}

edited Apr 18 '16 at 14:48

answered Apr 18 '16 at 14:38

Robert McKee

21,305
1
43
57

This did not work while testing. Groups did not get loaded. Getting orders and the filtered events works, but there is just an empty list for groups on the events. The includes do not seems to be doing anything, as it is the same result with or without them. – Archigo Apr 18 '16 at 19:37
Hmm. Interesting. A simple fix is to add `.ToList()` between the Include and Select. That will definitely work, however, you will be retrieving all the event records, not just ones that match your `.Where` conditions. – Robert McKee Apr 18 '16 at 19:42

Entity Framework 6.1.3: Projection - load children of children directly

3 Answers3