Generated code - Prefetch paths, Adapter

Preface

Pre-fetching all entities required by the code which will consume the entities, including related entities is called Eager Loading. In the occasion where a lot of related entities are to be fetched together with the main set of entities, doing this manually could lead to a lot of queries and tedious code. For example, fetching a collection of Order entities and their related Customer entities, using normal code this would require for 50 order entities 51 queries: 1 for the Order entities and one per fetched Order to obtain the related Customer entity.

To do this more efficiently, i.e. in two queries and merging the two sets automatically, LLBLGen Pro runtime framework offers a feature called Prefetch Paths, which allow you to specify which objects to fetch together with the actual objects to fetch, using only one query per node in the path (in our order-customer example this would mean 2 queries).

note Note:
If your database is case insensitive (uses case insensitive collation), and you have foreign key values which only differ in casing from the PK values, it can be that the prefetch path merging (merge child (e.g. order) with parent (e.g. customer) doesn't find matching parent-child relations, because the FK differs from the PK value (as the routine uses case sensitive compares).

To fix this, set the static / shared property EntityFieldCore.CaseSensitiveStringHashCodes to false (default is true). You can also do this through a config file setting by specifying caseSensitiveStringHashCodes with 'false' in the .config file of your application. See for more information about this config file setting Application Configuration through .config files.

Using Prefetch Paths, the basics

In the Preface paragraph, the example of an Order selection and their related Customer objects was mentioned. The most efficient way to fetch all that data would be: two queries, one for all the Order entities and one for all the Customer entities. By specifying a Prefetch Path together with the fetch action for the Order entities, the logic will fetch these related entities defined by the Prefetch Path as efficient as possible and will merge the two result-sets to the result you're looking for.

Adapter uses the PrefetchPath2 class for Prefetch Path objects. PrefetchPath2 objects are created for a single entity type, specified by the specified entity enumeration. This ensures that PrefetchPathElement2 objects added to the PrefetchPath2 object actually define a valid node for the entity the path belongs to.

PrefetchPathElement2 objects, the nodes added to the PrefetchPath2 objects which define the entities to fetch, are created using static (shared) properties of the parent entity. The properties are named after the fields mapped on the relations they define the fetch action for. Example: The Orders collection in a Customer entity can be fetched using a Prefetch Path by using the static (shared) property CustomerEntity.PrefetchPathOrders to produce the PrefetchPathElement2 for 'Orders'.

The example of Order entities and their related Customer entities fetched with Prefetch Paths looks like this:

// C#
EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.OrderEntity);
prefetchPath.Add(OrderEntity.PrefetchPathCustomer);
RelationPredicateBucket filter = new RelationPredicateBucket(OrderFields.EmployeeId == 2);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchEntityCollection(orders, filter, prefetchPath);
}

// QuerySpec alternative
var orders = new EntityCollection<OrderEntity>();
using(var adapter = new DataAccessAdapter())
{
	var qf = new QueryFactory();
	var q = qf.Order
		.Where(OrderFields.EmployeeId == 2)
		.WithPath(OrderEntity.PrefetchPathCustomer);
	adapter.FetchQuery(q, orders);
}
' VB.NET
Dim orders As New EntityCollection(Of OrderEntity)()
Dim prefetchPath As New PrefetchPath2(CInt(EntityType.OrderEntity))
prefetchPath.Add(OrderEntity.PrefetchPathCustomer)
Dim filter As New RelationPredicateBucket(OrderFields.EmployeeId = 2)
Using adapter As New DataAccessAdapter()
	adapter.FetchEntityCollection(orders, filter, prefetchPath)
End Using


' QuerySpec alternative
Dim orders As New EntityCollection(Of OrderEntity)()
Using adapter As New DataAccessAdapter()
	Dim qf As New QueryFactory()
	Dim q = qf.Order _
		.Where(OrderFields.EmployeeId = 2) _
		.WithPath(OrderEntity.PrefetchPathCustomer)
	adapter.FetchQuery(q, orders)
End Using

This fetch action will fetch all Order entities accepted by the Employee with Id 2, and will also fetch for each of these Order entities the related Customer entity. This will result in just two queries: one for the Order entities with the filter on EmployeeId = 2 and one for the Customer entities with a subquery filter using the Order entity query. The fetch logic will then merge these two resultsets using efficient hashtables in a single pass algorithm.

The example above is a rather simple graph with just two nodes. LLBLGen Pro's Prefetch Path functionality is capable of handling much more complex graphs and offers options to tweak the fetch actions per PrefetchPathElement2 object to your liking. To illustrate that the graph doesn't have to be linear, the exaxmple below shows a more complex graph: a set of Customer entities, all their related Order eentities, all the Order's Order Detail entities and the Customer entities' Address entities. The example illustrates how to use sublevels in the graph: use the SubPath property of the PrefetchPathElement2 object used to build graph nodes with.

