Prefetch paths

LLBLGen Pro has the ability to fetch related entities together with a set of entities, e.g. fetch a set of Customer entities and also their Order entities. This feature is called Prefetch Paths, (Adapter, SelfServicing). With Linq, it is also possible to specify which related entities should be fetched together with the entities fetched in the query.

In Linq to LLBLGen Pro, prefetch paths are specified inside the Linq query. This is done because the prefetch path should be part of the query, as it describes the related entities to fetch for the entities returned by the query. When the prefetch path is part of the query itself, it's possible to specify multiple queries with different prefetch paths using the same LinqMetaData for example.

Linq to LLBLGen Pro uses the same prefetch path engine as you'd normally use through the main query API, as Linq to LLBLGen Pro is built on top of the framework query API. This means the performance for the fetch is optimal: 1 query per node in the prefetch path.

There are three different built-in ways of specifying a prefetch path in Linq to LLBLGen Pro:

  1. using PathEdge instances
  2. using Lambda expressions
  3. using a normal PrefetchPath(2) object like with the lower-level API.

It can be that one appeals more to you than the other. They're identical in feature set (the Lambda variant is built on top of the PathEdge variant, which is more low level). 

Approach 2 also covers the extension library that was originally created under the name LLBLGen.Linq.Prefetch and which has been merged into the ORMSupportClasses library, starting with v5.7. To use the extension methods in this library, add a using/Imports directive to your code file for the LLBLGen.Linq.Prefetch namespace.

Approach 1: WithPath and PathEdges

A developer can specify a prefetch path by using the Queryable extension method WithPath. WithPath accepts one or more PathEdge instances. A PathEdge is a specification of a new node which has to be fetched related to its parent, so it represents an edge in the path.

PathEdge constructors accept 0 or more PathEdges as well, which are the related edges below the current node. PathEdge instances require a type specification for the type of entity they represent (which is fetched through the PathEdge), because developers are able to specify the prefetch path element filter by using Linq constructions.

SortExpression instances have to be specified with LLBLGen Pro constructions, as linq doesn't offer a way to specify these with the Linq keyword 'orderby'.

There's also a WithPath overload which accepts a pre-build PrefetchPath. This can be useful for people who have already written a lot of prefetch path construction code in utility libraries and want to re-use that code.

Location of  WithPath calls

The WithPath method is an extension method of Queryable. This means that everywhere in the query where a Queryable object is used, WithPath can be used as well:

// query  A
var q =  from c in metaData.Customer.WithPath(...) select c;

// query  B
var q =  (from c in metaData.Customer select c).WithPath(...);

// query  C
var q =  (from c in metaData.Customer.WithPath(...) where ... select c) 
         join o in  metaData.Order on ...


' query  A
Dim q =  From c In metaData.Customer.WithPath(...) Select c

' query  B
Dim q =  (From c In metaData.Customer Select c).WithPath(...)

' query  C
Dim q =  (From c In metaData.Customer.WithPath(...) Where ... Select c) _
          Join o In metaData.Order On ...

Query A and B define the WithPath statement on the element which is in the projection as well. However, query C doesn't necessarily do so: the prefetch path is inside a query on its own, not on the final set. In query C, the prefetch path definition is ignored. Approach B is the recommended way to use prefetch paths in Linq queries, as it defines the path on the resultset to return.

WithPath only works with a query which returns entities. A query which returns a list of anonymous type instances can't be used with WithPath. In that case, use a nested query.

Specifying nodes

The first parameter a PathEdge constructor has, is the prefetch path node to fetch, e.g. CustomerEntity.PrefetchPathOrders. The type specified with the PathEdge instance is the type of the entity the PathEdge represents.

LLBLGen Pro's prefetch path system is very flexible: you can specify per node a filter, a limiter and a sorter, and also exclude fields in the entity to fetch. This is the same with PathEdge objects. The PathEdge constructor allows the developer to specify the filter, using lambda expressions, the sort expression using normal LLBLGen Pro sortclauses, a limiter and an ExcludeIncludeFields list. These elements are used within the PrefetchPath for the query.

If you want to specify a level below the PathEdge, e.g. you want to specify for the fetch of a set of customers not only the related Order entities, but below the orders also the related OrderDetail entities for each order specified, you specify the OrderDetail PathEdge instance at the end of the constructor call to the PathEdge for Order.

The following example illustrates this. This example fetches all Customer entities from Germany, their Orders, per Order the OrderDetail instances and also the Employee entities related to the Order entities fetched. The PathEdge instance for Order therefore has two PathEdge instances below it: one for OrderDetail and one for Employee. Both are specified in the constructor call of the PathEdge of Order.

var q = (from c in metaData.Customer
         where c.Country=="Germany"
           select c)
            .WithPath(
                 new PathEdge<OrderEntity>(
                      CustomerEntity.PrefetchPathOrders,
                      o=>o.EmployeeId == 2,
                      new PathEdge<OrderDetailsEntity>(OrderEntity.PrefetchPathOrderDetails),
                      new PathEdge<EmployeeEntity>(OrderEntity.PrefetchPathEmployee)));

