How to select all parent objects into DataContext using single LINQ query?

I am looking for an answer to a specific problem of fetching whole LINQ object hierarchy using single SELECT.

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.

For this question I have simplified this query to popular invoice example:

public static class Extensions
{
        public static IEnumerable<T> ForEach<T>(this IEnumerable<T> collection, Action<T> func)
        {
            foreach(var c in collection)
            {
                func(c);
            }
            return collection;
        }
}

public IEnumerable<Entry> 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 =>
        {
            x.Entry.Invoice = Invoice;
            x.Invoice.Period = Period;
            x.Entry.Store = Store;
        }
    ).Select(x => x.Entry);
}

When calling this function and traversing through result set, for example:

var entries = GetResults(this.Context);
int withoutStore = 0;
foreach(var k in entries)
{
    if(k.EntryStoreId  == null)
        withoutStore++;
}

the resulting query to database looks like (single result is fetched):

SELECT
    [t0].[EntryId], 
    [t0].[EntryInvoiceId],开发者_如何学C 
    [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

The question is why there are two queries and how can I fetch LINQ objects without such hacks?

---

Why not call LoadWith multiple times?

In the DataLoadOptions documentation it says:

Each call to LoadWith checks whether cycles have [...]

(In the section on avoiding cycles.)

---

Well... I think you’re traversing through your “queryable” twice: in the ForEach extension and in the “imperative” foreach () block... Have you tried changing the implementation of the ForEach to...

    public static IEnumerable<T> ForEach<T>(this IEnumerable<T> collection, Action<T> func)
    {
        foreach (var c in collection)
        {
            func(c);
            yield return c;
        }
    }

---

For others seeking an exact solution for this problem, please consider following shortened and working version of the code for fetching LINQ object hierarchy in single SELECT. I have changed return type of GetResults function to IQueryable as the collection can be properly tracked by LINQ change tracking mechanisms allowing to update database with changes in collection.

public void InitContext(AppDataContext context)
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Entry>(x => x.Invoice);
    options.LoadWith<Entry>(x => x.Store);
    options.LoadWith<Invoice>(x => x.Period);
    context.DataLoadOptions = options;
}

public IQueryable<Entry> 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 entry
    );
}