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?

You already have a `CurrentTourVersion` navigation property. That means EF will load it for you (either lazily or eagerly) — haim770, Nov 03 '16 at 18:53
Are you allowed to change your class structures? I think one-many is usually coded differently in EF code first — Anton Maiorov, Nov 03 '16 at 18:58
I don't think it will because each Tour has multiple TourVersions. How could EF know which of the multiple TourVersions to load? — Kev, Nov 03 '16 at 18:58
Your current class structure shows that each tour has a single tourversion. — Emad, Nov 03 '16 at 19:01
Each tour has multiple tourversions, but only ONE current Version (i.e. the latest one). The challenge here is to find the most recent version and prefetch it for each tour in a single query — Kev, Nov 03 '16 at 19:15

score 0 · Answer 1 · answered Nov 03 '16 at 19:06

Entity framework automatically loads related entities for you until the context is alive. You get the list of Tours by

var tours = dbContext.Tours.ToList();

for example and you can get the tour version by calling

tours[i].CurrentTourVersion;

Unless dbContext is disposed somewhere before you get tour version in that case you are going to need eager loading (see here). This way you get and keep the related objects.

var tours = dbContext.Tours.Include(obj=>obj.CurrentTourVersion).ToList();

score 0 · Accepted Answer · edited May 23 '17 at 12:24

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

This restriction doesn't exist. I'm sure you can do `select new Tour` at the first `select`. — Gert Arnold, Nov 04 '16 at 09:49

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

2 Answers2