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.
精彩评论