// C#
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);
prefetchPath.Add(CustomerEntity.PrefetchPathVisitingAddress);
RelationPredicateBucket filter = new RelationPredicateBucket(CustomerFields.Country == "Germany");
Using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchEntityCollection(customers, filter, prefetchPath);
}

// QuerySpec alternative
var customers = new EntityCollection<CustomerEntity>();
using(var adapter = new DataAccessAdapter())
{
	var qf = new QueryFactory();
	var q = qf.Customer
		.Where(CustomerFields.Country=="Germany")
		.WithPath(CustomerEntity.PrefetchPath.Orders
				.WithSubPath(OrderEntity.PrefetchPathOrderDetails), 
			 CustomerEntity.PrefetchPathVisitingAddress);
	adapter.FetchQuery(q, customers);
}
' VB.NET
Dim customers As New EntityCollection(Of CustomerEntity)()
Dim prefetchPath As New PrefetchPath2(CInt(EntityType.CustomerEntity))
prefetchPath.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails)
prefetchPath.Add(CustomerEntity.PrefetchPathVisitingAddress)
Dim filter As New RelationPredicateBucket(CustomerFields.Country = "Germany")
Using adapter As New DataAccessAdapter()
	adapter.FetchEntityCollection(customers, filter, prefetchPath)
End Using

' QuerySpec alternative
Dim customers As New EntityCollection(Of CustomerEntity)()
Using adapter As New DataAccessAdapter()
	Dim qf As New QueryFactory()
	Dim q = qf.Customer _
		.Where(CustomerFields.Country="Germany") _
		.WithPath(CustomerEntity.PrefetchPath.Orders _
				.WithSubPath(OrderEntity.PrefetchPathOrderDetails), _
			 CustomerEntity.PrefetchPathVisitingAddress)
	adapter.FetchQuery(q, customers)
End Using

The example above, fetches in 4 queries (one for the Customer entities, one for the Order entities, one for the Order Detail entities and one for the Address entities) all objects required for this particular graph. As the end result, you'll get all Customer entities from Germany, which have their Orders collections filled with their related Order entities, all Order entities have their related Order Detail entities loaded and each Customer entity also has their related Address entity (over the Visiting property) loaded. The graph is also non-linear: it has two branches from Customer. You can define more if you want, there is no limit on the number of PrefetchPathElement2 objects in a Prefetch Path, however consider that each level in a graph is a separate query.
Optimizing Prefetch Paths
The LLBLGen Pro runtime framework creates a sub-query for each node in a Prefetch Path to be able to filter child nodes on the query results of the parent nodes. Say, you want to fetch all customers from "France" and their order objects. This would look something like the following:

//// C#
PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);
' VB.NET
Dim path As New PrefetchPath2(CInt(EntityType.CustomerEntity))
path.Add(CustomerEntity.PrefetchPathOrders);

When the customers are fetched with the filter and the path it will produce SQL like the following: (pseudo)
Query to fetch the customers:

SELECT CustomerID, CompanyName, ...
FROM CustomersWHERE Country = @country

Query to fetch the orders:

SELECT OrderID, CustomerID, OrderDate, ...
FROM Orders
WHERE CustomerID IN
(
	SELECT CustomerID
	FROM Customers	WHERE Country = @country
)

Tests will show that for small quantities of Customers, say 10, this query is less efficient than this query: (pseudo)

SELECT OrderID, CustomerID, OrderDate, ...
FROM Orders
WHERE CustomerID IN
( @customer1, @customer2, ... , @customer10)

The LLBLGen Pro runtime framework offers you to tweak this query generation by specifying a threshold, DataAccessAdapter.ParameterisedPrefetchPathThreshold, what the runtime libraries should do: produce a full sub-query or an IN clause with a range of values. The value set for ParameterisedPrefetchPathThreshold specifies the amount of the parent entities (in our example, the customer entities) it has to switch to a full subquery. ParameterisedPrefetchPathThreshold is set to 50 by default. Tests showed a threshold of 200 is still efficient, but to be sure it works on every database, the value is set to 50.

Please note that for each subnode fetch, its parent is the one which is examined for this threshold, so it's not only the root of the complete graph which is optimized with this setting. In the example in the previous paragraph, Customer - Orders - OrderDetails was fetched, for OrderDetails the node for Orders is the parent node and the entities fetched for orders are the parent entities for the orderdetails entities. This means that for the query for OrderDetails, the number of Orders fetched determines whether a full sub-query is used or an IN clause.

