SqlFilter.SqlExpression Property

The following information describes the possible values for the SqlExpression property. A valid predicate is defined as follows. These values align with SQL92 standard and T/SQL:

<predicate ::=
{ NOT <predicate> }
| <predicate> AND <predicate>
| <predicate> OR <predicate>
| <expression> { = | <> | != | > | >= | < | <= } <expression>
| <property> IS [NOT] NULL
| <expression> [NOT] IN ( <expression> [, ...n] )
| <expression> [NOT] LIKE <pattern> [ESCAPE <escape_char>]
| EXISTS ( <property> )
| ( <predicate> )
 
<expression> ::=
<constant> 
| <property>
| <expression> { + | - | * | / } <expression>
| { + | - } <expression>
| ( <expression> )

Syntax

<property> := 
 [<scope> .] <property_name>

Arguments

<scope>

An optional string indicating the scope of <property_name>. Valid values are sys or user. sys indicates system scope in which <property_name> is a public property name of the BrokeredMessage class. user indicates user scope where <property_name> is a key of the Properties dictionary. user scope is the default scope if <scope> is not specified.

Remarks

• An attempt to access a non-existent system property is an error. An attempt to access a non-existent user property is not an error. Instead, the non-existing user property is evaluated as an unknown value. An unknown value is treated specially during operator evaluation.

Syntax

<property_name> ::=
<property_name_part>

<scope> ::=
<property_name_part>

<property_name_part> ::=
<identifier>
| <delimited_identifier>

<identifier> ::=
<regular_identifier> | <quoted_identifier> | <delimited_identifier>

Arguments

<regular_identifier>

A string represented by the following regular expression: [[:IsLetter:]][_[:IsLetter:][:IsDigit:]]*. This means any string that starts with a letter and followed by one or more underscore, letter, or digit. [:IsLetter:] is any Unicode character that is categorized as a Unicode letter. System.Char.IsLetter(c) returns true if c is a Unicode letter. [:IsDigit:] is any Unicode character that is categorized as a decimal digit. System.Char.IsDigit(c) returns true if c is a Unicode digit. A <regular_identifier> cannot be a reserved keyword.

<delimited_identifier>

Any string that is enclosed with left and right square brackets ([]). A right square bracket is represented as two right square brackets.

The following are examples of <delimited_identifier>:

Copy

[Property With Space]
[HR-EmployeeID]

<quoted_identifier>

Any string that is enclosed in double quotation marks. A double quotation mark in identifier is represented as two double quotation marks. It is not recommended to use quoted identifiers, because it can easily be confused with a string constant. Use a delimited identifier if possible.

The following are examples of <quoted_identifier>:

Copy

"Contoso & Northwind"

Syntax

<pattern> ::=
<expression>

Remarks

• <pattern> must be an expression that is evaluated as a string. It is used as a pattern for the LIKE operator, and can contain the following wildcard characters:

  • %: Any string of zero or more characters.

  • _: Any single character.

Syntax

<escape_char> ::=
<expression>

Remarks

• <escape_char> must be an expression that is evaluated as a string of length 1. It is used as an escape character for the LIKE operator. For example, property LIKE 'ABC\%' ESCAPE '\' matches ABC% rather than a string that starts with "ABC".

Syntax

<constant> ::=
<integer_constant> | <decimal_constant> | <approximate_number_constant> | <boolean_constant> | NULL

Arguments

<integer_constant>

A string of numbers that is not enclosed in quotation marks and does not contain decimal points. The values are stored as System.Int64 and follow the same range.

The following are examples of long constants:

Copy

1894
2

<decimal_constant>

A string of numbers that is not enclosed in quotation marks, and contains a decimal point. The values are stored as System.Double and follow the same range/precision.

Note
In future versions it is possible that the number will be stored in other data types, to support exact number semantics, so do not rely on the fact that the underlying data type is System.Double for <decimal_constant>.

The following are examples of decimal constants:

Copy

1894.1204
2.0

<approximate_number_constant>

A number written in scientific notation. The values are stored as System.Double and follow the same range/precision.

The following are examples of approximate number constants:

Copy

101.5E5
0.5E-2

Syntax

<boolean_constant> :=
TRUE | FALSE

Remarks

• Boolean constants are represented by the keyword TRUE or FALSE. The values are stored as System.Boolean.

Syntax

<string_constant>

Remarks

• String constants are enclosed in single quotation marks and include any valid Unicode characters. A single quotation mark embedded in a string constant is represented as two single quotation marks.

Note the following semantics for SqlFilter:

• Property names are case-insensitive.

• “IS NULL” returns true if the property doesn’t exist or if the property’s value is null. To check if a property exists, use the EXISTS keyword. For example, the following will return true if aProperty exists:

  SQL

  Copy

  EXISTS(aProperty)
  

• When a property name is used as part of any expression, its value becomes “unknown” if the property does not exist. Any arithmetic operator (for example, +) returns an “unknown” value if any operand is unknown. For example, the following is “unknown” if aMissingProperty is missing:

  Copy

  aMissingProperty + “abc”
  

• Any comparison operators return “unknown” if any operand’s value is evaluated to “unknown”. For example:

  Copy

  aMissingProperty > anExistingProperty
  

  The above expression always returns “unknown” if aMissingProperty does not exist.

• Any logical operators consider three internal values as valid conditions: “true”, “false” and “unknown.” The following table describes these operators’ truth values (T, F, U each represents “true”, “false” and “unknown”):

  AND	T	F	U
  T	T	F	U
  F	F	F	F
  U	U	F	U

  OR	T	F	U
  T	T	T	T
  F	T	F	U
  U	T	U	U

  NOT	-
  T	F
  F	T
  U	U

  EXISTS and LIKE return “unknown” if any operand is “unknown.” IN returns “unknown” if the left operand is “unknown.”

• You can no longer specify a GUID as a valid literal. However, you can still write them in a string literal and you can expect implicit conversion when you assign the value to GUID type property.

• Non-logical operators (such as +, -, * and /) now behave in the same way as the C# operator binding. You should expect exactly the same behavior as C#, except for the “unknown” value handling mentioned above.

• A filter action assignment operator attempts to perform an implicit conversion if an implicit conversion exists, if the left side of a property exists and its value is not null. The exact behavior follows what the System.Convert.ChangeType() function does in the CLR, in addition to some implicit conversions of DateTimeOffset, TimeSpan, Guid, and Uri between strings.

Reference