title: Entity Framework - Eager load two many-to-many relationships
body: <p>Sorry for this being so long, but at least I think I got all info to be able to understand and maybe help?</p>
  
  <p>I would like to load data from my database using eager loading.</p>
  
  <p>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):</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>The first two really only consist of their own ID (to hook other data not relevant here into).</p>
  
  <p>In between these three data tables are two m:n tables, with a sorting hint:</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>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?)</p>
  
  <p><br /><br /><br /><b>What I want to do:</b> My entry point is exactly one item of relation.</p>
  
  <p>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</p>
  
  <pre><code>IQueryable&lt;relation&gt; query = context.relations;
  query = query.Where( ... ); // filters down to exactly one
  query = query.Include(r =&gt; r.relation_members);
  relation rel = query.SingleOrDefault();
  </code></pre>
  
  <p>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".</p>
  
  <p>This does NOT change if I modify the line like so:</p>
  
  <pre><code>query = query.Include(r =&gt; r.relation_members.Select(rm =&gt; rm.way));
  </code></pre>
  
  <p>So I cannot get the middle level loaded here, it seems?</p>
  
  <p>What I cannot get working at all is load the node level of data eagerly. I tried the following:</p>
  
  <pre><code>foreach (relation_member rm in rel.relation_members) {
      IQueryable&lt;way_node&gt; query = rm.way.way_nodes.AsQueryable();
      query = query.Include(wn =&gt; wn.node);
      query.Load();
  }
  </code></pre>
  
  <p>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 <b>not</b> 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.</p>
  
  <p>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.</p>
  
  <p>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?</p>