Entity Framework - eager loading of related entities

Question

Sorry the title isn't more specific - I didn't know how to describe this succinctly. I have Trips and Location that have a many-to-many relationship - straightforward except that Locations have no need to know about the Trips that use them. I've created these entities to represent this:

public class Trip
{
    public int TripId { get; set; }
    public virtual IList<TripLocation> TripLocations { get; set; }
}

public class TripLocation
{
    public int TripId { get; set; }
    public int LocationId { get; set; }

    public virtual Location Location { get; set; }
}

public class Location
{
    public int LocationId { get; set; }
    // Note: Intentionally no collection of Trips
}

I can get the Trip to eager load it's TripLocations but I can't get the TripLocations to eager load their Locations. I've tried a bunch of combinations fluent configuration and "Include"ing in the query such as

IQueryable<Trip> query = from trip in context
                              .Include(r =>r.TripLocations)
                              .Include(r => r.TripLocations.Select(tl => tl.Location))
                         select ride;

Any suggestions much appreciated!

Why do you have the `TripLocation` entity in your model? Are there other properties in this entity which you are not showing? Otherwise you can remove this entity from your model altogether and create a many-to-many relationship directly between `Trip` and `Location`. In your current model you actually have two one-to-many and not a many-to-many relationship. — Slauma, Jan 10 '12 at 18:25
TripLocation is there because I don't want a Collection of Trips in Location and I couldn't figure out how to use fluent configuration to do a many-to-many without it. Since @tyron's answer helped me get this working as is, I'm going to go back and see if I can do what you suggest. — gruve, Jan 10 '12 at 19:06
Just use `modelBuilder.Entity().HasMany(t => t.Locations).WithMany().Map(...);`. Using `WithMany()` without parameter is for the case when you don't have a `Trips` collection in `Location`. — Slauma, Jan 10 '12 at 19:18
@Slauma - Thanks, I didn't know I could do that! It worked great. — gruve, Jan 10 '12 at 19:39
I thought you were using TripLocation because it could have some other properties on this entity. Indeed, that's what I'm trying to do in my own project, and wasn't able to achieve it yet. If @Slauma has some material on this subject, I'd appreciate it. — tyron, Jan 10 '12 at 19:51
@tyron: I am not sure if I understand you correctly but I meant: If you **have no** additional properties in the join table you can create a many-to-many relationship without the intermediate entity (`TripLocation`). If you **have** such additional properties you can't create a many-to-many relation at all, you must create two one-to-many relationship and expose the join table as an entity in the model: http://stackoverflow.com/a/7053393/270591 — Slauma, Jan 10 '12 at 19:58
@Slauma: I understood the difference, and in my project, I have additional properties, but I'm having some troubles while using the join entity. The example you procided seems very good, I'll take a better look at it. — tyron, Jan 11 '12 at 13:53
Can you please clarify whether this kind of eager loading is only needed if lazy loading is disabled? If lazy loading is enabled, can you directly call something like `var locId = trip.TripLocations.First().Location.LocationId` and EF takes care of it? — Ε Г И І И О, Sep 28 '12 at 07:10

tyron · Accepted Answer · 2012-01-10T17:24:47.153

I recreated your scenario here and I was able to get all the results in a single query.

var a = from trip in context.Trips.Include("TripLocations.Location")
        select trip;

That's all. That's what was queried against my database:

SELECT 
[Project1].[TripId] AS [TripId], 
[Project1].[Name] AS [Name], 
[Project1].[C1] AS [C1], 
[Project1].[TripId1] AS [TripId1], 
[Project1].[LocationId] AS [LocationId], 
[Project1].[LocationId1] AS [LocationId1], 
[Project1].[Name1] AS [Name1]
FROM ( SELECT 
    [Extent1].[TripId] AS [TripId], 
    [Extent1].[Name] AS [Name], 
    [Join1].[TripId] AS [TripId1], 
    [Join1].[LocationId1] AS [LocationId], 
    [Join1].[LocationId2] AS [LocationId1], 
    [Join1].[Name] AS [Name1], 
    CASE WHEN ([Join1].[TripId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Trips] AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Extent2].[TripId] AS [TripId], [Extent2].[LocationId] AS [LocationId1], [Extent3].[LocationId] AS [LocationId2], [Extent3].[Name] AS [Name]
        FROM  [dbo].[TripLocations] AS [Extent2]
        INNER JOIN [dbo].[Locations] AS [Extent3] ON [Extent2].[LocationId] = [Extent3].[LocationId] ) AS [Join1] ON [Extent1].[TripId] = [Join1].[TripId]
)  AS [Project1]
ORDER BY [Project1].[TripId] ASC, [Project1].[C1] ASC

UPDATE:

If you wanna keep with the lambda version, this will do the work:

IQueryable<Trip> query = from ride in context.Set<Trip>()
                             .Include(t=>t.TripLocations.Select(l=>l.Location))                                     
                         select ride;

More information on the MSDN blog.

Thank you! Your answer confirmed that I was on the right track and got me to look at the actual code from a different perspective (the example above is just what I thought the relevant parts of actual code are). — gruve, Jan 10 '12 at 18:54

score 0 · Answer 2 · answered Jun 04 '14 at 15:55

Regarding the lambda expression, you can use context.Set like @tyron said or you can use context.Trips. For example:

IQueryable<Trip> query = from ride in context.Trips
                             .Include(t=>t.TripLocations.Select(l=>l.Location))                                     
                         select ride;

In order to make this code work, you need to define a property of type DbSet in your DbContext class like below:

public DbSet<Trip> Trips { get; set; }

Defining a property that returns DbSet is nice but at the same time it's equivalent to accesing context.Set. It's just a code style that could also be combined.

score 0 · Answer 3 · answered Jul 13 '16 at 13:24

0

Remove the VIRTUAL keyword on your relationship properties e.g.Location, that will disable Lazy Loading and force you to eager load.

answered Jul 13 '16 at 13:24

dotnetftw

13
6

Entity Framework - eager loading of related entities

3 Answers3

Linked