LinqConnect Documentation
In This Topic
    Filtering Related Entities
    In This Topic

    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:


    C#csharpCopy Code
    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();
    Visual BasicCopy Code
    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:


    C#csharpCopy Code
    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();
    Visual BasicCopy Code
    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):


    C#csharpCopy Code
    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();
    Visual BasicCopy Code
    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:

     

     

    © 2010 - 2024 Devart. All Rights Reserved.

    [email protected]