Using Linq to SQL, how do I Eager Load all child and any nested children results

Question

I have 5 tables in a L2S Classes dbml : Global >> Categories >> ItemType >> Item >> ItemData. For the below example I have only gone as far as 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();"
This line gets executed numerous times in the foreach, and a query is executed to fetch the results, and I understand why to an extent, but I thought it would eager load on Loadwith as an option, as in fetch everything with one query.

So basically I would have expected L2S behind the scenes to fetch the "global" records in one query, take any primary key values, get the "category" children using one one query. Take those results and stick them into collections linked to the global. Then take all the category keys and excute one query to fetch the itemtype children and link those into their associated collections. Something on the order of (Select * from ItemTypes Where CategoryID in ( select categoryID from Categories where GlobalID in ( #,#,# ))

I would like to know how to properly eager load associated children with minimal queries and possibly how to accomplish my routine generically not knowing how far down I need to build the hierarchy, but given a parent entity, grab all the associated child collections and then do what I need to do.

Answer 1

Use of ToList() may be all that you need. At the end of a Linq query, simply add ".ToList()". This will cause the query to execute and it will return a List of the results. You can continue to perform Linq operations as before, but it may confuse things in some LinqToObjects and LinqToSql combinations.

Answer 2

Linq to SQL has some limitations with respect to eager loading.

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.

For details see:

http://www.lowendahl.net/showShout.aspx?id=190

Answer 3

I am sure this could be done better, but I got my code working with minimal queries. One per level. This is obviously not really eager loading using L2S, but if someone knows the right way I would like to know for future reference.

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

    var globalList = query.ToList();

    List<Category> categoryList = g.category.ToList<Category>();

    var categoryIds = from c in cdc.Category
                where c.globalId == g.globalId
                select c.categoryId;

    var types = from t in cdc.ItemTypes
                where categoryIds.Any(i => i == t.categoryId)
                select t;

    List<ItemType> TypeList = types.ToList<ItemType>();

    var items = from i in cdc.Items
                from d in cdc.ItemData
                where i.ItemId == d.ItemId && d.labelId == 1
                where types.Any(i => i == r.ItemTypes)
                select new 
                    {
                        i.Id, 
                        // A Bunch of more fields shortened for berevity
                        d.Data    
                    };

    var ItemList = items.ToList();

    // Keep on going down the hierarchy if you need more child results
    // Do your processing psuedocode
    for each item in list
        filter child list
        for each item in child list
            .....
    //

Wouldn't mind knowing how to do this all using generics and a recursive method given the top level table