Getting started with filtering

One of the most powerful aspects of the generated code and the framework it forms is the ability to formulate filters (predicates) and sort-clauses directly in your code and let the code evaluate them at runtime. This means that once the code 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 and filters are fully parameterized. This makes sure execution plans are preserved by the database server's optimizer and at the same time the filters are not constructed with values concatenated into the SQL query itself, so no risks for SQL injection attacks.

This section describes the low-level API on which the Linq to LLBLGen Pro provider and the QuerySpec system are build on. It's important to know the basics of predicate classes and how to construct them to fully utilize the power of the full LLBLGen Pro runtime framework, even if you're using Linq or QuerySpec in most of your queries; some functionality, like updates based on filters, use predicates directly and require basic knowledge of the predicate system.

Difference between Adapter and SelfServicing

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 class which accept a filter, do this by accepting a RelationPredicateBucket object.

SelfServicing uses the RelationCollection and PredicateExpression objects separately.

QuerySpec extension methods

The QuerySpec query API offers a set of extension methods which make it easier to formulate predicates and predicate expressions. To utilize these in your code, add the following line to the top of your code file:

C#VB.NET

using SD.LLBLGen.Pro.QuerySpec;

Imports SD.LLBLGen.Pro.QuerySpec

The examples in this section will use these extension methods. See for more examples of what extension methods are available to you, the QuerySpec section, or the LLBLGen Pro RTL Reference Manual, and check the extension methods on the classes EntityField (SelfServicing), EntityField2 (Adapter), Predicate and Expression.

Predicates and Predicate expressions

A predicate is effectively a clause used in a WHERE statement which will result in True or False, the keyword 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'. Furthermore, predicates can be negated using 'Not'.Filtering is the same for entities, typed views and typed lists as well as dynamic lists and projections of database data.

Below is an example of a nested WHERE clause with some predicates.

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

To construct the predicate expression, we do the following:

IPredicateExpression whereClause = ((Table1Fields.Foo == "One").And(Table1Fields.Bar == "Two"))
                                    .Or(Table2Fields.Bar2 == "Three");

To use it in Adapter, we add it to a RelationPredicateBucket:

// Adapter
var filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(whereClause);

To use it in SelfServicing, we can simply use the whereClause object as-is.

Brackets and scoping

Every predicate expression created, is wrapped in () in the SQL query. This makes it easy to make sure the AND and OR operators are applied to the right operands. In the example above, the And() method creates a PredicateExpression with the two operands the extension method works on: the predicate it's called on (Table1Fields.Foo == "One") and the predicate specified as argument: Table1Fields.Bar == "Two". To make sure the Or() method is applied to the result of And, we wrap the And call in (). Strictly this isn't necessary but for readability of the code it makes things more clear.

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. LLBLGen Pro offers a convenient way to produce entity field objects: entitynameFields.FieldName, and typedviewnameFields.FieldName.

Example:

var companyNameField = CustomerFields.CompanyName;

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 the QuerySpec extension methods like .As(string) and .Sum(value), on EntityField (SelfServicing) or EntityField2 (Adapter) objects. You can also use the older methods which use command chaining by using special methods to set the appropriate property: using the EntityField2 methods SetAggregateFunction(), SetExpression() and SetObjectAlias() you can write all assignments in one statement. Below an example for a filter to use in a Having clause:

// SUM(Quantity) > 4 filter
IPredicate filter = (OrderDetailsFields.Quantity.SetAggregateFunction(AggregateFunctions.Sum) > 4);

// using Queryspec extension methods:
IPredicate filterQS = (OrderDetailsFields.Quantity.Sum() > 4);