Entity Framework Include Single Object from One-To-Many Relationship

Question

I'm trying to construct a single query to fetch the list of all Tour objects, with each tour's latest version preloaded (latest being most recently created). See the models below. Each Tour can have multiple associated TourVersion objects

class Tour {
    public Guid TourId { get; set; }
    public virtual TourVersion CurrentTourVersion { get; set; }
    // Other properties etc.
}
class TourVersion {
    public Guid TourId { get; set; }
    public DateTime CreatedOn { get; set; }
    // Other properties etc.
}

I'm able to accomplish what I want running another query for each Tour:

var tours = context.Tours.ToList();

foreach (var tour in tours)
{
    tour.CurrentTourVersion = context.TourVersions
        .Where(t => t.TourId == tour.Id)
        // ToUnixTime is a custom extension method that returns a long
        .OrderByDescending(t => t.CreatedOn.ToUnixTime())
        .FirstOrDefault();
}

I'd like to be able to accomplish this in a single query. Any suggestions?

Answer 1

Ended up solving it with this:

var tours = (from tour in context.Tours
             join v in context.TourVersions on tour.TourId equals v.TourId
             where v.CreatedOn == context.TourVersions.Select(b => b.CreatedOn).Max()
                select new
                {
                    TourId = tour.Id,
                    CurrentTourVersion = v
                }).ToList().Select(tour => new Tour
                {
                    TourId = tour.Id,
                    CurrentTourVersion = tour.CurrentTourVersion
                });

return tours.ToList();

Explanation of why this works:

• Joins each tour with the most recent version
  • Finds the most recent version by selecting version against maximum date
• Creates an anonymous object with the TourId and CurrentTourVersion fields
• Maps that to a list of Tour objects

EF has a restriction to force you to put the results of a join into an anonymous object. See this answer for more info