title: "Deep loading" with LoadWith
body: <p>I have a table structure, each table with a primary key field named as "ID" and foreign key names matching their parent table's primary key. Therefore, the tables below have relationships where their primary key appears in another table and the first field in any table is it's primary key:</p>
  
  <pre><code>Category
  --------
  CategoryID
  Title
  
  
  CategoryList
  ------------
  CategoryListID
  CategoryID
  ListID
  
  
  List
  ----
  ListID
  Title
  
  
  DataPoint
  ---------
  DataPointID
  RecordedDateTime
  
  
  DataPointValue
  --------------
  DataPointValueID
  DataPointID
  TheValue
  </code></pre>
  
  <p>The above is a many-to-many join between Category and List, via CategoryList. It is also a one-to-many join from List to DataPoint, DataPoint to DataPointValue.</p>
  
  <p>Using C#/LINQ and given a List of the CategoryID values, I would like to retrieve:</p>
  
  <p>All the List entries attached to the Category I have ID's for. With those List entries, I would like to take the most recent 1 DataPoint, as ordered by RecordedDateTime Descending. From there I would like to retrieve every DataPointValue attached to the DataPoint.</p>
  
  <p>The LINQ I have is:</p>
  
  <p>DBDataContext context = new DBDataContext(ConnectionString);</p>
  
  <pre><code>        context.LoadOptions = new DataLoadOptions();
          context.LoadOptions.LoadWith&lt;DataPoint&gt;(p =&gt; p.DataPoints.OrderByDescending(p.RecordedDataTime).FirstOrDefault());
  
          // this next line is how I get the list of category IDs, but don't worry about that...
          List&lt;int&gt; categoryIDs = (from TreeNode n in nodes
                                   select Int32.Parse(n.Value)).Distinct().ToList();
  
          var lists = from i in context.List
                                           join ci in context.CategoryLists on i.ListID equals ci.ListID
                                           join p in context.DataPoints on i.ListID equals p.ListID
                                           join v in context.DataPointValues on p.DataPointID equals v.DataPointID
                                           where categoryIDs.Contains(ci.CategoryID)
                                           orderby i.Title ascending
                                           select new
                                           {
                                               List = i,
                                               DataPoint = p,
                                               DataPointValues = p.DataPointValues
                                           };
  </code></pre>
  
  <p>But this is obviously not working - the LoadWith is causing me issues. Could someone explain how to construct the LoadWith so that it will cause as few SQL queries as possible to retrieve this (admittedly large) amount of data, please?</p>
  
  <p>Many thanks,</p>
  
  <p>Matt.</p>