Entity Framework - Eager load two many-to-many relationships

Question

Sorry for this being so long, but at least I think I got all info to be able to understand and maybe help?

I would like to load data from my database using eager loading.

The data is set up in five tables, setting up two Levels of m:n relations. So there are three tables containing data (ordered in a way of hierarchy top to bottom):

CREATE TABLE [dbo].[relations](
    [relation_id] [bigint] NOT NULL
)

CREATE TABLE [dbo].[ways](
    [way_id] [bigint] NOT NULL
)

CREATE TABLE [dbo].[nodes](
    [node_id] [bigint] NOT NULL,
    [latitude] [int] NOT NULL,
    [longitude] [int] NOT NULL
)

The first two really only consist of their own ID (to hook other data not relevant here into).

In between these three data tables are two m:n tables, with a sorting hint:

CREATE TABLE [dbo].[relations_ways](
    [relation_id] [bigint] NOT NULL,
    [way_id] [bigint] NOT NULL,
    [sequence_id] [smallint] NOT NULL
)

CREATE TABLE [dbo].[ways_nodes](
    [way_id] [bigint] NOT NULL,
    [node_id] [bigint] NOT NULL,
    [sequence_id] [smallint] NOT NULL
)

This is, essentially, a part of the OpenStreetMap data structure. I let Entity Framework build it's objects from this database and it set up the classes exactly as the tables are. The m:n tables do really exist as class. (I understand in EF you can build your objects m:n relation without having the explicit in-between class - should I try to change the object model in this way?)

What I want to do: My entry point is exactly one item of relation.

I think it would be best to first eager load the middle m:n relation, and then in a loop iterate over that and eager load the lowest one. I try to do that in the following way

IQueryable<relation> query = context.relations;
query = query.Where( ... ); // filters down to exactly one
query = query.Include(r => r.relation_members);
relation rel = query.SingleOrDefault();

That loads the relation and all it's 1:n info in just one trip to the database - ok, good. But I noticed it only loads the 1:n table, not the middle data table "ways".

This does NOT change if I modify the line like so:

query = query.Include(r => r.relation_members.Select(rm => rm.way));

So I cannot get the middle level loaded here, it seems?

What I cannot get working at all is load the node level of data eagerly. I tried the following:

foreach (relation_member rm in rel.relation_members) {
    IQueryable<way_node> query = rm.way.way_nodes.AsQueryable();
    query = query.Include(wn => wn.node);
    query.Load();
}

This does work and eagerly loads the middle level way and all 1:n info of way_node in one statement for each iteration, but not the Information from node (latitude/longitude). If I access one of these values I trigger another trip to the database to load one single node object.

This last trip is deadly, since I want to load 1 relation -> 300 ways which each way -> 2000 nodes. So in the end I am hitting the server 1 + 300 + 300*2000... room for improvment, I think.

But how? I cannot get this last statement written in valid syntax AND eager loading. Out of interest; is there a way to load the whole object graph in one trip, starting with one relation?

Answer 1

Loading the whole graph in one roundtrip would be:

IQueryable<relation> query = context.relations;
query = query.Where( ... ); // filters down to exactly one
query = query.Include(r => r.relation_members
    .Select(rm => rm.way.way_nodes
        .Select(wn => wn.node)));
relation rel = query.SingleOrDefault();

However, since you say that the Include up to ...Select(rm => rm.way) didn't work it is unlikely that this will work. (And if it would work the performance possibly isn't funny due to the complexity of the generated SQL and the amount of data and entities this query will return.)

The first thing you should investigate further is why .Include(r => r.relation_members.Select(rm => rm.way)) doesn't work because it seems correct. Is your model and mapping to the database correct?

The loop to get the nodes via explicit loading should look like this:

foreach (relation_member rm in rel.relation_members) {
    context.Entry(rm).Reference(r => r.way).Query()
        .Include(w => w.way_nodes.Select(wn => wn.node))
        .Load();
}