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

Question

I am looking for an answer to the specific problem of retrieving the entire hierarchy of LINQ objects using a single SELECT.

At first I tried to populate as many LINQ objects as possible with LoadOptions, but AFAIK this method allows you to link only one table in a single query using LoadWith. Therefore, I came up with a decision to force all the parent entities of the entity to be selected from the list, although there is a problem with several SELECTS entering the database - one query results in two SELECTS with the same parameters in the same LINQ context.

On this question, I simplified this request to a popular example of an account:

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 crossing the result set, for example:

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

( ):

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

, LINQ ?

Answer 1

LoadWith ?

DataLoadOptions :

LoadWith , [...]

( .)

Answer 2

... , "" : ForEach "" foreach() ... ForEach ...

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

Answer 3

For others looking for the exact solution to this problem, consider the following shortened and working version of the code to get a hierarchy of LINQ objects in one SELECT. I changed the return type of the GetResults function to IQueryable, since the collection can be correctly tracked using LINQ change tracking mechanisms that allow updating the database with changes to the 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
    );
}