Adding a second query inside the SELECT of a LINQ to Entities query gives Not Recognized Method error

Question

I have built a LINQ query for to fill one of my ViewModel with data but inside the .SELECT() method I tried to add another query to populate one of the VM's properties. When I ran the page I got the error:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[FFInfo.DAL.Location] Include[Location](System.Linq.IQueryable1[FFInfo.DAL.Location], System.String)' method, and this method cannot be translated into a store expression.

The reason I am using the second query is because LocationTransitionPoints does link directly to Locations (via ToLoation and FromLocation) but it does not link directly to Section and I don't know how to work my way up to it (LocationTranitionPoints -> Locations -> Section)

I have a felling I am just missing some stupid little thing but can't place my finger on it, any suggestions please?

        public ActionResult TransitionPoints()
    {
        try
        {
            using (var db = new GeographyContext())
            {
                var model = db.LocationTransitionPoints
                              .Include("FromLocation")
                              .Include("ToLocation")
                              .Select(ltp => new TransitionPointsVM()
                              {
                                  FromLocation = ltp.FromLocation.Name,
                                  ID = ltp.ID,
                                  SectionTitle = db.Locations.Where(l => l.ID == ltp.ToLocationID).Include("Section").Select(l => l.Section.Title).First(),
                                  ToLocation = ltp.ToLocation.Name,
                                  TransitionPoint = ltp.TransitionPoint
                              }).ToList();

                return View(model);
            }
        }
        catch (Exception ex)
        {
            ErrorSignal.FromCurrentContext().Raise(ex);
            return PartialView("LoadError");
        }
    }

Answer 1

If you haven't disabled lazy loading you should try with the solution that was proposed by @Peter. Otherwise, you can also load the Section nav property as part of your query, including it in the path that you pass as parameter in the Include method:

 using (var db = new GeographyContext())
 {
      var model = db.LocationTransitionPoints
                    .Include("FromLocation")
                    .Include("ToLocation.Section")
                    .Select(ltp => new TransitionPointsVM()
                    {
                      FromLocation = ltp.FromLocation.Name,
                      ID = ltp.ID,
                      SectionTitle = ltp.ToLocation.Section.Title, 
                      ToLocation = ltp.ToLocation.Name,
                      TransitionPoint = ltp.TransitionPoint
                    }).ToList();
           //...
}

You can load deeper levels following this pattern:

Include("FirstLevelNavProp.SecondLevelNavProp...")

But a better idea is use this another Include extension method which is strongly typed:

 using (var db = new GeographyContext())
 {
      var model = db.LocationTransitionPoints
                    .Include(ltp=>ltp.FromLocation)
                    .Include(ltp=>ltp.ToLocation.Section)
                    .Select(ltp => new TransitionPointsVM()
                    {
                      FromLocation = ltp.FromLocation.Name,
                      ID = ltp.ID,
                      SectionTitle = ltp.ToLocation.Section.Title, 
                      ToLocation = ltp.ToLocation.Name,
                      TransitionPoint = ltp.TransitionPoint
                    }).ToList();
            //...
}

Answer 2

Could you just do something like this?

var model = db.LocationTransitionPoints
  .Select(ltp => new TransitionPointsVM()
  {
      FromLocation = ltp.FromLocation.Name,
      ID = ltp.ID,
      // get to Section through ToLocation
      SectionTitle = ltp.ToLocation.Section.Title,
      ToLocation = ltp.ToLocation.Name,
      TransitionPoint = ltp.TransitionPoint
  }).ToList();

Also, I cut the .Include() parts as they're not really necessary since you're projecting into the view model.