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],
[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?
