Let's suppose a navigation property may link this particular entity object to multiple related entities. For example, in the CRM Demo sample database, the product category and the corresponding products are the case. Suppose that we need to work with only a part of those related entities, and this part can be described by a foreknown condition.

In this case, we would have to apply this condition to the navigation property each time we access this property. For example, we look for products that cost less than 50$, querying the 'novel' product category, and all related products that satisfy this condition would look like:

CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };

var pcQuery = from pc in context.Productcategories
              where pc.CategoryName == "novel"
              select pc;
var novelCategory = pcQuery.SingleOrDefault();
var pQuery = from p in novelCategory.Products
             where p.Price <= 50
             select p;
var novels = pQuery.ToList();

Dim context As New CrmDemoDataContext With { .Log = Console.Out }
Dim pcQuery = From pc In context.Productcategories _
              Where pc.CategoryName = "novel" _
              Select pc
Dim novelCategory = pcQuery.SingleOrDefault()
Dim pQuery = From p In novelCategory.Products _
             Where p.Price <= 50 _
             Select p
Dim novels = pQuery.ToList()

This is fine, but if later we do the same for the 'detective story' category, we have to write the price condition again:

pcQuery = from pc in context.Productcategories
          where pc.CategoryName == "detective story"
          select pc;
var detectiveCategory = pcQuery.SingleOrDefault();
pQuery = from p in detectiveCategory.Products
         where p.Price <= 50
         select p;
var detectives = pQuery.ToList();

pcQuery = From pc In context.Productcategories _
          Where pc.CategoryName = "detective story" _
          Select pc
Dim detectiveCategory = pcQuery.SingleOrDefault()
pQuery = From p In detectiveCategory.Products _
         Where p.Price <= 50 _
         Select p
Dim detectives = pQuery.ToList()

Obviously, this is not the best practice, especially if the filter condition is complicated and tends to change. In such situations it is recommended to set the filter on the data context level. As with the eager loading configuration, this is done via the LoadOptions property of DataContext (the AssociateWith method is used this time):

CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };

DataLoadOptions options = new DataLoadOptions();
options.AssociateWith<Productcategory>(
    pc => pc.Products.Where(
      p => p.Price <= 50)
);
context.LoadOptions = options;

var pcQuery = from pc in context.Productcategories
              where pc.CategoryName == "novel"
              select pc;
var novelCategory = pcQuery.SingleOrDefault();
var novels = novelCategory.Products.ToList();

pcQuery = from pc in context.Productcategories
          where pc.CategoryName == "detective story"
          select pc;
var detectiveCategory = pcQuery.SingleOrDefault();
var detectives = detectiveCategory.Products.ToList();

Dim context As New CrmDemoDataContext With { .Log = Console.Out }
Dim options As New DataLoadOptions()
options.AssociateWith(Of Productcategory)(Function(pc) _
    pc.Products.Where(Function(p) p.Price <= 50))
context.LoadOptions = options

Dim pcQuery = From pc In context.Productcategories _
              Where pc.CategoryName = "novel" _
              Select pc
Dim novelCategory = pcQuery.SingleOrDefault()
Dim novels = novelCategory.Products.ToList()

pcQuery = From pc In context.Productcategories _
          Where pc.CategoryName = "detective story" _
          Select pc
Dim detectiveCategory = pcQuery.SingleOrDefault()
Dim detectives = detectiveCategory.Products.ToList()

In this sample, we set a condition on related products via the DataLoadOptions class, and then get all related products for one of product categories. In fact, only those products satisfying the 'AssociateWith' condition are retrieved (check the WHERE clause of the generated SQL). This is done for all queries involving the 'Products' navigation property of the ProductCategory entity class. Even if some other products were already fetched, they won't be exposed by the Products property.

There are several restrictions related to using the DataLoadOptions class and its AssociateWith method:

• The assigned DataLoadOptions object cannot be modified (i.e., you cannot invoke LoadWith or AssociateWith on it);
• After any query was executed via a DataContext instance, you cannot assign another DataLoadOptions to the LoadOptions property of this instance.
• Only expressions like "entity => entity.[RelatedEntities].Where([expression])" can be passed to the AssociateWith method.