In one of the above examples, we retrieved the category name of a product using this expression:
p.Category.CategoryName == "Beverages"
Even though there is no such field called categoryname
in the Products
table, we can still get the category name of a product because there is an association between Products
and Category
table. In the Northwind.dbml design pane, click on the line that connects the Products table and the Categories table and you will see all of the properties of the association. Note that its participating properties are Category.CategoryID -> Product.CategoryID
, meaning that category ID is the key field to link these two tables.
Because of this association, we can retrieve the category for each product, and on the other hand, we can also retrieve products for each category.
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();