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.
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.
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:
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
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.
Every predicate expression created, is wrapped in
() in the SQL query. This makes it easy to
make sure the
OR operators are applied to the right operands. In the example above,
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
And call in
(). Strictly this isn't necessary but for readability of the code it makes things more
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.
var companyNameField = CustomerFields.CompanyName;
To set an aggregate function, an expression (See Field expressions and
aggregates) or an object
alias, you can use the QuerySpec extension methods like
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);