Generated code - Prefetch Paths, SelfServicing
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: |
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.
SelfSeriving uses the
PrefetchPath class for Prefetch Path objects.
PrefetchPath
objects are created for a single entity type, specified by the specified
entity enumeration. This ensures that
PrefetchPathElement objects
added to the
PrefetchPath object actually define a valid node for the
entity the path belongs to.
PrefetchPathElement objects, the
nodes added to the PrefetchPath 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
PrefetchPathElement for 'Orders'.
The example of
Order
entities and their related
Customer entities fetched with Prefetch
Paths looks like this:
// C#
OrderCollection orders = new OrderCollection();
PrefetchPath prefetchPath = new PrefetchPath(EntityType.OrderEntity);
prefetchPath.Add(OrderEntity.PrefetchPathCustomer);
PredicateExpression filter = new PredicateExpression(OrderFields.EmployeeId == 2);
orders.GetMulti(filter, prefetchPath);
// QuerySpec alternative
var orders = new OrderCollection();
var qf = new QueryFactory();
var q = qf.Order
.Where(OrderFields.EmployeeId == 2)
.WithPath(OrderEntity.PrefetchPathCustomer);
orders.GetMulti(q);
' VB.NET
Dim orders As New OrderCollection()
Dim prefetchPath As New PrefetchPath(CInt(EntityType.OrderEntity))
prefetchPath.Add(OrderEntity.PrefetchPathCustomer)
Dim filter As New PredicateExpression(OrderFields.EmployeeId = 2)
orders.GetMulti(filter, prefetchPath)
' QuerySpec alternative
Dim orders As New OrderCollection()
Dim qf As New QueryFactory()
Dim q = qf.Order _
.Where(OrderFields.EmployeeId = 2) _
.WithPath(OrderEntity.PrefetchPathCustomer)
orders.GetMulti(q)
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, 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 PrefetchPathElement to your liking. To illustrate that the graph doesn't have to be linear, we'll fetch a
more complex graph: a set of Customer entities, all their related Order entities, 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 PrefetchPathElement object used to
build graph nodes with.
// C#
CustomerCollection customers = new CustomerCollection();
PrefetchPath prefetchPath = new PrefetchPath(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);
prefetchPath.Add(CustomerEntity.PrefetchPathVisitingAddress);
PredicateExpression filter = new PredicateExpression(CustomerFields.Country == "Germany");
customers.GetMulti(filter, prefetchPath);
// QuerySpec alternative
var customers = new CustomerCollection();
var qf = new QueryFactory();
var q = qf.Customer
.Where(CustomerFields.Country=="Germany")
.WithPath(CustomerEntity.PrefetchPath.Orders
.WithSubPath(OrderEntity.PrefetchPathOrderDetails),
CustomerEntity.PrefetchPathVisitingAddress);
customers.GetMulti(q);
' VB.NET
Dim customers As New CustomerCollection()
Dim prefetchPath As New PrefetchPath(CInt(EntityType.CustomerEntity))
prefetchPath.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails)
prefetchPath.Add(CustomerEntity.PrefetchPathVisitingAddress)
Dim filter As New PredicateExpression(CustomerFields.Country = "Germany")
customers.GetMulti(filter, prefetchPath)
' QuerySpec alternative
Dim customers As New CustomerCollection()
Dim qf As New QueryFactory()
Dim q = qf.Customer _
.Where(CustomerFields.Country="Germany") _
.WithPath(CustomerEntity.PrefetchPath.Orders _
.WithSubPath(OrderEntity.PrefetchPathOrderDetails), _
CustomerEntity.PrefetchPathVisitingAddress)
customers.GetMulti(q)
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
PrefetchPathElement objects in a Prefetch Path, however
consider that each level in a graph is a separate query.
Optimizing Prefetch Paths
The LLBLGen Pro runtime libraries create for a Prefetch Paths per node a sub-query, 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#
IPrefetchPath path = new PrefetchPath(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);
' VB.NET
Dim path As IPrefetchPath = New PrefetchPath(CInt(EntityType.CustomerEntity))
path.Add(CustomerEntity.PrefetchPathOrders);
When the customers are fetched with the filter and the path, using CustomerCollection.GetMulti(), it will produce SQL like the following: (pseudo)
Query to fetch the customers:
SELECT CustomerID, CompanyName, ...
FROM Customers
WHERE 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)
LLBLGen Pro offers you to tweak this query generation by specifying a threshold,
DaoBase.ParameterisedPrefetchPathThreshold, what
the runtime libraries should do: produce a subquery with a select or a sub-query with a range of values. The value set for
ParameterisedPrefetchPathThreshold specifies at which amount of the parent entities (in our example, the customer entities) it has to switch to a subquery with a select.
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 practise and it made
queries run faster. This to prevent you're using slower queries than necessary. The setting is global, so after setting the threshold, it affects all
prefetch path related fetches of the application.
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#
EmployeeCollection employees = new EmployeeCollection();
IPrefetchPath prefetchPath = new PrefetchPath(EntityType.EmployeeEntity);
// specify the actual path: BoardMember - CompanyCar
prefetchPath.Add(BoardMemberEntity.PrefetchPathCompanyCar);
// .. fetch code
' VB.NET
Dim employees As New EmployeeCollection()
Dim prefetchPath As New PrefetchPath(CInt(EntityType.EmployeeEntity))
' specify the actual path: BoardMember - CompanyCar
prefetchPath.Add(BoardMemberEntity.PrefetchPathCompanyCar)
' .. fetch code
LLBLGen Pro will then 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 subpaths 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:
PrefetchPath path = new PrefetchPath(EntityType.CustomerEntity);
IPrefetchPathElement orderElement = path.Add(CustomerEntity.PrefetchPathOrders);
orderElement.SubPath.Add(OrderEntity.PrefetchPathOrderDetails); // branch 1
orderElement.SubPath.Add(OrderEntity.PrefetchPathEmployee); // branch 2
Dim path As New PrefetchPath(CInt(EntityType.CustomerEntity))
Dim orderElement As IPrefetchPathElement = 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
PrefetchPathElement object, and are also
accessible through overloads of the
PrefetchPath.
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#
EmployeeCollection employees = new EmployeeCollection();
IPrefetchPath prefetchPath = new PrefetchPath(EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter);
employees.GetMulti(null, prefetchPath);
// QuerySpec alternative
var employees= new EmployeeCollection();
var qf = new QueryFactory();
var q = qf.Employee
.WithPath(EmployeeEntity.PrefetchPathOrders
.WithOrdering(OrderFields.OrderDate.Descending())
.WithLimit(1));
employees.GetMulti(q);
' VB.NET
Dim employees As New EmployeeCollection()
Dim prefetchPath As New PrefetchPath(CInt(EntityType.EmployeeEntity))
Dim sorter As New SortExpression()
sorter.Add(New SortClause(OrderFields.OrderDate, SortOperator.Descending))
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, Nothing, Nothing, sorter)
employees.GetMulti(Nothing, prefetchPath)
' QuerySpec alternative
Dim employees as New EmployeeCollection()
Dim qf As New QueryFactory()
Dim q = qf.Employee _
.WithPath(EmployeeEntity.PrefetchPathOrders _
.WithOrdering(OrderFields.OrderDate.Descending()) _
.WithLimit(1))
employees.GetMulti(q)
Besides a sort expression, you can specify a
RelationCollection together with a
PredicateExpression when you add a
PrefetchPathElement to the
PrefetchPath 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
GetMulti() method call.
// C#
CustomerCollection customers = new CustomerCollection();
PredicateExpression customerFilter = new PredicateExpression();
RelationCollection relations = new RelationCollection();
// fetch all customers which have orders shipped to brazil.
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
customerFilter.Add(OrderFields.ShipCountry=="Brazil");
// load for all customers fetched their orders.
PrefetchPath path = new PrefetchPath(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);
// perform the fetch
customers.GetMulti(customerFilter, 0, null, relations, path);
' VB.NET
Dim customers As New CustomerCollection()
Dim customerFilter As New PredicateExpression()
Dim relations As New RelationCollection()
' fetch all customers which have orders shipped to brazil.
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId)
customerFilter.Add(OrderFields.ShipCountry="Brazil")
' load for all customers fetched their orders.
Dim path As new PrefetchPath(EntityType.CustomerEntity)
path.Add(CustomerEntity.PrefetchPathOrders)
' Perform the fetch
customers.GetMulti(customerFilter, 0, Nothing, relations, path)
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.
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 specifying a primary key in the constructor 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#
IPrefetchPath prefetchPath = new PrefetchPath(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees);
CustomerEntity customer = new CustomerEntity("BLONP", prefetchPath);
' VB.NET
Dim prefetchPath As New PrefetchPath(CInt(EntityType.CustomerEntity))
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees)
Dim customer As New CustomerEntity("BLONP", prefetchPath)
Unique constraint fetch
// C#
CustomerEntity customer = new CustomerEntity();
IPrefetchPath prefetchPath = new PrefetchPath(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees);
customer.FetchUsingUCCompanyName("Blauer See Delikatessen", prefetchPath);
' VB.NET
Dim customer As New CustomerEntity()
Dim prefetchPath As New PrefetchPath(CInt(EntityType.CustomerEntity))
prefetchPath.Add(CustomerEntity.PrefetchPathEmployees)
customer.FetchUsingUCCompanyName("Blauer See Delikatessen", prefetchPath)
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
DaoBase.
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
DaoBase.
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.