It's recommended not to set the ParameterisedPrefetchPathThreshold to a value larger than 300 unless you've tested a larger value in practice and it made queries run faster. This to prevent you're using slower queries than necessary. You can set the value per call, and it affects all Prefetch Path related fetches done with the same DataAccessAdapter instance.
Polymorphic Prefetch Paths
LLBLGen Pro supports polymorphism in prefetch paths as well. Polymorphic prefetch paths work the same as normal prefetch paths, only now they work on subtypes as well. Say you have the following hierarchy: Employee - Manager - BoardMember and BoardMember has a relation with CompanyCar (which can be a hierarchy of its own). If you then fetch all employees (which can be of type BoardMember) and you want to load for each BoardMember loaded in that fetch also its related CompanyCar, you define the prefetch path as any other path:

//// C#
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.EmployeeEntity);
EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
// specify the actual path: BoardMember - CompanyCar
prefetchPath.Add(BoardMemberEntity.PrefetchPathCompanyCar);
// .. fetch codepre>
' ' VB.NET
Dim employees As New EntityCollection(Of EmployeeEntity)()
Dim prefetchPath As New PrefetchPath2(CInt(EntityType.EmployeeEntity))
' specify the actual path: BoardMember - CompanyCar
prefetchPath.Add(BoardMemberEntity.PrefetchPathCompanyCar)
' .. fetch code

In the code above, the LLBLGen Pro runtime framework will only load those CompanyCar entities which are referenced by a BoardMember entity, and will merge them at runtime with the BoardMember entities loaded in the fetch.
Multi-branched Prefetch Paths
Prefetch Paths can also be multi-branched. Multi-branched means that two or more sub-paths are defined from the same path node. As Prefetch Paths are defined per-line this can be a bit of a problem. The example below defines two subpaths from the OrderEntity node and it illustrates how to create this multi-branched Prefetch Path definition:

PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
PrefetchPathElement2 orderElement = path.Add(CustomerEntity.PrefetchPathOrders);
orderElement.SubPath.Add(OrderEntity.PrefetchPathOrderDetails);  // branch 1
orderElement.SubPath.Add(OrderEntity.PrefetchPathEmployee);      // branch 2
Dim path As New PrefetchPath2(CInt(EntityType.CustomerEntity))
Dim orderElement As PrefetchPathElement2 = path.Add(CustomerEntity.PrefetchPathOrders)
orderElement.SubPath.Add(OrderEntity.PrefetchPathOrderDetails)  ' branch 1
orderElement.SubPath.Add(OrderEntity.PrefetchPathEmployee)      ' branch 2

Advanced Prefetch Paths

The previous examples showed some of the power of the Prefetch Path functionality, but sometimes you need some extra features, like filtering on the related entities, sorting of the related entities fetched and limiting the number of related entities fetched. The LLBLGen Pro runtime framework offers these features in the PrefetchPathElement2 object, and are also accessible through overloads of the PrefetchPath2.Add() method. Let's say you want all employees and the last order they processed. The following example illustrates this, using Prefetch Paths. It sorts the related entities, and limits the output to just 1.

// C#
EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.EmployeeEntity);
SortExpression sorter = new SortExpression(OrderFields.OrderDate | SortOperator.Descending);
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchEntityCollection(employees, null, prefetchPath);
}

// QuerySpec alternative
var employees= new EntityCollection<EmployeeEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	var qf = new QueryFactory();
	var q = qf.Employee
		.WithPath(EmployeeEntity.PrefetchPathOrders
				.WithOrdering(OrderFields.OrderDate.Descending())
				.WithLimit(1));
	adapter.FetchQuery(q, employees);
}
' VB.NET
Dim employees As New EntityCollection(Of EmployeeEntity)()
Dim prefetchPath As New PrefetchPath2(CInt(EntityType.EmployeeEntity))
Dim sorter As New SortExpression(OrderFields.OrderDate Or SortOperator.Descending)
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, Nothing, Nothing, sorter)
Using adapter As New DataAccessAdapter()
	adapter.FetchEntityCollection(employees, Nothing, prefetchPath)
End Using

' QuerySpec alternative
Dim employees as New EntityCollection(Of EmployeeEntity)()
Using adapter As New DataAccessAdapter()
	Dim qf As New QueryFactory()
	Dim q = qf.Employee _
		.WithPath(EmployeeEntity.PrefetchPathOrders _
				.WithOrdering(OrderFields.OrderDate.Descending()) _
				.WithLimit(1))
	adapter.FetchQuery(q, employees)
End Using

Besides a sort expression, you can specify a RelationCollection together with a PredicateExpression when you add a PrefetchPathElement2 to the PrefetchPath2 object to ensure that the fetched related entities are the ones you need.

For example, the following code snippet illustrates the prefetch path of Customer - Orders, but also filters the customers on its related orders. As this filter belongs to the customers fetch, it shouldn't be added to the Orders node, but should be passed to the FetchEntityCollection() method call.

