Excluding / Including fields for fetches

Sometimes entities have fields which can be big in size; e.g. an Employee entity which has a Photo field which can contain an image, or an Order entity which can contain the Proposal Word document. In other situations, it can be that you only want to fetch a subset of the fields of an entity.

It then would be great if you could exclude one or more fields for the fetch of the entity or collection of entities. LLBLGen Pro allows you to do that: in single entity fetches, entity collection fetches and prefetch path nodes.

In Linq to LLBLGen and QuerySpec, excluding / including fields is part of the query system. For Linq this is explained in its own section: Excluding fields in Linq. For QuerySpec, please see the EntityQuery class.

This section describes the low-level API system using a specific class to specify which fields to exclude/include.

The ExcludeIncludeFieldsList class

To specify which fields to exclude, you have two options:

1. Specify the fields to exclude
2. Specify the fields to fetch (i.e. include) which means the rest is excluded.

To do this, you use the ExcludeIncludeFieldsList class and use its ExcludeContainedFields to specify what LLBLGen Pro should do with the fields contained in the list. By default, ExcludeContainedFields is set to true, which means that all fields added to the instance of ExcludeIncludeFieldsList should be seen as the fields to exclude (thus option 1. in the list above).

When ExcludeContainedFields is set to false, the fields added to the instance of ExcludeIncludeFieldsList should be seen as the fields to fetch (so the rest is excluded, option 2. in the list above). This gives great flexibility to specify the excluded fields. Sometimes the list of fields to exclude is larger than the list of fields to fetch, so in that case use option 2. If the list of fields is smaller than the list of fields to fetch, use option 1.

To fetch an entity or collection of entities with an ExcludeIncludeFieldsList, use one of the overloads for the various fetch methods which accept an ExcludeIncludeFieldsList object.

After you've fetched the entities, for example a set of employees without their Photo or Notes field, it can be you want to fetch that excluded data into the entity object you have in memory. For example you have a grid of Employee entities and when a given Employee entity in that grid is selected, the detail view of the entity requires the Photo and the Notes fields, which weren't fetched for the grid.

LLBLGen Pro offers you to fetch these excluded fields into an entity which was fetched without these fields. The examples below show you how to specify the fields to exclude and also how to fetch the data back into the entities.

Two utility classes are available: ExcludeFieldsList and IncludeFieldsList. Both classes derive from ExcludeIncludeFieldsList, and set the ExcludeContainedFields flag through the constructor. They don't contain any logic, though make it easier for people reading the code if the ExcludeIncludeFieldsList instance contains fields to exclude or fields to include.   

Fetching excluded fields in batches

The following examples first fetch the entities excluding some fields, and then show how to fetch the data back into the existing entities. LLBLGen Pro will do this in batches. So if you have 10,000 entities in memory and you want the data you excluded when fetching these entities fetched into these entities, LLBLGen Pro will break the set of 10,000 up into smaller sets and fetch the excluded fields per batch. ##### Batch size

The batch size is controlled using the DaoBase.ParameterizedPrefetchPathThreshold which is also used for parameterized prefetch paths. This same constant is used as the logic to fetch the excluded fields follows the same pattern. The initial batch size is 5 * the value of that threshold, where the number of primary fields * the number of entities to process is used to determine the number of batches to fetch, as the batch size set controls the number of parameters emitted in the query.

Example: if the entity has a primary key of two fields, and you specify that ParameterizedPrefetchPathThreshold is 10, the initial batch size is 50, and because the number of primary fields is 2, 25 entities per batch are processed, resulting in 2 batches if you have 50 entities to process.

To fetch excluded fields into existing entities, use the method entity.FetchExcludedFields or entitycollection.FetchExcludedFields. Please see the LLBLGen Pro reference manual for overloads on the various fetch methods for entities (single entities and collections) to see which ones accept an ExcludeIncludeFieldsList.

Entity fetch example

The following example uses the default constructor of ExcludeIncludeFieldsList, which sets the ExcludeContainedFields property to true. This means that the fields added to it are meant to be excluded. It will fetch all Northwind customers but with some fields excluded, which are after the fetch loaded into the entities again.

var excludedFields = new ExcludeIncludeFieldsList();
excludedFields.Add(CustomerFields.ContactName);
excludedFields.Add(CustomerFields.Country);

// fetch a collection of customers.
var customers = new CustomerCollection();
var sorter = new SortExpression(CustomerFields.CustomerId.Descending());
customers.GetMulti(null, 0, sorter, null, null, excludedFields, 0, 0);

// fetch a single customer 
var c = new CustomerEntity();
c.FetchUsingPK("CHOPS", null, null, excludedFields);

// ...

// load the excluded fields into the entities already loaded:
c.FetchExcludedFields(excludedFields);
customers.FetchExcludedFields(excludedFields);

Dim excludedFields As New ExcludeIncludeFieldsList()
excludedFields.Add(CustomerFields.ContactName)
excludedFields.Add(CustomerFields.Country)

' fetch a collection of customers.
Dim  customers As New CustomerCollection()
Dim sorter As New SortExpression(CustomerFields.CustomerId.Descending())
customers.GetMulti(Nothing, 0, sorter, Nothing, Nothing, excludedFields, 0, 0)

' fetch a single customer 
Dim c As New CustomerEntity()
c.FetchUsingPK("CHOPS", Nothing, Nothing, excludedFields)

' ...

' load the excluded fields into the entities already loaded:
c.FetchExcludedFields(excludedFields)
customers.FetchExcludedFields(excludedFields)

Prefetch path example

Excluding fields for fetches also works with prefetch paths. The following example illustrates that.

var excludedFields = new ExcludeIncludeFieldsList();
excludedFields.Add(OrderFields.OrderDate);

// Fetch the first 25 customers with their orders which have the OrderDate excluded.
var path = new PrefetchPath(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders, excludedFields);

var customers = new CustomerCollection();
customers.GetMulti(null, 25, null, null, path, excludedFields, 0, 0);

Dim excludedFields As New ExcludeIncludeFieldsList()
excludedFields.Add(OrderFields.OrderDate)

' Fetch the first 25 customers with their orders which have the OrderDate excluded.
Dim path As New PrefetchPath(EntityType.CustomerEntity)
path.Add(CustomerEntity.PrefetchPathOrders, excludedFields)
Dim customers As New CustomerCollection()

customers.GetMulti(Nothing, 25, Nothing, Nothing, path, excludedFields, 0, 0)