LinqConnect Documentation
In This Topic
    Eager Loading
    In This Topic

    Opposite to lazy loading, the eager loading pattern is used to initialize the object as soon as it is created. Applied to ORM entity objects, this means fetching all fields and related entities upon creating this particular entity.

    Fields

    By default, eager loading is enabled for all entity fields. There may be reasons to change this behaviour for some of them, see more in the Lazy Loading topic.

    Relationships

    Navigation properties of LinqConnect associations are lazy loaded by default (unless you are working with POCO classes which don't support lazy loading). However, there may be reasons for using eager loading instead: for example, if you do know that you will be working with the related entities, you want to load them at once to do less roundtrips to the server. Or, the entity objects should be serialized to be passed through a service to a remote machine; in this case, you should have the whole entity graph at once.

    There are two ways of using eager loading of related entities in LinqConnect: at the context level and at the query level. To configure eager loading at the context level, you initialize the LoadOptions property of the DataContext instance. To use eager loading for a single query only, you invoke the LoadWith extension method on your query object or the LoadWith method of a Devart.Data.Linq.Table object.

    We will demonstrate both ways on the following entity classes (generated for the 'Products', 'Product Categories' and 'Order Details' tables of the CRM Demo sample database):

    Eager Loading via LoadOptions

    The LoadOptions property of the DataContext class is of the DataLoadOptions type. It provides the LoadWith<TEntity> generic method, with which you can specify the navigation properties that should be eagerly loaded with the entities of the TEntity type. After invoking the LoadWith method and setting the LoadOptions property of your DataContext, the specified navigation property will be loaded for all TEntity objects retrieved by all queries executed via this DataContext instance.

    There are the following restrictions related to using the DataLoadOptions class and its LoadWith method:

    In the small example below, we show how to enable eager loading on the context level. We query for the 'poem' product category, and becaused of the LoadOptions configuration, all related products are retrieved with the ProductCategory object:


    C#csharpCopy Code
    CrmDemoDataContext context =
    new CrmDemoContext.CrmDemoDataContext() { Log = Console.Out };
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Productcategory>(pc => pc.Products);
context.LoadOptions = options;
var query = from category in context.Productcategories
            where category.CategoryName == "poem"
            select category;
Productcategory poetryCategory = query.SingleOrDefault();
    Visual BasicCopy Code
    Dim context As New CrmDemoDataContext() _
    With {.Log = Console.Out}
Dim options As New DataLoadOptions
options.LoadWith(Of Productcategory)( _
    Function(pc As CrmDemoContext.Productcategory) pc.Products)
context.LoadOptions = options
Dim query = From category In context.Productcategories _
            Where category.CategoryName = "poem" _
            Select category
Dim poetryCategory = query.SingleOrDefault()

    To check that the products are loaded with the category, you can access the Products property of the poetryCategory object. Usually, an additional query is executed against the database at this point, but this is not the case when LoadOptions are configured. We enable logging in the sample so that you can check the query executed against the server:

    SELECT t1."CategoryID", t1."CategoryName", t1."ParentCategory", 
t3."ProductID", t3."ProductName", t3."CategoryID" AS "CategoryID1",
t3."UnitName", t3."UnitScale", t3."InStock", t3."Price", t3."DiscontinuedPrice"
FROM (
    SELECT t2."CategoryID", t2."CategoryName", t2."ParentCategory"
    FROM CRM_DEMO."Product Categories" t2
    WHERE t2."CategoryName" = :p0
    ) t1
LEFT OUTER JOIN CRM_DEMO."Products" t3 ON t1."CategoryID" = t3."CategoryID"
ORDER BY t1."CategoryID"

    Without LoadOptions, the following query would be executed:

    SELECT t1."CategoryID", t1."CategoryName", t1."ParentCategory"
FROM CRM_DEMO."ProductCategories" t1
WHERE t1."CategoryName" = :p0

    LoadOptions can be configured for loading several navigation properties of the entity class (regardless of their multiplicity) and loading 'nested' navigation properties (i.e., navigation properties of dependent entities). For example, the following code retrieves products consisting of a single item, the categories they belong to, and all orders (including their details) including these products:


    C#csharpCopy Code
    CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Product>(p => p.Productcategory);
options.LoadWith<Product>(p => p.Orderdetails);
options.LoadWith<Orderdetail>(od => od.Order);
context.LoadOptions = options;
var query = from product in context.Products
            where product.UnitName == "unit"
            select product;
var unitProducts = query.ToList();
    Visual BasicCopy Code
    Dim context As New CrmDemoDataContext
context.Log = Console.Out
Dim options As New DataLoadOptions()
options.LoadWith(Of Product)(Function(p) p.Productcategory)
options.LoadWith(Of Product)(Function(p) p.Orderdetails)
options.LoadWith(Of Orderdetail)(Function(od) od.Order)
context.LoadOptions = options
Dim query = From product In context.Products _
            Where product.UnitName = "unit" _
            Select product
Dim unitProducts = query.ToList()

    Eager loading via LoadWith

    Besides LoadOptions, LinqConnect provides the query-level LoadWith method, which is used to configure eager loading of related entities for this particular query only. This is an extension method declared in the Devart.Data.Linq namespace, so you should add the proper 'using' ('imports' in VB) directive to use this method.

    For example, the code, loading the 'poem' category and all related products, would look like follows:


    C#csharpCopy Code
    CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };

var categoriesWithLoadOptions = context.Productcategories.LoadWith(pc => pc.Products);
var query = from category in categoriesWithLoadOptions
            where category.CategoryName == "poem"
            select category;
var poetryCategory = query.SingleOrDefault();
    Visual BasicCopy Code
    Dim context As New CrmDemoDataContext() With { _
  .Log = Console.Out _
}
 
Dim categoriesWithLoadOptions = context.Productcategories.LoadWith(Function(pc) pc.Products)
Dim query = From category In categoriesWithLoadOptions _
            Where category.CategoryName = "poem" _
            Select category
Dim poetryCategory = query.SingleOrDefault()

    By querying for another product category, you can ensure that these eager loading options are not applied to further queries (e.g., you can check the SQL generated):


    C#csharpCopy Code
    query = from category in categoriesWithLoadOptions
        where category.CategoryName == "novel"
        select category;
var novelCategory = query.SingleOrDefault();
    Visual BasicCopy Code
    query = From category In categoriesWithLoadOptions _
        Where category.CategoryName = "novel" _
        Select category
Dim novelCategory = query.SingleOrDefault()

    The LoadWith method of the 'query' can be used to load more than one navigation property and 'nested' properties as well. For example, to load several properties you can just continue invoking LoadWith on the result of the previous LoadWith invocation, and call it on the navigation property to load 'nested' properties:


    C#csharpCopy Code
    CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };
var productsWithLoadOptions =
    context.Products
        .LoadWith(p => p.Productcategory)
        .LoadWith(p => p.Orderdetails
        .LoadWith(od => od.Order)
    );
var query = from product in productsWithLoadOptions
            where product.UnitName == "unit"
            select product;
var unitProducts = query.ToList();
    Visual BasicCopy Code
    Dim context As New CrmDemoDataContext
context.Log = Console.Out
Dim productsWithLoadOptions = _
    context.Products _
        .LoadWith(Function(p) p.Productcategory) _
        .LoadWith(Function(p) p.Orderdetails _
        .LoadWith(Function(od) od.Order))
Dim query = From product In productsWithLoadOptions _
            Where product.UnitName = "unit" _
            Select product
Dim unitProducts = query.ToList()

    In the sample, all products consisting of a single item (measured in "units") are retrieved with the related category and order objects.

    The LoadWith method can also take a string parameter specifying the name of the eagerly loaded property. For example, the above code can be rewritten as:


    C#csharpCopy Code
    CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };

var productsWithLoadOptions =
    context.Products
        .LoadWith("ProductCategory")
        .LoadWith("OrderDetails.Order");
var query = from product in productsWithLoadOptions
            where product.UnitName == "unit"
            select product;
var unitProducts = query.ToList();
    Visual BasicCopy Code
    Dim context As New CrmDemoDataContext With { .Log = Console.Out }
Dim productsWithLoadOptions = _
    context.Products _
        .LoadWith("ProductCategory")
        .LoadWith("OrderDetails.Order")
Dim query = From product In productsWithLoadOptions _
            Where product.UnitName = "unit" _
            Select product