Dim q = (From c In metaData.Customer _
         Where c.Country="Germany" _
           Select c) _
            .WithPath( _
                 New PathEdge(Of OrderEntity)( _
                      CustomerEntity.PrefetchPathOrders, _
                      Function(o) o.EmployeeId=2, _            ' Filter for the node 
                      New PathEdge(Of OrderDetailsEntity)(OrderEntity.PrefetchPathOrderDetails), _
                      New PathEdge(Of EmployeeEntity)(OrderEntity.PrefetchPathEmployee)))

Approach 2a: WithPath and Lambda expressions

The PathEdge approach works OK, but if you want to go linq all-the-way, it can be a bit of a mixed bag: it contains LLBLGen Pro API elements, and not their Linq equivalents. The approach with Lambda expressions also uses the WithPath extension method, however it takes a Lambda expression which contains the full path. The start is always:

query.WithPath(var=>var.Prefetch...

It has two variants: var.Prefetch(...) and var.Prefetch<relatedEntityType>(...). The first is the simple variant, and can be used to specify 1 level of related entities to fetch without any filters etc., so basically a shortcut. Below is an example which fetches the orders for all customers in the query:

var q = (from c in metaData.Customer
         where c.Country=="UK"
         select c).WithPath(p=>p.Prefetch(c=>c.Orders));

Dim q = (From c In metaData.Customer _
         Where c.Country="UK" _
         Select c).WithPath(Function(p) p.Prefetch(Function(c) c.Orders))

The second variant requires a destination type and this variant allows the developer to specify filters, sorters, a limitation on the number of elements to fetch, excluding/including fields and subpaths.

Multiple nodes at the same level

Prefetch Paths are a tree: one or more branches with one or more levels with nodes. As Linq queries are specified at one continued line of code, it's key to be able to specify a full tree with this lambda expression based api as well.

The rules below should be followed to write successful prefetch paths with WithPath:

  • Every branch in the tree is started with .Prefetch
  • Every method specified after .Prefetch is applied to the same level.
  • A sub-level is specified with SubPath, inside you can start a new path, with Prefetch.

SubPaths

Using the .PrefetchPath<type> variant, we can define multiple levels of nodes in the prefetch path, as we can specify .SubPath calls. It's allowed to specify SubPath calls when the Prefetch call was specified with a destination type. The following example illustrates this:

var q = (from c in metaData.Customer
         where c.Country == "Germany"
         select c).WithPath(customerPath=>customerPath
              .Prefetch<OrderEntity>(c=>c.Orders)
                     .FilterOn(o=>o.EmployeeId==2)
                     .WithHint("NOLOCK")
                     .SubPath(orderPath=>orderPath.Prefetch(o => o.Employee))
                .Prefetch<EmployeeEntity>(c=>c.EmployeeCollectionViaOrder));

Dim q = (From c In metaData.Customer _
         Where c.Country = "Germany" _
         Select c).WithPath(Function(customerPath) customerPath _
              .Prefetch(Of OrderEntity)(Function(c) c.Orders) _
                     .FilterOn(Function(o) o.EmployeeId=2) _
                     .WithHint("NOLOCK") _
                     .SubPath(Function(orderPath) orderPath.Prefetch(Function(o) o.Employee)) _
              .Prefetch(Of EmployeeEntity)(Function(c) c.EmployeeCollectionViaOrder))

A SubPath is defined below Order. It allows the developer to specify which elements to fetch related to Order. The example also illustrates how to specify a second branch in the tree below Customer: besides Customer - Order, also Customer - Employee (m:n relation) is specified to be prefetched.

The specification for Employee in the Order's SubPath uses the simple form of .Prefetch(). This method doesn't allow a SubPath specification below it. If .Prefetch(o=>o.Employee) is changed to .Prefetch<EmployeeEntity>(o=>o.Employee), it allows a .SubPath call after it, which makes it possible to specify nodes related to Employee.

Filtering, sorting, excluding/including fields, limiting

Prefetch paths in LLBLGen Pro offer a rich fetch plan specification: per node the filter, sort expression, limitation on the total rows and also the fields to exclude/include. This lambda expression based api also offers this. By using Prefetch<type>(), the developer can add calls to the following extension methods. You can add these calls to .Prefetch similar to .SubPath in the example above.

  • FilterOn(lambda) to specify a filter. The filter is a lambda expression. E.g.: c=>c.Country=="USA"
  • OrderBy/OrderByDescending(lambda) to specify fields to sort on. OrderBy is ascending sorting, OrderByDescending is descending sorting. Fields are specified by simply using a lambda, e.g. o=>o.CustomerId
  • LimitTo(integer) to specify a limitation on the total number of related elements to fetch
  • Exclude/Include(lambda). One specification of this method is allowed and fields to include/exclude are specified with lambda's, using a comma separated list, e.g.: .Exclude(c=>c.Country, c=>c.Photo)
  • NoCaching() to specify the prefetch path node's resultset should be excluded from resultset caching.
  • WithHint(string) to specify a target/index hint.
  • ForSystemDate/ForBusinessDate(string) to specify a temporal table predicate.

Polymorphic prefetch paths

To define polymorphic prefetch paths, it's sufficient to specify the type of the subtype as the generic argument of Prefetch or SubPath. Example:

var q = (from d in metaData.Department
         select d).WithPath(departmentPath=>departmentPath
              .Prefetch<BoardMemberEntity>(dep=>dep.Employees)
                   .SubPath(bmPath=>bmPath
                        .Prefetch<FamilyCarEntity>(bm=>bm.CompanyCar)));


Dim q = (From d In metaData.Department _
         Select d).WithPath(Function(departmentPath) departmentPath _
              .Prefetch(Of BoardMemberEntity)(Function(dep) dep.Employees) _
                   .SubPath(Function(bmPath) bmPath _
                        .Prefetch(Of FamilyCarEntity)(Function(bm) bm.CompanyCar)))

Here, the subtype BoardMemberEntity is specified as Employee to fetch for 'Employees'. LLBLGen Pro adds a type filter to the query so only BoardMemberEntity instances are fetched. This also allows intellisense to specify the path further, as CompanyCar is a related entity of BoardMember only. There too, a subtype is specified to fetch.

Another way to do polymorphic prefetch paths is by using .SubPath<TSubType>, as the example above filters on BoardMemberEntity. If you don't want to have a filter, but just want to specify a related entity for some subtypes, you should use the .SubPath**<TSubType> approach instead.

Approach 2b: With and Lambda expressions

This approach also uses Lambda expressions like approach 2, but now it uses shortcut methods available in the namespace LLBLGen.Linq.Prefetch, which is available through the ORMSupportClasses starting with v5.7. The library was originally written by Richard Hopton.

This approach uses the .With extension method, combined with lambda expressions. The start is always:

query.With(var=>var.Navigator...

Below is an example which fetches the orders for all customers in the query:

var q = (from c in metaData.Customer
         where c.Country=="UK"
         select c).With(p=>p.Orders);

Dim q = (From c In metaData.Customer _
         Where c.Country="UK" _
         Select c).With(Function(p) p.Orders)

Multiple nodes at the same level

Prefetch Paths are a tree: one or more branches with one or more levels with nodes. As Linq queries are specified at one continued line of code, it's key to be able to specify a full tree with this lambda expression based api as well.

The rules below should be followed to write successful prefetch paths with ..With:

  • Every branch in the tree is started with .With
  • Every method specified after .With is applied to the same level.
  • A sub-level is specified with .With appended to the parent's .With call

Example

The following example illustrates this:

var q = (from c in metaData.Customer
         where c.Country == "Germany"
         select c).With(p=>p.Orders.FilterBy(o=>o.EmployeeId==2).With(op=>op.Employee), 
                        p=>p.EmployeeCollectionViaOrder);

Dim q = (From c In metaData.Customer _
         Where c.Country = "Germany" _
         Select c).With(Function(p) p.Orders.FilterBy(Function(o) o.EmployeeId=2)
                                    .With(Function(op) op.Employee), 
                        Function(p) p.EmployeeCollectionViaOrder)

A sub-path is defined below Order. It allows the developer to specify which elements to fetch related to Order. The example also illustrates how to specify a second branch in the tree below Customer: besides Customer - Order, also Customer - Employee (m:n relation) is specified to be prefetched.

Filtering, sorting, excluding/including fields, limiting

Prefetch paths in LLBLGen Pro offer a rich fetch plan specification: per node the filter, sort expression, limitation on the total rows and also the fields to exclude/include. This lambda expression based api also offers this.

  • FilterBy(lambda) to specify a filter. The filter is a lambda expression. E.g.: c=>c.Country=="USA"
  • SortBy/SortByDescending(lambda) to specify fields to sort on. OrderBy is ascending sorting, OrderByDescending is descending sorting. Fields are specified by simply using a lambda, e.g. o=>o.CustomerId
  • LimitTo(integer) to specify a limitation on the total number of related elements to fetch
  • Exclude/Include(lambda). One specification of this method is allowed and fields to include/exclude are specified with lambda's, using a comma separated list, e.g.: .Exclude(c=>c.Country, c=>c.Photo)

Approach 3: Normal PrefetchPath objects

A third approach exists which is similar to Approach 1 above, and which uses the low-level API prefetch path objects. It uses, like Approach 1, the .WithPath() method, but here the overload which accepts an IPrefetchPathCore instance is used. See the example below how to use it. 

var path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);

// use it in a linq query
var q = (from c in metaData.Customer
        where !(new string[] { "FISSA", "PARIS" }.Contains(c.CustomerId))
        select c).WithPath(path);