The predicate system
LLBLGen Pro's filtering capabilities are build using predicate objects, instantiated from predicate classes. This section describes these classes in depth, how to use them, what their purpose is and shows an example for every predicate. These classes are part of the low-level API of the LLBLGen Pro runtime framework.
When using Linq, you'll never run into them directly, the Linq to LLBLGen Pro provider will convert your Linq query to these objects. QuerySpec however uses the predicate classes to construct filters.
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:
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 their SQL equivalents
To quickly understand which predicate class you need, a handy table is provided below, which should help you converting a WHERE construct in SQL to LLBLGen Pro predicates. The predicate classes are then explained further below with examples how to construct them using the available extension methods.
SQL | Predicate class to use |
---|---|
Field BETWEEN 3 AND 5 | FieldBetweenPredicate |
Field BETWEEN field2 AND 4 | FieldBetweenPredicate |
Field = Field2 | FieldCompareExpressionPredicate |
Field < (Field2 + 4) | FieldCompareExpressionPredicate |
Field Is NULL | FieldCompareNullPredicate |
Field IN (1, 2, 3, 5) | FieldCompareRangePredicate |
Field IN (SELECT Field FROM Foo WHERE ...) | FieldCompareSetPredicate |
Field = 3 | FieldCompareValuePredicate |
Field != "Foo" | FieldCompareValuePredicate |
Field LIKE "Foo%" | FieldLikePredicate |
CONTAINS(Field, 'string'..) | FieldFullTextSearchPredicate |
There are also some predicate classes which are usable only for in-memory filtering, e.g. with EntityView(2) instances.
- AggregateSetPredicate. This predicate is usable to filter a set of entities based on the result of an aggregate function executed on related entities. Example: all customers which have at least 5 orders.
- DelegatePredicate. (generic and non-generic variant) This predicate is usable to filter a set of entities based on a function you write yourself. This is the most flexible way to filter entities in memory.
- MemberPredicate. This predicate is usable to filter a set of entities based on an aspect of a related entity or related set of entities. It's a meta-predicate which applies another predicate onto the related member or members and the result of that (true or false) is used to accept or deny an entity in the set to filter.
Constructing Predicate Expressions
To construct predicate expressions, utilize the QuerySpec extension methods .And()
, Or()
and Not()
. The low-level API
also offers other mechanisms but these are more verbose and less flexible. The following example shows the SQL predicates and
the equivalent .NET code how to construct such a predicate expression.
The example shows values in the WHERE clause, LLBLGen Pro always generates parameters for values, it never includes any value into the query directly
...
WHERE (TableFields.Foo = "One" AND TableFields.Foo = "Two")
OR (TableFields.Foo = "Three" AND NOT TableFIelds.Foo = "Four")
The following .NET code constructs the predicate expression to accomplish the aforementioned SQL predicate:
var filter = ((TableFields.Foo=="One").And(TableFields.Foo=="Two"))
.Or((TableFields.Foo=="Three").AndNot(TableFields.Foo=="Four"));
// alternative:
var filter = ((TableFields.Foo=="One").And(TableFields.Foo=="Two"))
.Or((TableFields.Foo=="Three").And(Functions.Not(TableFields.Foo=="Four")));
The predicate classes
All predicate classes implement the IPredicate interface and derive from the Predicate class located in the ORMSupportClasses assembly. If you want to add a specific predicate to the pack already offered, you can: implement a class also deriving from Predicate and you're done.
The following predicate classes are defined in the LLBLGen Pro runtime framework. For each class its purpose is defined as well as SQL equivalents and how to create instances of the class using the available extension methods. Each class has public constructors you can use to construct instances of the predicate classes, however it's recommended you use the shorthand extension methods. All predicates can be used for in-memory filtering in e.g. entity views unless stated otherwise.
FieldBetweenPredicate
This predicate compares the entity field specified using a BETWEEN operator and results
in true if the field's value is greater than or equal to the value
of valueBegin and less than or equal to the value of valueEnd. valueBegin
and valueEnd can be any value or an EntityField2, as shown in the examples.
SQL Equivalent
-- some examples
Field BETWEEN valueStart AND valueEnd
Field BETWEEN OtherField AND valueEnd
Field BETWEEN valueStart AND OtherField
Instantiating a predicate instance
To instantiate a FieldBetweenPredicate, use any of the following methods.
- entity field.Between(op1, op2)
- expression.Between(op1, op2)
Negate variants:
- entity field.NotBetween(op1, op2)
- expression.NotBetween(op1, op2)
op1 and op2 can be: - Entity Field - Literal (e.g. int value, string etc.) - Expression
Examples
// OrderDate BETWEEN dateStart AND dateEND
var pred1 = OrderFields.OrderDate.Between(dateStart, dateEnd));
// ShippedDate BETWEEN RequiredDate and dateEnd
var pred2 = OrderFields.ShippedDate.Between(OrderFields.requiredDate, dateEnd);
// ShippedDate NOT BETWEEN RequiredDate and dateEnd
var pred3 = OrderFields.ShippedDate.NotBetween(OrderFields.requiredDate, dateEnd);
FieldCompareExpressionPredicate
This predicate compares an entity field with an expression, which can be another field, or an LLBLGen Pro expression. See for more information about expressions the Field expressions and aggregates section.
SQL Equivalent
Field > (OtherField * 2)
Field <= OtherField
Instantiating a predicate instance
To instantiate a FieldCompareExpressionPredicate, use the following method.
- entity field.ComparisonMethod(op1)
ComparisonMethod can be:
- Equal
- NotEqual
- GreaterThan
- GreaterEqual
- LesserThan
- LesserEqual
op1 can be:
- Entity Field
- Expression
Operator overloads alternatives
LLBLGen Pro supports also operator overloaded shorthands for the ComparisonMethod. For backwards compatibility this system is still available however it's less robust as it relies on overloaded operators and compiler magic. The following table shows the equivalents:
ComparisonMethod | Overloaded Operator |
---|---|
Equal | == |
NotEqual | != |
GreaterThan | > |
GreaterEqual | >= |
LesserThan | < |
LesserEqual | <= |
It's recommended you use the ComparisonMethod instead of the overloaded operator.
Examples
// SQL: OrderDate = ShippedDate
var pred1 = OrderFields.OrderDate.Equal(OrderFields.ShippedDate);
// SQL: OrderDate <> ShippedDate
var pred2 = OrderFields.OrderDate.NotEqual(OrderFields.ShippedDate);
// SQL: Price > (Quantity * ItemPrice)
var pred3 = OrderFields.Price
.GreaterThan(OrderFields.Quantity.Mul(OrderFields.ItemPrice));
// ---------------
// above examples using operator overloads
// SQL: OrderDate = ShippedDate
var pred1 = OrderFields.OrderDate == OrderFields.ShippedDate;
// SQL: OrderDate <> ShippedDate
var pred2 = OrderFields.OrderDate != OrderFields.ShippedDate;
// SQL: Price > (Quantity * ItemPrice)
var pred3 = OrderFields.Price > (OrderFields.Quantity * OrderFields.ItemPrice);
FieldCompareNullPredicate
This predicate compares a field with the NULL value.
SQL Equivalent
Field IS NULL
Instantiating a predicate instance
To instantiate a FieldCompareNullPredicate, use the following method.
- entity field.IsNull()
Negate variant:
- entity field.IsNotNull()
If you're using VB.NET, be sure to call the IsNull extension method directly instead of on a field as the VB.NET compiler isn't capable of distinguishing between a property and an extension method. So you have to do:
' Wrong, won't compile
.Where(OrderFields.ShipCountry.IsNull())
' Correct, will compile
.Where(IsNullPredicateProducers.IsNull(OrderFields.ShipCountry))
Operator overloads alternatives
LLBLGen Pro supports also operator overloaded shorthands for the IsNull / IsNotNull methods. For backwards compatibility this system is still
available however it's less robust as it relies on overloaded operators and compiler magic. The operators ==
and !=
are overloaded and
when the operand used with these operators is null
, a FieldCompareNullPredicate instance is created.
It's recommended you use the IsNull/IsNotNull methods instead of the overloaded operator.
Examples
// SQL: OrderDate IS NULL
var pred1 = OrderFields.OrderDate.IsNull();
// SQL: OrderDate IS NOT NULL
var pred2 = OrderFields.OrderDate.IsNotNull();
// ---------------
// above examples using operator overloads
// SQL: OrderDate IS NULL
var pred1 = OrderFields.OrderDate==null;
// SQL: OrderDate IS NOT NULL
var pred2 = OrderFields.OrderDate!=null;
FieldCompareRangePredicate
This predicate compares a field with a range of values using an IN() statement. The range is a list of literal values, not a subquery, use FieldCompareSetPredicate for that.
SQL Equivalent
Field IN (1, 2, 5, 10)
Field IN ("Foo", "Bar", "Blah")
Instantiating a predicate instance
To instantiate a FieldCompareRangePredicate, use the following method.
- entity field.In(op1)
Negate variant:
- entity field.NotIn(op1)
op1 can be:
- Any IEnumerable
, where T is the type of the entity field - One or more Literals (e.g. int value, string etc.), separated by a comma.
Operator overloads alternatives
LLBLGen Pro supports also operator overloaded shorthands for the In / NotIn methods. For backwards compatibility this system is still
available however it's less robust as it relies on overloaded operators and compiler magic. The operators ==
and !=
are overloaded and
when the operand used with these operators is an IEnumerable
It's recommended you use the ComparisonMethod instead of the overloaded operator.
Examples
int[] values = new int[3] {1, 2, 5};
// SQL: EmployeeId IN (1, 2, 5)
var pred1 = OrderFields.EmployeeId.In(values);
// SQL: EmployeeId NOT IN (1, 2, 5)
var pred2 = OrderFields.EmployeeId.NotIn(values);
// SQL: EmployeeId IN (1, 2, 5)
var pred3 = OrderFields.EmployeeId.In(1, 2, 5);
// SQL: EmployeeId NOT IN (1, 2, 5)
var pred4 = OrderFields.EmployeeId.NotIn(1, 2, 5);
// ---------------
// above examples using operator overloads
// SQL: EmployeeId IN (1, 2, 5)
var pred1 = OrderFields.EmployeeId == values;
// SQL: EmployeeId NOT IN (1, 2, 5)
var pred2 = OrderFields.EmployeeId != values;
FieldCompareSetPredicate
This predicate compares the entity field specified with the set of values defined by a subquery. FieldCompareSetPredicates also allows you to define EXISTS()
queries.
The FieldCompareSetPredicate supports advanced comparison operators like ANY, ALL and combinations of these with comparison operators like Equal or GreaterThan.
FieldCompareSetPredicate can't be used for in-memory filtering, only in database queries.
SQL Equivalent
Field IN (SELECT OtherField FROM OtherTable WHERE Foo=2)
EXISTS (SELECT * FROM OtherTable)
Instantiating a predicate instance
To instantiate a FieldCompareSetPredicate, use one of the following methods.
-
entity field.In(singleFieldQuery).
Creates:FIELD IN (subquery)
-
Functions.Contains(singleFieldQuery)
Creates:EXISTS (subquery)
-
query.Any(predicate)
CreatesEXISTS (correlated subquery)
-
query.All(predicate)
CreatesNOT EXISTS (negated correlated subquery)
Negate variants:
- entity field.NotIn(query)
query is a normal queryspec query. singleFieldQuery is a queryspec query with a projection of one column (e.g. SELECT fieldx FROM...)
Examples
See QuerySpec for details about formulating queries with QuerySpec.
var qf = new QueryFactory(); // queryspec query factory;
// SQL: CustomerID IN (Select CustomerId FROM Orders WHERE EmployeeId = 2)
var pred1 = CustomerFields.CustomerID.In(
qf.Create()
.Select(OrderFields.CustomerId)
.Where(OrderFields.EmployeeId.Equal(2)));
// SQL: SELECT [E].Name FROM Employees [E]
// WHERE EXISTS (SELECT * FROM Order WHERE EmployeeId = [E].EmployeeId)
var q = qf.Employees.As("E")
.Where(qf.Order.Any(OrderFields.EmployeeId
.Equal(EmployeeFields.EmployeeId.Source("E"))))
.Select(EmployeeFields.Name.Source("E"));
FieldCompareValuePredicate
This predicate compares the entity field specified with the value specified. If the value to compare is a string, a case sensitive comparison is performed if the database is using a case sensitive collation (like Oracle). You can perform case insensitive compares however, by setting the CaseSensitiveCollation property to true. This will perform the UPPERCASE variant of the field with the value specified. Please note that if you've set CaseSensitiveCollaction to true, you have to specify your value in uppercase as well.
The FieldCompareValuePredicate detects value truncation for variable string typed parameters. If it detects value truncation, it will emit 1=0
instead of the actual predicate, followed by a comment. This 1=0
predicate is to make sure the predicate result in 0 rows. Value truncation occurs if the DbParameter
's Value property is set to a value which has a length longer than the size
property. The size property of the parameter is set to the length of the field compared against. So if you compare a varchar(20)
typed field F to a string with length 25, you'll get 1=0
instead of the comparison of the first 20 chars of the string with the field, which could lead to matches which are unintentional.
SQL Equivalent
Field > 3
Field = "Foo"
Instantiating a predicate instance
To instantiate a FieldCompareValuePredicate, use the following method.
- entity field.ComparisonMethod(op1)
ComparisonMethod can be:
- Equal
- NotEqual
- GreaterThan
- GreaterEqual
- LesserThan
- LesserEqual
op1 can be:
- Literal (e.g. int value, string etc.)
Operator overloads alternatives
LLBLGen Pro supports also operator overloaded shorthands for the ComparisonMethod. For backwards compatibility this system is still available however it's less robust as it relies on overloaded operators and compiler magic. The following table shows the equivalents:
ComparisonMethod | Overloaded Operator |
---|---|
Equal | == |
NotEqual | != |
GreaterThan | > |
GreaterEqual | >= |
LesserThan | < |
LesserEqual | <= |
It's recommended you use the ComparisonMethod instead of the overloaded operator.
Examples
// SQL: EmployeeId = 2
var pred1 = OrderFields.EmployeeId.Equal(2);
// SQL: EmployeeId <> 2
var pred2 = OrderFields.EmployeeId.NotEqual(2);
// SQL, case insensitive comparison on Oracle:
// UPPER(CompanyName)="FOO INC"
var pred3 = CustomerFields.CompanyName.Equal("FOO INC")
.CaseInsensitive();
// ---------------
// above examples using operator overloads
// SQL: EmployeeId = 2
var pred1 = OrderFields.EmployeeId==2;
// SQL: EmployeeId <> 2
var pred2 = OrderFields.EmployeeId!=2;
To have a case insensitive comparison you can use the extension method CaseInsensitive()
on the predicate object to make it a case insensitive comparison:
IPredicate p = CustomerFields.CompanyName.Equal("FOO INC").CaseInsensitive();
FieldFullTextSearchPredicate (SqlServer specific)
This SQL Server specific predicate compares the entity field specified with the pattern specified using the FullTextSearch operator specified. This predicate has no QuerySpec shortcut extension method: to use it, use one of the constructors of the class. This predicate can't be used for in-memory filtering, only in database queries.
SQL Equivalent
CONTAINS(Field, "Bla")
FREETEXT(Field, "Bla")
Instantiating a predicate instance
To instantiate a FieldFullTextSearchPredicate, use its constructor.
Examples
// SQL: CONTAINS(CompanyName, "Solution")
// Adapter
var pred1 = new FieldFullTextSearchPredicate(
CustomerFields.CompanyName, null,
FullTextSearchOperator.Contains, "Solution"));
// SelfServicing
var pred1 = new FieldFullTextSearchPredicate(
CustomerFields.CompanyName,
FullTextSearchOperator.Contains, "Solution"));
// SQL: CONTAINS((CompanyName, ContactName), "Solution")
var pred2 = new FieldFullTextSearchPredicate(
new[] { CustomerFields.CompanyName, CustomerFields.ContactName},
FullTextSearchOperator.Contains, "Solution"));
FieldLikePredicate
This predicate compares the entity field specified with the pattern specified, using the SQL LIKE
operator. If you use the class directly using its constructor, be sure the pattern contains the wildcard required, which is '%' (also for MS Access). FieldLikePredicate performs a LIKE compare using the case sensitivity setting of the database system the query is executed on: the SQL generated does not contain any collation information nor any case insensitive setting if the database is using case sensitive comparison operations by default (Oracle, some SqlServer installations).
You can perform case insensitive compares however, if the database is case sensitive, by setting the CaseSensitiveCollation property. This will perform the UPPERCASE variant of the field with the pattern specified. Please note that if you've set CaseSensitiveCollaction to true, you've to specify your pattern in uppercase as well.
SQL Equivalent
Field LIKE '%bla'
Field LIKE 'bla%'
Instantiating a predicate instance
To instantiate a FieldLikePredicate, use one of the following methods.
- entity field.Method(op1)
- expression.Method(op1)
Method can be:
-
StartsWith
Creates:FIELD LIKE 'op1%'
-
EndsWith
Creates:FIELD LIKE '%op1'
-
Contains
Creates:FIELD LIKE '%op1%'
-
Like
Creates:
Using Like requires op1 to contain the proper wildcards.FIELD LIKE op1
op1 can be:
- Literal string
Operator overloads alternatives
LLBLGen Pro supports also operator overloaded shorthands for the ComparisonMethod. For backwards compatibility this system is still
available however it's less robust as it relies on overloaded operators and compiler magic. The operator %
has been overloaded and
when a string operand is used with this operator, a FieldCompareRangePredicate instance is created.
It's recommended you use the ComparisonMethod instead of the overloaded operator.
Examples
// SQL: CompanyName LIKE 'Solution%'
var pred1 = CustomerFields.CompanyName.StartsWith("Solution");
// SQL: CompanyName LIKE '%Solution%'
var pred2 = CustomerFields.CompanyName.Contains("Solution");
// SQL: CompanyName NOT LIKE 'Solution%'
var pred3 = Functions.Not(CustomerFields.CompanyName.StartsWith("Solution"));
// SQL: UPPER(CompanyName) LIKE 'SOLUTION%'
var pred4 = CustomerFields.CompanyName.StartsWith("SOLUTION")
.CaseInsensitive();
// SQL: CompanyName LIKE 'Solution%'
var pred5 = CustomerFields.CompanyName.Like("Solution%");
// ---------------
// above examples using operator overloads
// SQL: CompanyName LIKE 'Solution%'
var pred1 = CustomerFields.CompanyName % "Solution%");
// SQL: CompanyName LIKE '%Solution%'
var pred2 = CustomerFields.CompanyName % "%Solution%");
Usage in in-memory filters
When used in in-memory filters, the pattern can either be a normal LIKE statement pattern with '%' wildcards, or it can be a full regular expression. If the pattern is a regular expression, be sure to set the property PatternIsRegEx to true.
Predicate classes for in-memory filtering only
Below you'll find the predicate classes which are only usable for in-memory filtering.
AggregateSetPredicate
Predicate class which performs an aggregate function on a set of entities and returns true or false depending if that aggregated value matches a specified expression. The set of entities this predicate is applied on, for example a collection of OrderEntity instances, are the elements of a member property with the name specified which match the specified filter, for example 'Orders' in a set of CustomerEntity instances.
Example
This example filters a set of customers to find all customers with at least 10 orders. customers is a collection of CustomerEntity instances.
// Adapter
IPredicate filter = new AggregateSetPredicate(
CustomerEntity.MemberNames.Orders, // member to apply the aggregate on
AggregateSetFunction.Count, // aggregate function to apply.
OrderFields.OrderId, // value producer. Can be a field as well.
ComparisonOperator.GreaterThan, // comparison operator for the aggregateset
10, // value to compare with
null); // additional filter to apply to the set.
var filteredCustomers = new EntityView2<CustomerEntity>(customers, filter);
DelegatePredicate and DelegatePredicate(Of T)
Predicate class to filter in-memory entity collections based on a specified callback function. Use this predicate to filter entities based on logic which is best expressed in a normal .NET language, like C# or VB.NET. The generic variant accepts a Lambda expression which is then used as a filter.
Example
This example filters a set of customers which have a CompanyName with length of 20 or higher. customers is a collection of CustomerEntity instances.
// Create an entity view from customers based on the DelegatePredicate filter.
var customersWithLongName = new EntityView2<CustomerEntity>(customers,
c=>c.CompanyName.Length > 20);
MemberPredicate
Predicate class which allows in-memory filters to perform a predicate (filter) on one or more related entities. The entity this predicate is applied on has to have a member property with the name specified. Each element in that member (or the member itself, in case of a single instance) will be interpreted with the specified filter. The result of that interpretation is used together with the MemberOperator specified what the result of this predicate will be: true or false, in which case the entity this predicate is applied on is accepted (true) or not (false).
Example
This example shows in memory filtering of customers which filters all customers which have orders with a total > 5000. The order total is calculated by SUMming the orderdetail total values. The order detail total is (UnitPrice * quantity) - ((UnitPrice * quantity)* discount)
. customers is a collection of CustomerEntity instances.
IPredicate filter = new MemberPredicate(
CustomerEntity.MemberNames.Orders, // the member to apply the specified filter on.
MemberOperator.Any, // operator for applying contained filter.
new AggregateSetPredicate( // aggregate predicate to apply on a member
OrderEntity.MemberNames.OrderDetails, // member to apply the aggregate on
AggregateSetFunction.Sum, // aggregate function to apply.
OrderDetailsFields.UnitPrice.Mul(OrderDetailsFields.Quantity)
.Sub(OrderDetailsFields.UnitPrice.Mul(OrderDetailsFields.Quantity)
.Mul(OrderDetailsFields.Discount)), // value producer expression.
ComparisonOperator.GreaterThan, // comparison operator for the aggregateset
5000.0M, // value to compare with
null // filter which specifies the set to apply the aggregate on.
)
);
IEntityView2 filteredCustomers = new EntityView2<CustomerEntity>(customers, filter);