Generated code - Getting started with filtering, Adapter

Preface

One of the most powerful aspects of the generated code and the framework it forms is the ability to formulate filters and sortclauses directly in your code and let the code evaluate them at runtime. This means that once the framework has been generated, developers working on business logic code can formulate specific filters to request only that information necessary for the task they're currently working on, without the requirement of a given filter in a special stored procedure.

When filters and sort clauses are used to fetch data from the persistent storage (database), the filters and sort clauses are transformed to SQL and embedded into the actual SQL query by the used Dynamic Query Engine (see Dynamic SQL) and filters are fully parameterized, thus execution plans are preserved in the database server's optimizer while the filters are not constructed with values concatenated into the SQL query itself, so no risks for SQL injection attacks.

When filters and sort clauses are used in combination of EntityView2 objects (See: Generated code - using the EntityView2 class) they're interpreted in-memory and not converted to SQL. Not all filter constructs available to you for usage with a database are available to you when you're filtering data in-memory. When a predicate class is usable for in-memory usage, it's mentioned with the predicate class. In-memory filtering doesn't use relations, just predicates.

This section describes the different ways of constructing filters using predicates and predicate expressions and how to use multi-entity spanning filters as well, using RelationCollection objects. It furthermore tells you how to construct sort clauses to sort the data you requested. The generated code contains factory classes for most predicates and all sort clauses to ease the use of creating the objects.

Adapter uses RelationPredicateBucket objects to combine a RelationCollection object and a PredicateExpression object in a single object, instead of the SelfServicing approach of having these separated. All methods of the DataAccessAdapter object which accept a filter, do this by accepting a RelationPredicateBucket object.

The first subsection describes in an abstract way the philosophy behind the predicate objects. This abstract discussion might look a little complex at first but it describes the way predicates can and should be organized into predicate expressions and when to do that to get the results you want.

All predicate construction methods of LLBLGen Pro are compile time checked. This means that if you for example rename a field in the designer, regenerate your code, and recompile your projects, you'll be notified by the compiler where you used the old name and thus which lines you have to update. This is crucial for reliable software development.

Upgrading from v1.0.200x.y: no PredicateFactory

In previous versions of LLBLGen Pro, v1.0.2005.1 and earlier, by default a class called PredicateFactory was generated. This class contained for most predicate classes a convenient construction method for each field in each entity. In larger projects however this lead to a very big class which was unusable in VS.NET due to the high number of overloads of a single method. In v2.0 of LLBLGen Pro this class is no longer generated by default and is discouraged to be used in your code. You can still generate this class however, simply enable to PredicateFactory generation task in the run queue of your preset of choice (See: Designer - Generating code).

This documentation will avoid the usage of the PredicateFactory class, unless stated otherwise. If you need information about the PredicateFactory class, please consult the documentation of v1.0.2005.1, still available at our website.

Predicates and Predicate expressions

Filtering is the same for entities, typed views and typed lists. To construct a predicate expression add predicate and PredicateExpression objects to the PredicateExpression object exposed by the RelationPredicateBucket class. You can then pass the PredicateExpression object as a parameter to a method which retrieves data or works on data. A predicate is effectively a clause used in a WHERE statement which will result in True or False, 'WHERE' itself is not part of the predicate. Predicates can be grouped in a predicate expression. Predicate expressions can also be grouped inside other predicate expressions. Predicates are placed inside a predicate expression with the operators 'And' and 'Or'. Predicate expressions can also be placed inside another predicate expression with the operators 'And' and 'Or'. This might sound a little complex, so let's illustrate with an example of a nested WHERE clause with some predicate expressions.

... Some Select statement
WHERE
	(	
		Table1.Foo = @param1
		AND
		Table1.Bar = @param2
	)
	OR
	Table2.Bar2 = @param3

(Table1.Foo = @param1 AND Table1.Bar = @param2) OR Table2.Bar2 = @param3.

The following predicates are found in this filter:

• Table1.Foo = @param1
• Table1.Bar = @param2
• Table2.Bar2 = @param3

There are 2 predicate expressions found:

1. (Table1.Foo = @param1 AND Table1.Bar = @param2)
2. (Table1.Foo = @param1 AND Table1.Bar = @param2) OR Table2.Bar2 = @param3

To formulate the filter correctly, we start by constructing an empty RelationPredicateBucket instance, B, which has a new, empty PredicateExpression object. Let's assume param1 has the value "One", param2 has the value "Two" and param3 has the value "Three".


The easiest way to proceed is then to construct predicate expression A:


A is now constructed and we can add this predicate expression as a single predicate to the predicate expression B:


There is one predicate left, OR Table2.Bar2 = @param3. Let's add that one with the Or operator directly to B:


B now has been filled with the complete filter. To sum it up, below are the complete sections of code to construct the complete predicate expression


There is no maximum set for the number of predicate objects you can add to a predicate expression, nor has a maximum been set for the number of predicate expressions you can nest into each other. As a rule of thumb, every set of predicates that should be grouped together as a single boolean expression should be placed in a separate PredicateExpression object: the complete contents of a PredicateExpression object will be placed inside a '()' pair to group the predicates physically in the SQL query.

Creating and working with field objects

The filtering system of LLBLGen Pro uses predicate classes, which use entity field objects (or typed view field objects) to work with. Starting with version 1.0.2005.1, LLBLGen Pro offers a convenient way to produce entity field objects: entitynameFields.FieldName, and typedviewnameFields.FieldName. Example:


In earlier versions you needed to use:


To utilize this feature, please add the following code to your code file:


In the section The predicate system, filter creation using operator overloading is discussed, which shows how field objects can be utilized together with native C# .NET 1.x/2005 and VB.NET 2005 operators to form predicates.

Setting aliases, expressions and aggregates on fields

To set an aggregate function, an expression (See Field expressions and aggregates) or an object alias, you can use command chaining by using special methods to set the appropriate property. Typically when you want to set an aggregate function on a field, your code will look like:


Due to the assignment statement, you can't simply specify a field directly with a predicate class constructor and set the aggregate function, expression or object alias at the same time. However, using the EntityField2 methods SetAggregateFunction(), SetExpression and SetObjectAlias you can. Below an example for a filter to use in a Having clause:


Please note that VB.NET 2002/2003 doesn't support operator overloading and has to use the FieldCompareValuePredicate class for the filter construction.

What to include in a filter

For filtering Typed View objects, you can only filter on one or more fields in the Typed View itself. When you want to filtering a Typed List, you can specify one or more fields which are part of the entities forming the base of the Typed List: you use the field objects of the entities included in the typed list, e.g. CustomerFields.CompanyName. When filtering on entities, using a method to fill an entity collection object, you have two possibilities:

• You filter on fields in the entity type to retrieve. This is the most commonly used type. Example: if you want to retrieve a set of customer objects, you filter on one or more customer fields.
• You filter on fields in a related entity of the entity type to retrieve. This is more advanced, an example of this is illustrated above in multi-entity filters

In all cases, be sure the field you filter on is in the entity type you want to retrieve or, if you use multi-entity filtering, be sure the field(s) in the filter are in any of the entities mentioned in the RelationCollection of the RelationPredicateBucket used.