Criteria Objects and Dynamic Queries
Criteria Objects
There is a chance that you will need to create complicated WHERE statements while developing dynamic SQL for SELECT, UPDATE, or DELETE. Although it is possible to construct these statements using string concatenation, it is time-consuming to avoid grammatical problems and exposes your code to SQL injection attempts. SQL injection issues might be resolved by using parameters, however adding parameters requires too much human labour.
Fortunately, Serenity's criterion system makes it easy to create parameterized queries using a syntax that resembles LINQ expression trees.
Unlike LINQ, which employs expression trees, serenity criteria are achieved by utilising the operator overloading characteristics of C#.
Operator in use
AND (&) Operator
It is possible to AND two criteria objects with C# & operator:
new Criteria("Field1 > 5") & new Criteria("Field2 < 4")
With the operator (AND) and a reference to these two criterion, this creates a new criteria object (BinaryCriteria). The initial criteria items are not changed.
OR (|) Operator
This is similar to AND operator, though it uses OR.
new Criteria("Field1 > 5") | new Criteria("Field2 < 4")
Parenthesis Operator (~)
You may want to use parenthesis when employing multiple AND/OR statements.
new Criteria("Field1 > 5") & (new Criteria("Field2 > 7") | new Criteria("Field2 < 3"))
Comparison Operators (>, >=, <, <=, ==, !=)
The most of C# comparison operators are overloaded, so you can use them as is with criteria.
new Criteria("Field1") == new Criteria("1") &
new Criteria("Field2") != new Criteria("2") &
new Criteria("Field3") > new Criteria("3") &
new Criteria("Field4") >= new Criteria("4") &
new Criteria("Field5") < new Criteria("5") &
new Criteria("Field6") <= new Criteria("6")
Inline Values
It is turned to a parameter criteria when one side of a comparison operator is a criteria and the other side is an integer, string, date, GUID, etc. value.
new Criteria("Field1") == 1 &
new Criteria("Field2") != "ABC" &
new Criteria("Field3") > DateTime.Now &
new Criteria("Field4") >= Guid.NewGuid() &
new Criteria("Field5") < 5L
ParamCriteria and Explicit Param Names
If you want to use some explicitly named parameter, you can make use of ParamCriteria:
var myParam = new ParamCriteria("@myparam");
new Criteria("SomeField") <= myParam
ConstantCriteria
ConstantCriteria objects can be used to store your values if you don't want to utilise parameterized queries. They won't be changed into automatic settings.
new Criteria("Field1") == new ConstantCriteria(1) & new Criteria("Field2") != new ConstantCriteria("ABC")
Null Comparison
new Criteria("a").IsNull();
new Criteria("a").IsNotNull();
int? b = 5;
new Criteria("c") == b.Value;
LIKE Operators
Criteria has methods Like, NotLike, StartsWith, EndsWith, Contains, NotContains to help with LIKE operations.
new Criteria("a").Like("__C%") &
new Criteria("b").NotLike("D%") &
new Criteria("c").StartsWith("S") &
new Criteria("d").EndsWith("X") &
new Criteria("e").Contains("This") &
new Criteria("f").NotContains("That")
IN and NOT IN Operators
Use an inline array to use IN or NOT IN:
new Criteria("A").In(1, 2, 3, 4, 5)
new Criteria("A").NotIn(1, 2, 3, 4, 5)
IEnumerable<int> x = new int[] { 1, 3, 5, 7, 9 };
new Criteria("A").In(x);
It is also possible to use a subquery:
var query = new SqlQuery()
.From("MyTable")
.Select("MyField");
query.Where("SomeID").In(
query.SubQuery()
.From("SomeTable")
.Select("SomeID")
.Where(new Criteria("Balance") < 0));
NOT Operator
Use C# ! (not) operator to use NOT:
!(new Criteria("a") >= 5)
NOT (a >= @p1) -- @p1 = 5
Usage with Field Objects
