Criteria Language Syntax

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. For nullable properties, NullableDateTimeProperty is null Otherwise, DateTimeProperty = #01/01/0001#
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. Int32 (int) - 1 Int16 (short) - 1s Byte (byte) - 1b Double (double) - 1.0 Single (float) - 1.0f Decimal (decimal) - 1.0m
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: a string to be searched; a substring to search.	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: a string to be searched; a substring to search at the end of the first string.	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: a string to be searched; a substring to search at the beginning of the first string.	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("[][^.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 Customers = new XPCollection();
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 Orders = new XPCollection();
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 myCollection = new XPCollection(session);
CriteriaOperator filterOperator = CriteriaOperator.Parse("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");
    }
}

FXFE

Saturday, March 17, 2018