LINQ to Entities: Loading One-To-Many Navigation Properties in Strong Typed Projections

Here the relevant part of my model:

And here the code from my model class:

/// <summary>
/// Retrieves a list of vans for binding with the BeachReach Snapshot
/// grid. Note: This method uses a POCO class that does not participate
/// in management by the entity context. See the DisplayVan class for more info
/// </summary>
/// <param name="setVanIDs">
/// You may limit which vans will be returned by passing a hash
/// set of their van IDs here
/// </param>
/// <returns>List of type DisplayVan - Unmanaged</returns>
public static List<DisplayVan> GetVansForSnapshot(HashSet<int> setVanIDs = null) {

    using (BeachReachDataEntities objContext = new BeachReachDataEntities()) {

        var qryVans = from v in objContext.vans
                                .Include("school")
                                .Include("location")

                      select new DisplayVan {
                          vanID = v.vanID,
                          vanName = v.vanName,
                          phone = v.phone,
                          capacity = (int)v.capacity,
                          schoolName = v.school.schoolName,
                          lastLocationName = v.location.locationName,
                          statusNote = v.statusNote,
                          isOffline = (v.isOffline == 1) ? true : false,
                        开发者_如何学运维  isPrayerRoom = (v.isPrayerRoom == 1) ? true : false,
                          isNotReady = (v.isNotReady == 1) ? true : false,
                          creationDate = v.creationDate,
                          modifiedDate = v.modifiedDate,
                          vanAssignments = (from va in objContext.vanAssignments
                                            from pr in objContext.pickupRequests
                                            where va.vanID == v.vanID
                                            where pr.pickupRequestID == va.pickupRequestID
                                            select va)
                      };

        if (setVanIDs != null && setVanIDs.Count > 0)
            return qryVans.Where(v => setVanIDs.Contains(v.vanID)).ToList<DisplayVan>();
        else
            return qryVans.ToList<DisplayVan>();

    }

}

// --------------------------------------------------------

I have also tried:

        var qryVans = from v in objContext.vans
                                .Include("school")
                                .Include("location")
                                .Include("vanAssignments")
                                .Include("vanAssignments.pickupRequest")


                      select new DisplayVan {
                          vanID = v.vanID,
                          vanName = v.vanName,
                          phone = v.phone,
                          capacity = (int)v.capacity,
                          schoolName = v.school.schoolName,
                          lastLocationName = v.location.locationName,
                          statusNote = v.statusNote,
                          isOffline = (v.isOffline == 1) ? true : false,
                          isPrayerRoom = (v.isPrayerRoom == 1) ? true : false,
                          isNotReady = (v.isNotReady == 1) ? true : false,
                          creationDate = v.creationDate,
                          modifiedDate = v.modifiedDate,
                          vanAssignments = v.vanAssignments
                      };

Ignore the is**** properties. I'm using a MySQL database which doesn't support boolean types.

The query must project onto DisplayVan instead of the van entity because I have some computed properties that I'm binding to the datagrid.

The problem is when I try to access the pickupRequest navigation property of the vanAssignment entity. No matter what I do, the pickupRequest property is null. Note, I do have lazy loading off. How can I load the vanAssignment's pickupRequest relation via my query above?

One last note: the vanAssignments property is a List of type vanAssignment. Thanks for any insight you can lend to this issue.

Edit

One additional question. So taking from your example, I wanted to go to vanAssignment.person.firstName. I tried this:

        var qryVans = (from v in objContext.vans
                       select new {
                           DisplayVan = new DisplayVan {
                               vanID = v.vanID,
                               vanName = v.vanName,
                               phone = v.phone,
                               capacity = (int)v.capacity,
                               schoolName = v.school.schoolName,
                               lastLocationName = v.location.locationName,
                               statusNote = v.statusNote,
                               isOffline = (v.isOffline == 1) ? true : false,
                               isPrayerRoom = (v.isPrayerRoom == 1) ? true : false,
                               isNotReady = (v.isNotReady == 1) ? true : false,
                               creationDate = v.creationDate,
                               modifiedDate = v.modifiedDate,
                               vanAssignments = v.vanAssignments
                           },
                           PickupRequests = v.vanAssignments.Select(va => va.pickupRequest),
                           People = v.vanAssignments.Select(va => va.pickupRequest.person)
                       }).ToList().Select(e => e.DisplayVan);

When I try to access that property, I get the following error:

---

This should work:

var qryVans = 
    (from v in objContext.vans
    select new
        {
            DisplayVan = new DisplayVan
            {
                vanID = v.vanID,
                vanName = v.vanName,
                phone = v.phone,
                capacity = (int)v.capacity,
                schoolName = v.school.schoolName,
                lastLocationName = v.location.locationName,
                statusNote = v.statusNote,
                isOffline = (v.isOffline == 1) ? true : false,
                isPrayerRoom = (v.isPrayerRoom == 1) ? true : false,
                isNotReady = (v.isNotReady == 1) ? true : false,
                creationDate = v.creationDate,
                modifiedDate = v.modifiedDate,
                vanAssignments = v.vanAssignments
            },
            PickupRequests = v.vanAssignments.Select(va => new {PickupReuqest = va.pickupRequest, Person = va.pickupRequest.Person})
        }
    ).ToList().Select(e => e.DisplayVan);

Includes are ignored in this construction, so you should leave them. You should specify with additional property, that you want pickupRequests too. ToList() executes query taking demanded data and since all required pickupRequests are in context, you will have correct fields populated. EF analyzes fields, that you requests in your view model and prepares sql statement, that will take them.