Entity Framework not including children in join-CodePudding

I am using inner join to return results with Entity Framework (v6.2.0) and the following code is not returning the RouteWaypoints children (i.e. route.RouteWaypoints is always null). Interestingly, single children are loading (Customer, OriginLocation, etc), but not multiple children:

public List<Route> GetAllForTripWithWaypoints(int tripId)
{
    return (
        from route in GetAllBaseWithWaypoints()
        from tripTask in DbContext.TripTasks.Where(x =>
            x.TripId == tripId && x.OriginLocationId == route.OriginLocationId)
        select route
    ).ToList();
}

private IQueryable<Route> GetAllBaseWithWaypoints()
{
    return DbContext.Routes
        .Include(x => x.Customer)
        .Include(x => x.OriginLocation)
        .Include(x => x.DestinationLocation)
        .Include(x => x.RouteWaypoints.Select(y => y.Location))
        .OrderBy(x => x.OriginLocation.Name).ThenBy(x => x.DestinationLocation.Name)
        .AsQueryable();
}

This approach does work if I load just the Route entity, but not when I do the join. As a reference, this does load the children successfully:

public Route GetByIdWithWaypoints(int id, bool validateExists = true)
{
    var route = GetAllBaseWithWaypoints().FirstOrDefault(x => x.Id == id);
    if (validateExists && route == null)
        throw new Exception("Route not found for id: "   id);

    return route;
}

How can I keep it working when joining?

CodePudding user response：

I did an imperfect workaround by making two calls to the db - slightly less efficient, but it solves the problem:

public List<Route> GetAllForTripWithWaypoints(int tripId)
{
    var routeIds = (
        from route in GetAllBase()
        from tripTask in DbContext.TripTasks.Where(x =>
            x.TripId == tripId && x.OriginLocationId == route.OriginLocationId)
        select route.Id
    ).ToList();

    return GetAllBaseWithWaypoints().Where(x => routeIds.Contains(x.Id)).ToList();
}