title: How to select all parent objects into DataContext using single LINQ query?
body: <p>I am looking for an answer to a specific problem of fetching whole LINQ object hierarchy using single SELECT.</p>
  
  <p>At first I was trying to fill as much LINQ objects as possible using LoadOptions, but AFAIK this method allows only single table to be linked in one query using LoadWith. So I have invented a solution to forcibly set all parent objects of entity which of list is to be fetched, although there is a problem of multiple SELECTS going to database - a single query results in two SELECTS with the same parameters in the same LINQ context.</p>
  
  <p>For this question I have simplified this query to popular invoice example:</p>
  
  <pre><code>public static class Extensions
  {
          public static IEnumerable&lt;T&gt; ForEach&lt;T&gt;(this IEnumerable&lt;T&gt; collection, Action&lt;T&gt; func)
          {
              foreach(var c in collection)
              {
                  func(c);
              }
              return collection;
          }
  }
  
  public IEnumerable&lt;Entry&gt; GetResults(AppDataContext context, int CustomerId)
  {
      return
      (
          from entry in context.Entries
          join invoice in context.Invoices on entry.EntryInvoiceId equals invoice.InvoiceId
          join period in context.Periods on invoice.InvoicePeriodId equals period.PeriodId
          // LEFT OUTER JOIN, store is not mandatory
          join store in context.Stores on entry.EntryStoreId equals store.StoreId into condStore
          from store in condStore.DefaultIfEmpty()
          where
              (invoice.InvoiceCustomerId = CustomerId)
          orderby entry.EntryPrice descending
          select new
          {
              Entry = entry,
              Invoice = invoice,
              Period = period,
              Store = store
          }
      ).ForEach(x =&gt;
          {
              x.Entry.Invoice = Invoice;
              x.Invoice.Period = Period;
              x.Entry.Store = Store;
          }
      ).Select(x =&gt; x.Entry);
  }
  </code></pre>
  
  <p>When calling this function and traversing through result set, for example:</p>
  
  <pre><code>var entries = GetResults(this.Context);
  int withoutStore = 0;
  foreach(var k in entries)
  {
      if(k.EntryStoreId  == null)
          withoutStore++;
  }
  </code></pre>
  
  <p>the resulting query to database looks like (single result is fetched):</p>
  
  <pre><code>SELECT
      [t0].[EntryId], 
      [t0].[EntryInvoiceId], 
      [t0].[EntryStoreId],
      [t0].[EntryProductId],
      [t0].[EntryQuantity],
      [t0].[EntryPrice],
      [t1].[InvoiceId], 
      [t1].[InvoiceCustomerId],
      [t1].[InvoiceDate],
      [t1].[InvoicePeriodId],
      [t2].[PeriodId], 
      [t2].[PeriodName], 
      [t2].[PeriodDateFrom],
      [t4].[StoreId],
      [t4].[StoreName]
  FROM
      [Entry] AS [t0]
      INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId]
      INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId]
  LEFT OUTER JOIN (
      SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName]
      FROM [Store] AS [t3]
      ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId])
  WHERE (([t1].[InvoiceCustomerId]) = @p0)
  ORDER BY [t0].[InvoicePrice] DESC
  -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186]
  -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
  
  SELECT
      [t0].[EntryId], 
      [t0].[EntryInvoiceId], 
      [t0].[EntryStoreId],
      [t0].[EntryProductId],
      [t0].[EntryQuantity],
      [t0].[EntryPrice],
      [t1].[InvoiceId], 
      [t1].[InvoiceCustomerId],
      [t1].[InvoiceDate],
      [t1].[InvoicePeriodId],
      [t2].[PeriodId], 
      [t2].[PeriodName], 
      [t2].[PeriodDateFrom],
      [t4].[StoreId],
      [t4].[StoreName]
  FROM
      [Entry] AS [t0]
      INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId]
      INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId]
  LEFT OUTER JOIN (
      SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName]
      FROM [Store] AS [t3]
      ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId])
  WHERE (([t1].[InvoiceCustomerId]) = @p0)
  ORDER BY [t0].[InvoicePrice] DESC
  -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186]
  -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
  </code></pre>
  
  <p>The question is why there are two queries and how can I fetch LINQ objects without such hacks?</p>