However, even with an association, the associated data is not loaded when the query is executed. For example, suppose we retrieve all of the categories like this:

var categories = from c in db.Categories select c;

Later on, if we need to get products for each category, the database has to be queried again. The following diagram shows the result of executing the query:

From this diagram, we know that LINQ first goes to the database to query all of the categories. Then, for each category, when we need to get the total count of products, it goes to the database again to query all of the products for that category.

This is because by default lazy loading is set to true, meaning that the loading of all associated data (children) is deferred until the data is needed.

To change this behavior, we can use the LoadWith method to tell the DataContext to automatically load the specified children during the initial query:

// eager loading products of categories
DataLoadOptions dlo2 = new DataLoadOptions();
dlo2.LoadWith<Category>(c => c.Products);
// create another data context, because we can't change LoadOptions of db
// once a query has been executed against it
NorthwindDataContext db2 = new NorthwindDataContext();
db2.Log = Console.Out;
db2.LoadOptions = dlo2;
var categories2 = from c in db2.Categories select c;
foreach (var category2 in categories2)
{
Console.WriteLine("There are {0} products in category {1}", category2.Products.Count(), category2.CategoryName);
}
db2.Dispose();

Note that DataLoadOptions is in the namespace System.Data.Linq. So you have to add a using statement to the program:

using System.Data.Linq;

Also, we have to create a new DataContext instance for this test, because we have run some queries in the original db DataContext, and it is no longer possible to change its LoadOptions.

Now, after the category is loaded, all of its children (products) will be loaded too. This can be confirmed in the following image:

As you can see from this image, all products for all categories are loaded during the first query.

While LoadWith is used to eager load all children, AssociateWith can be used to filter the children that are to be loaded. For example, if we only want to load products for categories 1 and 2, we can use this query:

// eager loading only certain children
DataLoadOptions dlo3 = new DataLoadOptions();
dlo3.AssociateWith<Category>(c => c.Products.Where(p => p.CategoryID == 1 || p.CategoryID == 2));
// create another data context, because we can't change LoadOptions of db
// once query has been executed against it
NorthwindDataContext db3 = new NorthwindDataContext();
db3.LoadOptions = dlo3;
db3.Log = Console.Out;
var categories3 = from c in db3.Categories select c;
foreach (var category3 in categories3)
{
Console.WriteLine("There are {0} products in category {1}", category3.Products.Count(), category3.CategoryName);
}
db3.Dispose();

Now, if we query all of the categories, and print out the product count for each category, we will find that only the first two categories contain products, and all other categories have no products at all, as seen in the following image:

However, from the output above, you can see that this uses lazy loading. If you want the eager loading of products with some filters, you can combine LoadWith and AssociateWith, as shown in the following code:

DataLoadOptions dlo4 = new DataLoadOptions();
dlo4.LoadWith<Category>(c => c.Products);
dlo4.AssociateWith<Category>(c => c.Products.Where(p => p.CategoryID == 1 || p.CategoryID == 2));
// create another data context, because we can't change LoadOptions of db
// once q query has been executed
NorthwindDataContext db4 = new NorthwindDataContext();
db4.Log = Console.Out;
db4.LoadOptions = dlo4;
var categories4 = from c in db4.Categories select c;
foreach (var category4 in categories4)
{
Console.WriteLine("There are {0} products in category {1}", category4.Products.Count(), category4.CategoryName);
}
db4.Dispose();

The output of this query is shown in the following image: