如何使用单个LINQ查询将所有父对象选择到DataContext中? [英] How to select all parent objects into DataContext using single LINQ query?

问题描述

我正在寻找使用单个SELECT提取整个LINQ对象层次结构的特定问题的答案.

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

起初，我试图使用LoadOptions填充尽可能多的LINQ对象，但AFAIK此方法仅允许使用LoadWith在一个查询中链接单个表.因此，我发明了一种解决方案，用于强制设置要获取列表的实体的所有父对象，尽管存在将多个SELECTS存入数据库的问题-单个查询会导致两个SELECTS在相同的LINQ上下文中具有相同的参数

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

问题是为什么要进行两个查询?如何在没有此类黑客的情况下获取LINQ对象?

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

推荐答案

为什么不多次调用LoadWith?

在DataLoadOptions文档中说:

每次调用LoadWith都会检查循环是否具有[...]

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

(在避免循环的部分.)

(In the section on avoiding cycles.)

这篇关于如何使用单个LINQ查询将所有父对象选择到DataContext中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！