Dim unitProducts = query.ToList()

    The LoadWith method should be used only on IEnumerable<TEntity> instances that belong to either of the following groups:

    Fetch Modes

    Provided that eager loading is enabled, the related entities may be fetched in two different ways. The first way is to select data from joined 'main' and related tables, instead of querying the 'main' table only:

    SELECT t1."OrderID", ..., t2."OrderID" AS "OrderID1", t2."ProductID", ... 
FROM CRM_DEMO."Orders" t1
LEFT OUTER JOIN CRM_DEMO."Order Details" t2 ON t1."OrderID" = t2."OrderID"

    Another way is to select related entities separately, i.e., by executing a new select command for each 'main' entity:

    SELECT t1."OrderID", ... 
FROM CRM_DEMO."Orders" t1

SELECT t1."OrderID", t1."ProductID", ... 
FROM CRM_DEMO."Order Details" t1
WHERE :np0 = t1."OrderID"
-- np0: Input Number (Size = 0; DbType = Decimal) [1]

...

SELECT t1."OrderID", t1."ProductID", ... 
FROM CRM_DEMO."Order Details" t1
WHERE :np0 = t1."OrderID"
-- np0: Input Number (Size = 0; DbType = Decimal) [20]

    The first approach has an obvious advantage that only one command should be executed, thus only one request against the server is performed. However, building a join takes more time than a query to a single table; while it is not so important for a single join, joining multiple tables may result in considerable resource consumption. Thus, if a parent entity type has more than one descendant entity set that should be loaded eagerly, it is often faster to load all but one of these entity sets separately.

    Note: the main reason why using joins may be slower than separate queries is that the data is repeated (and possibly multiple times) in them. For example, let a parent entity type have two descendant types (say, descendant1 and descendant2), and the average numbers of descendants are D1 and D2 for them. Then the result set of the double join (parents joined with descendant1s joined with descendant2s) will have C1*C2 rows for each parent, having the full parent's data for each of these rows.

    Default fetch strategy

    To maximize the fetch performance of eager loading, LinqConnect uses the following approach by default:

    1. For all entities being at the 'one' side of the association (i.e., parent entities and descendants of one-to-one relations), the join is used.
    2. The first 'one-to-many' related entity set is joined as well.
    3. All other entities are retrieved via separate select commands.

    For example, if we want to get all companies with the complete information about their orders and personal contacts, we can execute the following code:


    C#csharpCopy Code
    DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Company>(c => c.Company_Orders);
dlo.LoadWith<Company>(c => c.ShippingCompany_Orders);
dlo.LoadWith<Company>(c => c.Primary_Contact);
dlo.LoadWith<Company>(c => c.Company_Contacts);
context.LoadOptions = dlo;
 
var companyList = context.Companies.ToList();
    Visual BasicCopy Code
    Dim dlo As New DataLoadOptions()
dlo.LoadWith(Of Company)(Function(c) c.Company_Orders)
dlo.LoadWith(Of Company)(Function(c) c.ShippingCompany_Orders)
dlo.LoadWith(Of Company)(Function(c) c.Primary_Contact)
dlo.LoadWith(Of Company)(Function(c) c.Company_Contacts)
context.LoadOptions = dlo
 
Dim companyList = context.Companies.ToList()

    The SQL generated for this snippet is

    SELECT t1."CompanyID", ..., t2."ContactID", ..., t3."OrderID", ... 
FROM CRM_DEMO."Company" t1
LEFT OUTER JOIN CRM_DEMO."Person Contact" t2 ON t1."PrimaryContact" = t2."ContactID"
LEFT OUTER JOIN CRM_DEMO."Orders" t3 ON t1."CompanyID" = t3."CompanyID"
ORDER BY t1."CompanyID", t1."PrimaryContact"

SELECT t1."ContactID", ... 
FROM CRM_DEMO."Person Contact" t1
WHERE :np0 = t1."CompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [1]

SELECT t1."OrderID", ...
FROM CRM_DEMO."Orders" t1
WHERE :np0 = t1."ShipCompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [1]

SELECT t1."ContactID", ... 
FROM CRM_DEMO."Person Contact" t1
WHERE :np0 = t1."CompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [2]

