Advanced LINQ Query Help

Question

So I have the following LINQ query:

var qryVans = from v in objContext.vans

                join s in objContext.schools on v.schoolID equals s.schoolID
                join l in objContext.locations on v.lastKnownLocationID equals l.locationID

                select new DisplayVan {
                    vanID = v.vanID,
                    vanName = v.vanName,
                    phone = v.phone,
                    capacity = (int)v.capacity,
                    schoolName = s.schoolName,
                    lastLocationName = l.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 a in v.vanAssignments
                                    where a.vanID == v.vanID
                                    select a
                };

All works fine, except I'm needing to fill the navigation properties of the VanAssignment entity. Notice, in my DisplayVan projection, I am using a nested query to gather the van assignments for the given van. The vanAssignment entity has a person entity. So how would I load the vanAssignment.person property in this query?

Also, could I write this to be more efficient?

Thanks for your help!

EDIT

Here is where my code above fails:

After I execute a toList() on the query above, I'll try to access the needed person like this:

List<DisplayVan> lstVans = qryVans.toList<DisplayVan>();
foreach(DisplayVan objVan in lstVans) {

   Console.WriteLine(objVan.person.firstName);

}

Now, since I didn't load in the person entity with the vanAssignment entity, the person navigation property is null and it throws an error.

My question centers around the correct way to load in this person entity along with vanAssignment?

Hope that helps.

Answer 1

Since DisplayVan sounds like a DTO, why don't you select a DisplayVanAssignment object that explicitly references the person properties you need.

Answer 2

You could have something like this for person assignment

...
 modifiedDate = v.modifiedDate,
 vanAssignments = GetVanAssignmentsWithPerson(v.vanAssignments.where(a => a.VanID == v.vanID), person)
...

private static IEnumerable<Assignment> GetVanAssignmentsWithPerson(IEnumerable<Assignment> assignments, Person p)
{
      foreach(var assignment in assignments)
      {
           assignment.person = p;
      }
}

Answer 3

I am not Linq to sql expert, but found this:

DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Assignment>(a => a.Person);
objContext.LoadOptions = dataLoadOptions;

Setting DataLoadOptions would make Person eager load with Assignment.

EDIT

This work in EF for sure, but should also work in Linq to SQL:

(from a in v.vanAssignments
where a.vanID == v.vanID
select new { Assignment = a, Person = a.Person }).Select(i => i.Assignment);