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
– Slauma, Commented Jan 10, 2012 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
– gruve, Commented Jan 10, 2012 at 19:06
Just use modelBuilder.Entity<Trip>().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
– Slauma, Commented Jan 10, 2012 at 19:18
@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: stackoverflow.com/a/7053393/270591 — Slauma
– Slauma, Commented Jan 10, 2012 at 19:58

tyron · Accepted Answer · 2012-01-10 17:24:47Z

14

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.

edited Jan 10, 2012 at 17:24

answered Jan 10, 2012 at 17:17

tyron

3,8851 gold badge24 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gruve Over a year ago

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).

Francisco Goldenstein · Accepted Answer · 2014-06-04 15:55:55Z

0

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.

answered Jun 4, 2014 at 15:55

Francisco Goldenstein

13.9k7 gold badges63 silver badges75 bronze badges

Comments

dotnetftw · Accepted Answer · 2016-07-13 13:24:04Z

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, 2016 at 13:24

dotnetftw

136 bronze badges

Collectives™ on Stack Overflow

Entity Framework - eager loading of related entities

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related