SELECT t1."OrderID", ...
FROM CRM_DEMO."Orders" t1
WHERE :np0 = t1."ShipCompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [2]

...

    As we can see, the related entity being at the 'one' side of association (Person_Contact) and the first entity set being at the 'many' side are retrieved via a join. Other entities are selected separately for each Company object.

    Fetch Modes Customization

    Though the default approach should be convenient in most cases, fetch performance may be improved if you know some peculiarities of the data you are working with. For example, performing even a single join may be not optimal if numerous descendant entities of the given type are associated to each parent entity being retrieved. On the other hand, if you know that each association has at most two or three entities at its 'many' side, you will probably prefer joining all related entity sets instead of only the first one.

    LinqConnect allows to configure this behaviour with the help of the FetchMode enumeration:


    C#csharpCopy Code
    // Specifies the way related entities should be retrieved 
// when eager loading is enabled for them.
enum FetchMode
{
    Default = 0,// The fetch mode is defined automatically.
    Join,		// The related table should be joined.
    SelectByOne	// Separate selects should be performed for these related entities.
}
    Visual BasicCopy Code
    Enum FetchMode
    [Default] = 0
    ' The fetch mode is defined automatically.
    Join
    ' The related table should be joined.
    SelectByOne
    ' Separate selects should be performed for these related entities.
End Enum

    For example, the following code will affect generating a select command with two joins (recall that by default only one join will be used in such a situation):


    C#csharpCopy Code
    DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Company>(c => c.Company_Orders, FetchMode.Join);
dlo.LoadWith<Company>(c => c.Company_Contacts, FetchMode.Join);
context.LoadOptions = dlo;
var companyList = context.Companies.ToList();
    Visual BasicCopy Code
    Dim dlo As New DataLoadOptions()
dlo.LoadWith(Of Company)(Function(c) c.Company_Orders, FetchMode.Join)
dlo.LoadWith(Of Company)(Function(c) c.Company_Contacts, FetchMode.Join)
context.LoadOptions = dlo
Dim companyList = context.Companies.ToList()

    Generated SQL:

    SELECT t1."CompanyID", ..., t2."OrderID", ..., t3."ContactID" AS "ContactID1", ...
FROM CRM_DEMO."Company" t1
LEFT OUTER JOIN CRM_DEMO."Orders" t2 ON t1."CompanyID" = t2."CompanyID"
LEFT OUTER CRM_DEMO."Person Contact" t3 ON t1."PrimaryContact" = t3."ContactID"
ORDER BY t1."CompanyID"

    And no joins will be performed for this sample:


    C#csharpCopy Code
    DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Company>(c => c.Company_Orders, FetchMode.SelectByOne);
context.LoadOptions = dlo;
var companyList = context.Companies.ToList();
    Visual BasicCopy Code
    Dim dlo As New DataLoadOptions()
dlo.LoadWith(Of Company)(Function(c) c.Company_Orders, FetchMode.SelectByOne)
context.LoadOptions = dlo
Dim companyList = context.Companies.ToList()

    Generated SQL:

    SELECT t1."CompanyID", ...
FROM CRM_DEMO."Company" t1

SELECT t1."OrderID", ... 
FROM CRM_DEMO."Orders" t1
WHERE :np0 = t1."CompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [1]

SELECT t1."OrderID", ... 
FROM CRM_DEMO."Orders" t1
WHERE :np0 = t1."CompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [2]

...

    Fetch mode may be specified for each navigation property separately. For example, we may know that each company ships numerous orders, but may have only few (if any) orders as a client; besides that, suppose that it hardly has more than two or three person contacts. Apparently, in this case we will want to use the following way of eager load:


    C#csharpCopy Code
    DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Company>(c => c.Company_Orders, FetchMode.Join);
dlo.LoadWith<Company>(c => c.ShippingCompany_Orders, FetchMode.SelectByOne);
dlo.LoadWith<Company>(c => c.Company_Contacts, FetchMode.Join);
 
// Suppose we are not interested whether primary contacts will be joined or not.
// (And they will, actually, since they are at the 'one' side of the relation).
dlo.LoadWith<Company>(c => c.Primary_Contact, FetchMode.Default);
context.LoadOptions = dlo;
var companyList = context.Companies.ToList();
    Visual BasicCopy Code
    Dim dlo As New DataLoadOptions()