// C#
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
RelationPredicateBucket customerFilter = new RelationPredicateBucket();
// fetch all customers which have orders shipped to brazil.
customerFilter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
customerFilter.PredicateExpression.Add(OrderFields.ShipCountry=="Brazil");
// load for all customers fetched their orders. 
PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);
// perform the fetch
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, customerFilter, path);
}
' VB.NET
Dim customers As New EntityCollection(Of CustomerEntity)();
Dim customerFilter As New RelationPredicateBucket()
' fetch all customers which have orders shipped to brazil.
customerFilter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId)
customerFilter.PredicateExpression.Add(OrderFields.ShipCountry="Brazil")
' load for all customers fetched their orders. 
Dim path As New PrefetchPath2(CInt(EntityType.CustomerEntity))
path.Add(CustomerEntity.PrefetchPathOrders)
' Perform the fetch
Using adapter As new DataAccessAdapter()
    adapter.FetchEntityCollection(customers, customerFilter, path)
End Using

M:N related entities
Prefetch Paths can also be used to fetch m:n related entities, they work the same as other related entities. There is one caveat: the intermediate entities are not fetched with an m:n relation Prefetch Path. For example, if you fetch a set of Customer entities and also their m:n related Employee entities, the intermediate entity, Order, is not fetched. If you specify, via another PrefetchPathElement2, to fetch the Order entities as well, and via a SubPath also their related Employee entities, these Employee entities are not the same objects as located in the Employees collection of every Customer entity you fetched. To overcome this, use a Context object to make the framework load unique instances per unique entities.

Derived entity classes and Prefetch Paths
If you create or generate derived entity classes for your Adapter entities, you also have derived new entity factory classes from the entity factory classes for the entity classes. The PrefetchPathElement2 objects produced by the static (shared) properties however will contain an entity factory for the original generated entity classes. To make sure the fetched related entities are created using the proper entity factory, specify the entity factory to use when you add the PrefetchPathElement2 to the PrefetchPath2 object, using the proper PrefetchPath2.Add() overload. You can also set the entity factory to use by setting the PrefetchPathElement2.EntityFactoryToUse property.

Optimizing root fetches when sorter/limits are specified
 If you want to use a sort clause on the root of the path and / or a limit (e.g. only the first 10 entities have to be fetched), it can be more efficient to set the property UseRootMaxLimitAndSorterInPrefetchPathSubQueries to true. However it could lead to non-working queries due to the inlining of sorters and limits in deeper queries in some cases, so use this with care. It's only applied to the root node of a path, this setting is ignored for sub-nodes.

Single entity fetches and Prefetch Paths

Prefetch Paths can also be used when you fetch a single entity, either by fetching the entity using a primary key or via a unique constraint fetch. Below are two examples, one using the primary key and one using a unique constraint. Both fetch the m:n related Employees for the particular Customer entity instantiated.

Primary key fetch

// C#
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees);
CustomerEntity customer = new CustomerEntity("BLONP");
using(DataAccessAdapter adapter = new DataAccessAdapter()
{
	adapter.FetchEntity(customer, prefetchPath);
}
' VB.NET
Dim prefetchPath As New PrefetchPath2(CInt(EntityType.CustomerEntity))
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees)
Dim customer As New CustomerEntity("BLONP")
Using adapter As New DataAccessAdapter()
	adapter.FetchEntity(customer, prefetchPath)
End Using

Unique constraint fetch

// C#
CustomerEntity customer = new CustomerEntity();
customer.CompanyName = "Blauer See Delikatessen";
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchEntityUsingUniqueConstraint(customer, customer.ConstructFilterForUCCompanyName(), prefetchPath);
}
' VB.NET
Dim customer As New CustomerEntity()
customer.CompanyName = "Blauer See Delikatessen"
Dim prefetchPath As New PrefetchPath2(CInt(EntityType.CustomerEntity))
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees)
Using adapter As New DataAccessAdapter()
	adapter.FetchEntityUsingUniqueConstraint(customer, customer.ConstructFilterForUCCompanyName(), prefetchPath)
End Using

Prefetch Paths and Paging

LLBLGen Pro supports paging functionality in combination of Prefetch Paths. If you want to utilize paging in combination of prefetch paths, be sure to set DataAccessAdapter.ParameterisedPrefetchPathThreshold to a value larger than the page size you want to use. You can use paging in combination of prefetch path with a page size larger than DataAccessAdapter.ParameterisedPrefetchPathThreshold but it will be less efficient.

To use paging in combination of prefetch paths, use one of the overloads you'd normally use for fetching data using a prefetch path, which accept a page size and page number as well.
LLBLGen Pro Runtime Framework v3.5 documentation. ©2012 Solutions Design bv