Criteria Language Syntax
- 14 minutes to read
The criteria language provided by the Cross-Platform Class Library is used in queries for persistent objects built using the CriteriaOperator.Parse method. This method allows you to query for objects using the standard object oriented syntax and also includes several operators used to compare complex values.
Familiar Visual Basic and C# Syntax
The following examples show identical expressions. The first example is similar to the Visual Basic .NET syntax. The second example is similar to C#. The criterion selects persistent objects whose Name property equals to “John” or whose Age property equals 30.
Name = ‘John’ Or Age = 30
Name == ‘John’ || Age == 30
Note
Take special note that you cannot use Visual Basic or C# specific expressions and keywords inside a criteria string like you usually do when writing code in the Visual Studio Code Editor. For instance, your criteria string cannot contain calls of methods and references to variables declared in your classes, expressions like typeof(YourPersistentType).FullName, String.IsNullOrEmpty and other features of managed languages, because they require .NET CLR and other supporting environments to be evaluated at runtime. At the same time, criteria language provides a number of built-in criteria functions for common scenarios as well as the capability to define custom criteria operators.
Literals
The following table lists different types of literals supported by the criteria language.
Type | Delimiter | Description |
|---|---|---|
Boolean |
| Use the ‘True’ or ‘False’ keyword (case-insensitive) to specify a Boolean value. IsMale = True IsMale = False When comparing a Boolean value with true, the ‘True’ keyword can be omitted. IsMale Not IsMale |
DateTime | # | Date and time comparison values. Use the following syntax when checking date/time property values for null.
|
Enumeration |
| Enumeration comparison values. To specify an enumeration value in criteria, use its underlying integer value. Status = 1 Note that you cannot specify an enumeration value using its qualified name. So, the following criteria is incorrect. Status = Status.InProgress For enumerations registered using the static methods of the EnumProcessingHelper class, corresponding criteria values can be serialized in the following way. Status = ##Enum#MyNamespace.Status,InProgress# |
Guid | {} | Guid comparison values. A Guid may only be used in a relational operation with equality or inequality operators. |
Numeric |
| Numeric comparison values. Numeric literals of different types can be specified in a string form using suffixes.
|
String | ‘ | String comparison values. Name == ‘John’ To use the apostrophe within the string literal, add another apostrophe as an escape character. Subject == ‘John’’s Reports’ |
Operators
The following sections list available operators.
Aggregation Operators
Operators that calculate a single value from the given collection.
| Name | Description | Usage |
|---|---|---|
| Avg | Evaluates the average of all values in a collection. | CriteriaOperator.Parse(“Accounts.Avg(Amount) = 75”) |
| Count | Returns the number of objects in a collection. | CriteriaOperator.Parse(“Accounts.Count > 1”) |
| Exists | Determines whether or not a collection property contains objects. | CriteriaOperator.Parse(“Accounts.Exists”) |
| Max | Returns the maximum expression value in a collection. | CriteriaOperator.Parse(“Accounts.Max(Amount) > 75”) |
| Min | Returns the minimum expression value in a collection. | CriteriaOperator.Parse(“Accounts.Min(Amount) < 10”) |
| Single | Returns a single object from a collection. | CriteriaOperator.Parse(“Accounts.Single() is not null”) |
| Sum | Returns a sum of all expression values in a collection. | CriteriaOperator.Parse(“Accounts.Sum(Amount) > 150”) |
Binary Operators
Logical expressions that consist of comparison operations between two operands.
Name | Description | Usage |
|---|---|---|
BitwiseAnd | The bitwise AND operator. | CriteriaOperator.Parse(“Roles & 1 = 1”) |
BitwiseOr | The bitwise OR operator. | CriteriaOperator.Parse(“Roles | 253 = 255”) |
BitwiseXor | The bitwise XOR operator. | CriteriaOperator.Parse(“Roles ^ 253 = 255”) |
Divide | The division operator. | CriteriaOperator.Parse(“Accounts.Max(Amount) / Accounts.Min(Amount) > 10”) |
Equal | The Boolean equality operator. | CriteriaOperator.Parse(“Name = ‘John’”) |
Greater | The Boolean greater-than operator. | CriteriaOperator.Parse(“Age > 20”) |
GreaterOrEqual | The Boolean greater-than-or-equal-to operator. | CriteriaOperator.Parse(“Age >= 20”) |
Less | The Boolean less-than operator. | CriteriaOperator.Parse(“Age < 20”) |
LessOrEqual | Represents the Boolean less-than-or-equal-to operator. | CriteriaOperator.Parse(“Age <= 20”) |
Like | Determines whether a specific character string matches a specified pattern or not. Important This operator behavior is different, depending on current circumstances. We recommend that you use StartsWith, Contains and EndsWith function operators instead of Like, where possible. | CriteriaOperator.Parse(“Name like ‘Jo%’ or Name like ‘%ob’”) |
Minus | Represents the subtraction operator. | CriteriaOperator.Parse(“Age - 30 > 0”) |
Modulo | The modulus operator (computes the remainder after dividing its first operand by its second). | CriteriaOperator.Parse(“Age % 50 = 0”) |
Multiply | The multiplication operator. | CriteriaOperator.Parse(“Accounts.Sum(Amount) * 20 >= 3000”) |
NotEqual | The Boolean inequality operator. | CriteriaOperator.Parse(“Name != ‘John’”) |
Plus | The addition operator. Note: You can also use this operator to concatenate strings. | CriteriaOperator.Parse(“Age + 50 = 100”) CriteriaOperator.Parse(“Name + ToStr(Age)”) |
Function Operators
Name | Description | Usage |
|---|---|---|
Concat | Concatenates one or more strings. | CriteriaOperator.Parse(“Name like Concat(‘%J’, ‘o%’)”) |
Contains | Determines if one string occurs within another string. Requires two string operands:
| CriteriaOperator.Parse(“Contains(String, SubString)”) |
Custom | This operator allows you to implement a custom function that is supported by a specific database engine. To learn more, see the How to: Implement a Custom Criteria Language Operator topic. | CriteriaOperator.Parse(“Custom(‘CustomFunctionName’, [Property], ‘Value’, …)”) Where CustomFunctionName is a mandatory parameter followed by custom function parameters. |
EndsWith | Determines if the end of one string matches another string. Requires two string operands:
| CriteriaOperator.Parse(“EndsWith(String, SubString)”) |
Iif | Returns one of several values depending upon the values of logical expressions. The function can take n operands of the CriteriaOperator class: 1 - determines the first logical expression; 2 - specifies the value that will be returned if the first logical expression evaluates to true; … n-2 - determines the n-2 logical expression; n-1 - specifies the value that will be returned if the n-2 logical expression evaluates to true; n - specifies the value that will be returned if the previously evaluated logical expressions yielded false. | CriteriaOperator.Parse(“Iif(Name = ‘Bob’, 1, Name = ‘Dan’, 2, Name = ‘Sam’, 3, 4)”) |
IsExactType | Determines whether a particular object has a specified type. The function takes 2 operands: 1 - specifies the object whose type will be checked; 2 - specifies the fully qualified name of the required type. See also: IsExactTypeFunction | CriteriaOperator.Parse(“IsExactType(order, ‘MyApplication.DailyOrder’)”) CriteriaOperator.Parse(“IsExactType(This, ?)”, typeof(ForeignEmployee).FullName) |
IsInstanceOfType | Determines whether a particular object is of a specified type or derives from it. The function takes 2 operands: 1 - specifies the object whose type will be checked; 2 - specifies the fully qualified name of the required type. See also: IsInstanceOfTypeFunction | CriteriaOperator.Parse(“IsInstanceOfType(person, ‘MyApplication.Person’)”) CriteriaOperator.Parse(“IsInstanceOfType(This, ?)”, typeof(EmployeeBase).FullName) |
IsNull | Compares the first operand with the null value. This function requires one or two operands of the CriteriaOperator class. The value returned depends upon the number of arguments. If a single operand is passed, the function returns true if the operand is null, otherwise false is returned. If two operands are passed, the function returns the second operand if the first operand is null, otherwise, the first operand is returned. | CriteriaOperator.Parse(“IsNull(MiddleName)”) CriteriaOperator.Parse(“IsNull(MaidenName, LastName) like ‘Do%’”) |
Len | Returns the length of the string specified by an operand. The operand should be an object of the CriteriaOperator type. | CriteriaOperator.Parse(“Len(Name) > 3”) |
Lower | Converts all characters in a string operand to lowercase. The operand should be an object of the CriteriaOperator type. | CriteriaOperator.Parse(“Lower(Name) like ‘%jo%’”) |
StartsWith | Determines if the beginning of one string matches another string. Requires two string operands:
| CriteriaOperator.Parse(“StartsWith(String, SubString)”) |
Substring | Returns a substring extracted from the specified string. This function requires two or three operands of the CriteriaOperator class. If two operands are passed, the substring will be subtracted starting from the given position and ending at the end of the original string. The operands should be defined as follows: 1 - an original string; 2 - an integer that specifies the zero-based index at which the substring to return begins. If three operands are passed, a substring of the specified length will be subtracted starting from the given position in the original string. The operands should be defined as follows: 1 - an original string; 2 - an integer that specifies the zero-based index at which the substring to return begins; 3 - an integer that specifies the length of the substring. | CriteriaOperator.Parse(“Substring(Name, 0, 2) = ‘Bo’”) |
ToStr | Returns a string representation of the specified numeric operand. Note that the resulting string may vary, based on the format settings of the database system used as the backend. | CriteriaOperator.Parse(“ToStr(ZipCode) = ‘14127’”) |
Trim | Returns a string containing a copy of a specified string with no leading or trailing spaces. This function requires a single operand of the CriteriaOperator class that refers to the original string. | CriteriaOperator.Parse(“Trim(Name) = ‘Bob’”) |
Upper | Converts all characters in a string operand to uppercase. The operand should be an object of the CriteriaOperator type. | CriteriaOperator.Parse(“Upper(Name) like ‘%JO%’”) |
Note
The table above lists only basic logical and string management operators. For a complete set of available function operators, refer to the FunctionOperatorType enumeration description.
Group Operators
Logical expressions that group two or more operands with a logical AND or OR.
| Name | Description | Usage |
|---|---|---|
| And | Groups operands with logical AND. | CriteriaOperator.Parse(“Name = ‘John’ and Age = 30”) |
| Or | Groups operands with logical OR. | CriteriaOperator.Parse(“Name = ‘John’ or Age = 30”) |
Join Operators
An operand that joins persistent objects on a condition, calculates aggregate functions against matching objects using their properties, and returns aggregate values as the result of joining.
The following code snippet demonstrates how to retrieve employees that closed more than 50 orders.
CriteriaOperator.Parse(“[<Orders>][^.EmployeeID = EmployeeID].Count() > 50”);
For more information on this operand, refer to Free Joins.
Unary Operators
Unary operators perform operations on a single expression.
| Name | Description | Usage |
|---|---|---|
| BitwiseNot | Represents the bitwise NOT operator. | CriteriaOperator.Parse(“~Roles = 251”) |
| IsNull | Represents the operator that determines whether or not a given expression is NULL. | CriteriaOperator.Parse(“Name is null”) |
| Minus | Represents the unary negation (-) operator. | CriteriaOperator.Parse(“-Age = -20”) |
| Not | Represents the logical NOT. | CriteriaOperator.Parse(“not (Name = ‘John’ or Age = 30)”) |
| Plus | Represents the unary plus (+) operator. | CriteriaOperator.Parse(“Age = +10”) |
Parent Relationship Traversal Operator
The ‘^’ character is used to refer to the parent in a parent-child relationship. The parent relationship traversal operator allows you to access parent objects in expressions written in the context of a child. The parent operator can be applied successively to navigate multiple parent relationships. Consider the following expression.
XPCollection<Customer> Customers = new XPCollection<Customer>();
Customers.Criteria = CriteriaOperator.Parse("Orders[^.RegistrationDate == Date]");
Here, the “RegistrationDate” property refers to the Customer objects and the “Date” property refers to the Order objects. This expression selects all the customers who have made at least one order on the day they registered. The parent relationship traversal operator can only be used within brackets in the context of the ContainsOperator. So, the following expression is incorrect.
XPCollection<Order> Orders = new XPCollection<Order>();
Orders.Criteria = CriteriaOperator.Parse("^.RegistrationDate == Date");
Miscellaneous Operators
| Name | Description | Usage |
|---|---|---|
| Between | Determines whether or not the expression lies between a specified range of values. | CriteriaOperator.Parse(“Age between (20, 40)”) |
| In | Determines whether or not a value matches any value in the specified list. | CriteriaOperator.Parse(“Name in (‘John’, ‘Bob’, ‘Nick’)”) |
Positional Parameters
You can build parameterized criteria using any number of positional parameters. To accomplish this, add parameter placeholders (question mark characters) to a criteria expression to identify parameter positions, and provide a list of parameter values. When building criteria, parameter placeholders are substituted with parameter values in correspondence with the order of parameter values in the list.
CriteriaOperator.Parse(“Name = ? and Age = ?”, “John”, 33)
With positional parameters you can avoid using the String.Format function to format criteria expressions.
Related Property Reference
To refer to a related property, use brackets “[ ]”. For instance, the following query returns all persistent objects that have an Account with an Amount of 100.
Accounts[Amount == 100]
Grouping Clauses with Brackets
When a query traverses an object relationship, it is checked that a related object exists before comparing property values. Because of this behavior, it is important to use brackets to ensure that your query returns the intended results.
For instance, the following query for objects of the Customer type returns all of the customers where an Account exists with a Date of 8/25/2006 and where an account exists with an Amount of 100.
Accounts[Date == #8/25/2006#] && Accounts[Amount == 100]
To search for all customers that have an Account with both a Date of 8/25/2006 and an Amount of 100, construct the query as in the following example
Accounts[Date == #8/25/2006# && Amount == 100]
Operator Precedence
When an expression contains multiple operators, their precedence controls the order in which expression elements are evaluated.
- Literal values
- Parameters
- Identifiers
- OR (left-associative)
- AND (left-associative)
- ‘.’ relationship qualifier (left-associative)
- ==, !=, Like
- <, >, <=, >=
- -, + (left-associative)
- *, /, % (left-associative)
- NOT
- unary -
- In
- Iif
- Trim(), Len(), Substring(), IsNull()
- ‘[]’ (for set-restriction)
- ‘()’
The default precedence can be changed by grouping elements with parentheses. For instance, in the first of the following two code samples, the operators will be performed in the default order. In the second code sample, the addition operation will be performed first, because its associated elements are grouped with parentheses, and the multiplication operation will be performed last.
Accounts[Amount == 2 + 48 * 2]
Accounts[Amount == (2 + 48) * 2]
Case Sensitivity
Operators are case insensitive. The case sensitivity of values depends on the data source.
Note
The behavior of certain operators is affected by the data source. For instance, by default, the SQL Server Express 2005 is configured as case insensitive. In this case, the following expression always evaluates to true and all persistent objects are selected.
Lower(Name) == Upper(Name)
Upcasting
The upcasting feature allows you to access properties of a class different from a collection’s base class. This feature is useful if you have a collection typed for a base class, and in reality it holds objects of a derived class. Since this collection is typed for the base class, you are only able to directly access its properties. To access the properties of the derived class, they must be upcasted.
The following code snippet demonstrates this. The ExtendedProperty is upcasted in the second line to specify that it is a property of the CustomClass derived from the CustomBaseClass.
XPCollection<CustomBaseClass> myCollection = new XPCollection<CustomBaseClass>(session);
CriteriaOperator filterOperator = CriteriaOperator.Parse("<CustomClass>ExtendedProperty > 0");
myCollection.Filter = filterOperator;
To learn more, refer to Upcasting.
Escaping Keywords
If the property name of an object is also a keyword, then you can escape the property name by prefixing it with an @ sign. For instance, in the @Or = ‘value’ query, the CriteriaOperator.Parse method interprets @Or as the property named “Or”, not the logical operator OR.
Retrieving Reference Properties
Note that while a criteria expression can return an object reference, this is not supported in all scenarios. Returning an object reference by directly referencing a property, as in the following code snippet, is fully supported.
[PersistentAlias("Iif(Part is null, MyCustOrderLine.Part, Part)")]
public Part CustomPart {
get {
return (Part)EvaluateAlias("CustomPart");
}
}
In this code snippet, a Part object referenced by the Part or MyCustOrderLine.Part property will be returned correctly. However, retrieving reference properties from function and aggregation operators is not supported. So, the following persistent alias declaration will not work.
[PersistentAlias("Iif(Part is null, MyCustOrderLine, MyCustOrderLine2).Part")]
public Part CustomPart {
get {
return (Part)EvaluateAlias("CustomPart");
}
}