dlo.LoadWith(Of Company)(Function(c) c.Company_Orders, FetchMode.Join)
dlo.LoadWith(Of Company)(Function(c) c.ShippingCompany_Orders, FetchMode.SelectByOne)
dlo.LoadWith(Of Company)(Function(c) c.Company_Contacts, FetchMode.Join)
 
' Suppose we are not interested whether primary contacts will be joined or not.
' (And they will, actually, since they are at the 'one' side of the relation).
dlo.LoadWith(Of Company)(Function(c) c.Primary_Contact, FetchMode.[Default])
context.LoadOptions = dlo
Dim companyList = context.Companies.ToList()

    Generated SQL:

    SELECT t1."CompanyID", ..., t2."OrderID", ..., t3."ContactID", ..., t4."ContactID" AS "ContactID2", ...
FROM CRM_DEMO."Company" t1
LEFT OUTER JOIN CRM_DEMO."Orders" t2 ON t1."CompanyID" = t2."CompanyID"
LEFT OUTER JOIN CRM_DEMO."Person Contact" t3 ON t1."CompanyID" = t3."CompanyID"
LEFT OUTER JOIN CRM_DEMO."Person Contact" t4 ON t1."PrimaryContact" = t4."ContactID"

SELECT t1."OrderID", ...
FROM CRM_DEMO."Orders" t1
WHERE :np0 = t1."ShipCompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [1]

SELECT t1."OrderID", ...
FROM CRM_DEMO."Orders" t1
WHERE :np0 = t1."ShipCompanyID"
-- np0: Input Number (Size = 0; DbType = Decimal) [2]

...

    Customizing Fetch Mode for Nested Load Options

    Suppose we are loading entities of one type (say, parent) with related entities (say, children) of another type. If children have their own related entities (call these grand-children), we may be interested in eager loading of grand-children as well. To do so, we declare several 'nested' LoadWith statements:


    C#csharpCopy Code
    DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Company>(c => c.Company_Contacts);
dlo.LoadWith<Person_Contact>(pc => pc.Orders);
context.LoadOptions = dlo;
var companyList = context.Companies.ToList();
    Visual BasicCopy Code
    Dim dlo As New DataLoadOptions()
dlo.LoadWith(Of Company)(Function(c) c.Company_Contacts)
dlo.LoadWith(Of Person_Contact)(Function(pc) pc.Orders)
context.LoadOptions = dlo
Dim companyList = context.Companies.ToList()

    Or, if we want to use these load options for a single query only,


    C#csharpCopy Code
    var companyList = context.Companies
                      .LoadWith(
                          c => c.Company_Contacts
                          .LoadWith(pc => pc.Orders)
                      ).ToList();
    Visual BasicCopy Code
    Dim companyList = context.Companies.LoadWith( _
    Function(c) c.Company_Contacts.LoadWith( _
        Function(pc) pc.Orders)).ToList()

    By default, the entities mentioned in 'nested' load options are retrieved with separate queries (as only the first related collection is joined). However, it is still possible to set other fetch modes for such 'nested' load options. This is done in the same way as for 'plain' ones. For example, a single SQL statement containing two joins will be generated for the following snippet:


    C#csharpCopy Code
    var companyList = context.Companies
                      .LoadWith(
                          c => c.Company_Contacts
                              .LoadWith(pc => pc.Orders, FetchMode.Join),
                          FetchMode.Join
                      )
                      .ToList();
    Visual BasicCopy Code
    context.Companies.LoadWith( _
    Function(c) c.Company_Contacts.LoadWith( _
        Function(pc) pc.Orders, FetchMode.Join _
    ),
    FetchMode.Join _
).ToList()

    Generated SQL:

    SELECT t1."CompanyID", ..., t2."ContactID", ..., t3."OrderID", ...
FROM CRM_DEMO."Company" t1
LEFT OUTER JOIN 
  (CRM_DEMO."Person Contact" t2
  LEFT OUTER JOIN CRM_DEMO."Orders" t3 ON t2."ContactID" = t3."ContactID") 
ON t1."CompanyID" = t2."CompanyID"
ORDER BY t1."CompanyID"

     

     

    © 2010 - 2024 Devart. All Rights Reserved.

    [email protected]