Saturday, March 17, 2018

Criteria Language Syntax

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.

Expanded 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).FullNameString.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.

Expanded Literals

The following table lists different types of literals supported by the criteria language.
TypeDelimiterDescription
BooleanUse 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.
NumericNumeric 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'

Expanded Operators

The following sections list available operators.
Aggregation Operators
Operators that calculate a single value from the given collection.
NameDescriptionUsage
AvgEvaluates the average of all values in a collection.CriteriaOperator.Parse("Accounts.Avg(Amount) = 75")
CountReturns the number of objects in a collection.CriteriaOperator.Parse("Accounts.Count > 1")
ExistsDetermines whether or not a collection property contains objects.CriteriaOperator.Parse("Accounts.Exists")
MaxReturns the maximum expression value in a collection.CriteriaOperator.Parse("Accounts.Max(Amount) > 75")
MinReturns the minimum expression value in a collection.CriteriaOperator.Parse("Accounts.Min(Amount) < 10")
SingleReturns a single object from a collection.CriteriaOperator.Parse("Accounts.Single() is not null")
SumReturns 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.
NameDescriptionUsage
BitwiseAndThe bitwise AND operator.CriteriaOperator.Parse("Roles & 1 = 1")
BitwiseOrThe bitwise OR operator.CriteriaOperator.Parse("Roles | 253 = 255")
BitwiseXorThe bitwise XOR operator.CriteriaOperator.Parse("Roles ^ 253 = 255")
DivideThe division operator.CriteriaOperator.Parse("Accounts.Max(Amount) / Accounts.Min(Amount) > 10")
EqualThe Boolean equality operator.CriteriaOperator.Parse("Name = 'John'")
GreaterThe Boolean greater-than operator.CriteriaOperator.Parse("Age > 20")
GreaterOrEqualThe Boolean greater-than-or-equal-to operator.CriteriaOperator.Parse("Age >= 20")
LessThe Boolean less-than operator.CriteriaOperator.Parse("Age < 20")
LessOrEqualRepresents the Boolean less-than-or-equal-to operator.CriteriaOperator.Parse("Age <= 20")
LikeDetermines 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 StartsWithContains and EndsWith function operators instead of Like, where possible.
CriteriaOperator.Parse("Name like 'Jo%' or Name like '%ob'")
MinusRepresents the subtraction operator.CriteriaOperator.Parse("Age - 30 > 0")
ModuloThe modulus operator (computes the remainder after dividing its first operand by its second).CriteriaOperator.Parse("Age % 50 = 0")
MultiplyThe multiplication operator.CriteriaOperator.Parse("Accounts.Sum(Amount) * 20 >= 3000")
NotEqualThe Boolean inequality operator.CriteriaOperator.Parse("Name != 'John'")
PlusThe addition operator.
Note: You can also use this operator to concatenate strings.
CriteriaOperator.Parse("Age + 50 = 100")
CriteriaOperator.Parse("Name + ToStr(Age)")
Function Operators
NameDescriptionUsage
ConcatConcatenates one or more strings.CriteriaOperator.Parse("Name like Concat('%J', 'o%')")
Contains
Determines if one string occurs within another string.
Requires two string operands:
  1. a string to be searched;
  2. 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.
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:
  1. a string to be searched;
  2. 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.
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.
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:
  1. a string to be searched;
  2. 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.
NameDescriptionUsage
AndGroups operands with logical AND.CriteriaOperator.Parse("Name = 'John' and Age = 30")
OrGroups 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.
NameDescriptionUsage
BitwiseNotRepresents the bitwise NOT operator.CriteriaOperator.Parse("~Roles = 251")
IsNullRepresents the operator that determines whether or not a given expression is NULL.CriteriaOperator.Parse("Name is null")
MinusRepresents the unary negation (-) operator.CriteriaOperator.Parse("-Age = -20")
NotRepresents the logical NOT.CriteriaOperator.Parse("not (Name = 'John' or Age = 30)")
PlusRepresents 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.
C#
VB
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.
C#
VB
XPCollection Orders = new XPCollection();
Orders.Criteria = CriteriaOperator.Parse("^.RegistrationDate == Date");
Miscellaneous Operators
NameDescriptionUsage
BetweenDetermines whether or not the expression lies between a specified range of values.CriteriaOperator.Parse("Age between (20, 40)")
InDetermines whether or not a value matches any value in the specified list.CriteriaOperator.Parse("Name in ('John', 'Bob', 'Nick')")

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

Expanded 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]

Expanded 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]

Expanded 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]

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

Expanded 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.
C#
VB
XPCollection myCollection = new XPCollection(session);
CriteriaOperator filterOperator = CriteriaOperator.Parse("ExtendedProperty > 0");
myCollection.Filter = filterOperator;
To learn more, refer to Upcasting.

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

Expanded 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.
C#
VB
[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.
C#
VB
[PersistentAlias("Iif(Part is null, MyCustOrderLine, MyCustOrderLine2).Part")]
public Part CustomPart {
    get {
        return (Part)EvaluateAlias("CustomPart");
    }
}

Expanded See Also

No comments: