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.

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.

...
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:

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()

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 (and not string), a FieldCompareRangePredicate instance is created.

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)
  Creates

  EXISTS (correlated subquery)
  

• query.All(predicate)
  Creates

  NOT 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.

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:

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;

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:

  FIELD LIKE op1
  

  Using Like requires op1 to contain the proper wildcards.

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);