c# – 使用Linq to SQL,如何Eager加载所有子项和任何嵌套子项结果

我在L2S Classes dbml中有5个表：Global分类 ItemType项目的ItemData.对于下面的例子,我只去了itemtype.//cdc is my datacontextDataLoadOptions options = new DataLoadOptions();options.LoadWithGlobal...

我在L2S Classes dbml中有5个表：Global>>分类>> ItemType>>项目>>的ItemData.对于下面的例子,我只去了itemtype.

    //cdc is my datacontext

DataLoadOptions options = new DataLoadOptions();

options.LoadWith<Global>(p => p.Category);
options.AssociateWith<Global>(p => p.Category.OrderBy(o => o.SortOrder));
options.LoadWith<Category>(p => p.ItemTypes);
options.AssociateWith<Category>(p => p.ItemTypes.OrderBy(o => o.SortOrder));

cdc.LoadOptions = options;

TraceTextWriter traceWriter = new TraceTextWriter();
cdc.Log = traceWriter;

var query =
from g in cdc.Global
where g.active == true && g.globalid == 41
select g;

var globalList = query.ToList();

// In this case I have hardcoded an id while I figure this out
// but intend on trying to figure out a way to include something like globalid in (#,#,#)
foreach (var g in globalList)
{

   // I only have one result set, but if I had multiple globals this would run however many times and execute multiple queries like it does farther down in the hierarchy 
    List<Category> categoryList = g.category.ToList<Category>();

    // Doing some processing that sticks parent record into a hierarchical collection

    var categories = (from comp in categoryList
        where comp.Type == i 
        select comp).ToList<Category>();

    foreach (var c in categories)
    {
        // Doing some processing that stick child records into a hierarchical collection
        // Here is where multiple queries are run for each type collection in the category
        // I want to somehow run this above the loop once where I can get all the Items for the categories
        // And just do a filter

        List<ItemType> typeList = c.ItemTypes.ToList<ItemType>();

        var itemTypes = (from cat in TypeList
                where cat.itemLevel == 2
                select cat).ToList<ItemType>();

        foreach (var t in itemTypes)
        {
           // Doing some processing that stick child records into a hierarchical collection                            
        }
    }
}

“List typeList = c.ItemTypes.ToList();”
这行在foreach中被执行多次,并且执行查询以获取结果,并且我理解为什么在某种程度上,但我认为它会急于加载Loadwith作为选项,就像用一个查询获取所有内容一样.

所以基本上我会期望L2S在幕后获取一个查询中的“全局”记录,获取任何主键值,使用一个查询获取“类别”子项.获取这些结果并将其粘贴到与全球相关的集合中.然后获取所有类别键并执行一个查询以获取itemtype子项并将其链接到其关联的集合中.按顺序排列的东西(从ItemTypes中选择*其中的CategoryID(从类别中选择categoryID in(#,#,#))

我想知道如何正确地用最少的查询来加载关联的子项,以及可能如何完成我的例程,一般不知道我需要多长时间来构建层次结构,但是给定父实体,抓住所有关联的子集合然后执行我需要做什么.

解决方法:

Linq to SQL在急切加载方面有一些限制.

So Eager Load in Linq To SQL is only
eager loading for one level at a time.
As it is for lazy loading, with Load
Options we will still issue one query
per row (or object) at the root level
and this is something we really want
to avoid to spare the database. Which
is kind of the point with eager
loading, to spare the database. The
way LINQ to SQL issues queries for the
hierarchy will decrease the
performance by log(n) where n is the
number of root objects. Calling ToList
won’t change the behavior but it will
control when in time all the queries
will be issued to the database.

详情见：

http://www.cnblogs.com/cw_volcano/archive/2012/07/31/2616729.html