corelibraries-404016-devexpress-data-library-criteria-cheat-sheet.md
Tip
The Criteria Operators Cheat Sheet Github repo contains all samples of code used on this page.
Evaluates the average of the values in the collection.
Get average value of price property of all orders.
Expression:
CriteriaOperator.FromLambda<Order, double>(x => FromLambdaFunctions.TopLevelAggregate<Order>().Average(c => c.Price));
new AggregateOperand(null, nameof(Order.Price), Aggregate.Avg);
CriteriaOperator.Parse("Avg(Price)");
Test
Input:
|
Name
|
Price
| | --- | --- | |
Item0
|
10
| |
Item1
|
20
|
The result: 15
Get list of orders with average order item price more than 100.
Expression:
CriteriaOperator.FromLambda<Order>(x => x.OrderItems.Average(t => t.ItemPrice) > 100);
var aggrCriterion = new AggregateOperand(nameof(Order.OrderItems), nameof(OrderItem.ItemPrice) , Aggregate.Avg);
var resultCriterion = new BinaryOperator(aggrCriterion, 100, BinaryOperatorType.Greater);
CriteriaOperator.Parse("OrderItems.Avg(ItemPrice)>100");
Test
Input:
|
Order
|
OrderItem
|
OrderItemPrice
| | --- | --- | --- | |
Order0
| | | | |
Item0-0
|
10
| | |
Item0-1
|
20
| |
Order1
| | | | |
Item1-0
|
100
| | |
Item1-1
|
200
| |
Order2
| | | | |
Item2-0
|
30
| | |
Item2-1
|
40
| |
Order3
| | | | |
Item3-0
|
300
| | |
Item3-1
|
400
|
The result:
|
Order
| | --- | |
Order1
| |
Order3
|
Returns the number of objects in a collection.
Get amount of all orders.
Expression:
CriteriaOperator.FromLambda<Order, double>(x => FromLambdaFunctions.TopLevelAggregate<Order>().Count());
new AggregateOperand(null, Aggregate.Count);
CriteriaOperator.Parse("Count()");
Test
Input:
|
Order
| | --- | |
Order0
| |
Order1
|
The result: 2
Get orders with more than 1 order items.
Expression:
CriteriaOperator.FromLambda<Order>(x => x.OrderItems.Count() > 1);
new AggregateOperand(nameof(Order.OrderItems), Aggregate.Count);
CriteriaOperator.Parse("OrderItems.Count()>1");
Test
Input:
|
Order
|
OrderItem
| | --- | --- | |
Order0
| | |
Item0-0
| | |
Item0-1
| |
Order1
| | |
Item1-0
| |
Order2
| | |
Item2-0
| | |
Item2-1
|
The result:
|
Order
| | --- | |
Order0
| |
Order2
|
Get orders that have items with the IsAvailable property set to ‘True’.
Expression:
CriteriaOperator.FromLambda<Order>(x => x.OrderItems.Count(i => i.IsAvailable == true) > 0);
var criterion = new AggregateOperand(nameof(Order.OrderItems), Aggregate.Count, new BinaryOperator(nameof(OrderItem.IsAvailable), true));
var resultCriterion = new BinaryOperator(criterion, 0, BinaryOperatorType.Greater);
CriteriaOperator.Parse("[OrderItems][IsAvailable=true].Count()>0");
Test
Input:
|
Order
|
OrderItem
|
IsAvailable
| | --- | --- | --- | |
Order0
| | |
Item0-0
|
False
| | |
Item0-1
|
False
| |
Order1
| | |
Item1-0
|
True
| | |
Item1-1
|
False
| |
Order2
| | |
Item2-0
|
False
| | |
Item2-1
|
True
|
The result:
|
Order
| | --- | |
Order1
| |
Order2
|
Returns a single object from the AggregateOperand.CollectionProperty that matches the specified AggregateOperand.Condition. The collection must be empty or contain exactly one object.
Get a property’s value of a specific element from a child collection
Expression:
CriteriaOperator.FromLambda<Order, string>(o => o.OrderItems.SingleOrDefault(oi => oi.ItemPrice == 40).OrderItemName);
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.OrderItemName)), Aggregate.Single, new BinaryOperator(nameof(OrderItem.ItemPrice), 40));
CriteriaOperator.Parse("[OrderItems][ItemPrice=40].Single(OrderItemName)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | |
Order0
| | |
OrderItem1
|
30
| | |
OrderItem2
|
40
|
The result: OrderItem2
Get a specific element from a child collection.
Expression:
CriteriaOperator.FromLambda<Order, OrderItem>(o => o.OrderItems.SingleOrDefault(oi => oi.ItemPrice == 40));
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty("This"), Aggregate.Single, new BinaryOperator(nameof(OrderItem.ItemPrice), 40));
CriteriaOperator.Parse("[OrderItems][ItemPrice=40].Single()");
Test
Input:
|
Order
|
OrderItemId
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | --- | |
Order0
| | |
0
|
OrderItem1
|
30
| | |
1
|
OrderItem2
|
40
|
The result: 1
Get a specific element from a child collection and use it in another expression.
Expression:
CriteriaOperator.FromLambda<OrderItem>(oi => oi.Order == FromLambdaFunctions.FreeJoin<Order>(o => o.OrderItems.Any(oii => oii.ItemPrice == 456)).SingleOrDefault());
var criterion = new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), null, Aggregate.Exists, new BinaryOperator(nameof(OrderItem.ItemPrice), 456));
var joinCriterion = new JoinOperand(nameof(Order), criterion, Aggregate.Single, new OperandProperty("This"));
var resultCriterion = new BinaryOperator(nameof(OrderItem.Order), joinCriterion);
CriteriaOperator.Parse("Order=[<Order>][[OrderItems][ItemPrice=456]].Single()");
Test
Input:
|
OrderId
|
Order
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | --- | |
1
|
Order0
| | | |
OrderItem1
|
1
| | | |
OrderItem2
|
456
| | | |
OrderItem3
|
3
| | | |
OrderItem4
|
4
| |
2
|
Order1
| | | |
OrderItem5
|
30
| | | |
OrderItem6
|
40
|
The result:
|
OrderItemName
|
ItemPrice
| | --- | --- | |
OrderItem1
|
1
| |
OrderItem2
|
456
| |
OrderItem3
|
3
| |
OrderItem4
|
4
|
Determines whether the number of elements in a collection is greater than zero.
Check whether a collection has items.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Any());
new AggregateOperand(nameof(Order.OrderItems), Aggregate.Exists);
CriteriaOperator.Parse("OrderItems.Exists()");
Test
Input:
|
Order
|
OrderItemName
| | --- | --- | |
Order0
| | |
OrderItem1
| | |
OrderItem2
|
The result: True
Determines whether the number of elements in a collection that match the criteria is greater than zero.
Check whether a collection has elements that match a criteria.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Any(oi=>oi.ItemPrice==20)); ;
new AggregateOperand(nameof(Order.OrderItems), Aggregate.Exists,new BinaryOperator(nameof(OrderItem.ItemPrice),20));
CriteriaOperator.Parse("[OrderItems][ItemPrice=20]");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | |
Order0
| | |
OrderItem1
|
20
| | |
OrderItem2
|
30
|
The result: True
Returns the maximum value of a collection.
Get the max value from a whole collection.
Expression:
CriteriaOperator.FromLambda<Order, int>(o => o.OrderItems.Max(oi => oi.ItemPrice));
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.ItemPrice)), Aggregate.Max, null);
CriteriaOperator.Parse("OrderItems.Max(ItemPrice)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | |
Order0
| | |
OrderItem1
|
10
| | |
OrderItem2
|
20
|
The result: 20
Get the max value from a filtered collection.
Expression:
CriteriaOperator.FromLambda<Order, int>(o => o.OrderItems.Where(oi => oi.IsAvailable == true).Max(oi => oi.ItemPrice)); ;
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.ItemPrice)), Aggregate.Max, new BinaryOperator(nameof(OrderItem.IsAvailable),true));
CriteriaOperator.Parse("[OrderItems][IsAvailable=True].Max(ItemPrice)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
|
IsAvailable
| | --- | --- | --- | --- | |
Order0
| | |
OrderItem1
|
10
|
False
| | |
OrderItem2
|
20
|
True
| | |
OrderItem2
|
30
|
True
| | |
OrderItem2
|
40
|
False
|
The result: 30
Returns the minimum value of a collection.
Gets the min value from a collection.
Expression:
CriteriaOperator.FromLambda<Order, int>(o => o.OrderItems.Min(oi => oi.ItemPrice));
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.ItemPrice)), Aggregate.Min, null);
CriteriaOperator.Parse("OrderItems.Min(ItemPrice)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | |
Order0
| | |
OrderItem1
|
10
| | |
OrderItem2
|
20
|
The result: 10
Get the min value from a filtered collection.
Expression:
CriteriaOperator.FromLambda<Order, int>(o => o.OrderItems.Where(oi => oi.IsAvailable == true).Min(oi => oi.ItemPrice)); ;
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.ItemPrice)), Aggregate.Min, new BinaryOperator(nameof(OrderItem.IsAvailable), true));
CriteriaOperator.Parse("[OrderItems][IsAvailable=True].Min(ItemPrice)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
|
IsAvailable
| | --- | --- | --- | --- | |
Order0
| | |
OrderItem1
|
10
|
False
| | |
OrderItem2
|
20
|
True
| | |
OrderItem2
|
30
|
True
| | |
OrderItem2
|
40
|
False
|
The result: 20
Returns the sum of values from a collection.
Get the sum of values from a whole collection
Expression:
CriteriaOperator.FromLambda<Order, int>(o => o.OrderItems.Sum(oi => oi.ItemPrice));
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.ItemPrice)), Aggregate.Sum, null);
CriteriaOperator.Parse("OrderItems.Sum(ItemPrice)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | |
Order0
| | |
OrderItem1
|
10
| | |
OrderItem2
|
20
|
The result: 30
Get the sum of values from a filtered collection.
Expression:
CriteriaOperator.FromLambda<Order, int>(o => o.OrderItems.Where(oi => oi.IsAvailable == true).Sum(oi => oi.ItemPrice)); ;
new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), new OperandProperty(nameof(OrderItem.ItemPrice)), Aggregate.Sum, new BinaryOperator(nameof(OrderItem.IsAvailable), true));
CriteriaOperator.Parse("[OrderItems][IsAvailable=True].Sum(ItemPrice)");
Test
Input:
|
Order
|
OrderItemName
|
ItemPrice
|
IsAvailable
| | --- | --- | --- | --- | |
Order0
| | |
OrderItem1
|
10
|
False
| | |
OrderItem2
|
20
|
False
| | |
OrderItem2
|
30
|
True
| | |
OrderItem2
|
40
|
True
|
The result: 70
Determines whether items are within the range of values.
Get items from collection that have a property’s values between specified arguments.
Expression:
//Although there is no LINQ expression that generates the BetweenOperator you can solve this task using the following expression
CriteriaOperator.FromLambda<OrderItem>(oi => oi.ItemPrice >= 10 && oi.ItemPrice <= 30);
new BetweenOperator(nameof(OrderItem.ItemPrice), 10, 30);
CriteriaOperator.Parse("[ItemPrice] Between(10,30)");
Test
Input:
|
OrderItemName
|
ItemPrice
| | --- | --- | |
OrderItem1
|
10
| |
OrderItem2
|
20
| |
OrderItem3
|
30
| |
OrderItem4
|
40
|
The result:
|
OrderItemName
|
ItemPrice
| | --- | --- | |
OrderItem1
|
10
| |
OrderItem2
|
20
| |
OrderItem3
|
30
|
Performs a BinaryOperatorType operation between two operands.
Return orders that have price more or equal to 50.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.Price >= 50);
new BinaryOperator(nameof(Order.Price), 50, BinaryOperatorType.GreaterOrEqual);
CriteriaOperator.Parse("Price>=50");
Test
Input:
|
OrderName
|
Price
| | --- | --- | |
Order1
|
30
| |
Order2
|
40
| |
Order3
|
50
| |
Order4
|
60
|
The result:
|
OrderName
|
Price
| | --- | --- | |
Order1
|
50
| |
Order2
|
60
|
Checks if a collection contains at least one object matching a specific criteria.
Selects Order objects that contain at least one object in their OrderItems collection with the OrderPrice property set to 44.
Expression:
// There is no LINQ expression that generates the ContainsOperator you can solve this task using the following expression
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Any(oi => oi.ItemPrice == 44));
new ContainsOperator(nameof(Order.OrderItems), new BinaryOperator("ItemPrice", 44));
CriteriaOperator.Parse("[OrderItems][ItemPrice = ?]", 44);
Test
Input:
|
Order
|
OrderItem
|
ItemPrice
| | --- | --- | --- | |
Order0
| | |
Item0-0
|
10
| | |
Item0-1
|
20
| |
Order1
| | |
Item1-0
|
30
| | |
Item1-1
|
44
|
The result:
|
Order
| | --- | |
Order1
|
Indicates whether a specified operand is a null reference or empty string.
Select an Order with empty descriptions.
Expression:
CriteriaOperator.FromLambda<Order>(o => string.IsNullOrEmpty(o.Description));
new FunctionOperator(FunctionOperatorType.IsNullOrEmpty, new CriteriaOperator[] { new OperandProperty(nameof(Order.Description)) });
CriteriaOperator.Parse("IsNullOrEmpty(Description)");
Test
Input:
|
Order
|
Description
| | --- | --- | |
Order0
|
“The main order”
| |
Order1
|
The result:
|
Order
| | --- | |
Order1
|
Compares the first operand with the NULL value. If a single operand is passed, the function returns true if the operand is null; otherwise, false is returned.
If two operands are passed and the first operand is null, the function returns the second operand. Otherwise, the first operand is returned.
Select OrderItems with an empty Order property
Expression:
//Although there is no LINQ expression that generates the IsNull operator you can solve this taks using the following expression
CriteriaOperator.FromLambda<OrderItem>(oi => oi.Order == null);
new FunctionOperator(FunctionOperatorType.IsNull, new OperandProperty(nameof(OrderItem.Order)));
CriteriaOperator.Parse("IsNull(Order)");
Test
Input:
|
OrderItemName
|
Order
| | --- | --- | |
OrderItem0
|
Order0
| |
OrderItem1
|
The result:
|
OrderItemName
| | --- | |
OrderItem1
|
Selects Orders where Description is either null or has the ‘Wrong value’ value.
Expression:
//Although there is no LINQ expression that generates the IsNull operator you can solve this taks using the following expression
CriteriaOperator.FromLambda<Order>(oi => "WrongValue" == (oi.Description ?? "WrongValue"));
var isNullCriterion = new FunctionOperator(FunctionOperatorType.IsNull, new OperandProperty(nameof(Order.Description)), new ConstantValue("WrongValue"));
var criterion = new BinaryOperator(isNullCriterion, "WrongValue", BinaryOperatorType.Equal);
CriteriaOperator.Parse("'WrongValue'=IsNull(Description,'WrongValue')");
Test
Input:
|
OrderName
|
Description
| | --- | --- | |
Order0
|
“Test description”
| |
Order1
|
“”
| |
Order2
|
“Wrong value”
|
The result:
|
OrderName
| | --- | |
Order1
| |
Order2
|
Returns one of several specified values that depend on the values of logical expressions.
Selects orders where ‘OrderColor’ is either ‘Blue’ for ‘Type1’ orders or ‘Orange’ for ‘Type2 orders.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderColor == (o.OrderType == "Type1" ? "Blue" : o.OrderType == "Type2" ? "Orange" : "SomeNullValue"));
var iifOperator = new FunctionOperator(FunctionOperatorType.Iif, new BinaryOperator(nameof(Order.OrderType), "Type1"), new ConstantValue("Blue"), new BinaryOperator(nameof(Order.OrderType), "Type2"), new ConstantValue("Orange"), new ConstantValue("SomeNullValue"));
// ...
var finalCriterion = new BinaryOperator(nameof(Order.OrderColor), iifOperator);
CriteriaOperator.Parse("OrderColor=Iif(OrderType='Type1','Blue',OrderType='Type2','Orange','SomeNullValue')");
Test
Input:
|
Order
|
OrderType
|
OrderColor
| | --- | --- | --- | |
Order1
|
“Type1”
|
“Red”
| |
Order2
|
“Type1”
|
“Blue”
| |
Order3
|
“Type2”
|
“Green”
| |
Order4
|
“Type2”
|
“Orange”
|
The result:
|
Order
| | --- | |
Order2
| |
Order4
|
The following function operators are available for strings:
String function operators
You can read more about these operators here: FunctionOperatorType.
Selects Orders with names that end with 1.
Expression:
CriteriaOperator.FromLambda<Order>(oi => oi.OrderName.EndsWith("1"));
new FunctionOperator(FunctionOperatorType.EndsWith, new OperandProperty(nameof(Order.OrderName)), new ConstantValue("1"));
CriteriaOperator.Parse("EndsWith(OrderName,'1')");
Test
Input:
|
OrderName
| | --- | |
Order0
| |
Order1
|
The result:
|
OrderName
| | --- | |
Order1
|
Selects orders where the string represenation of the Price property is equal to 20.
Expression:
CriteriaOperator.FromLambda<Order>(oi => oi.Price.ToString() == "20");
var strCriterion = new FunctionOperator(FunctionOperatorType.ToStr, new OperandProperty(nameof(Order.Price)));
var criterion = new BinaryOperator(strCriterion, new ConstantValue("20"), BinaryOperatorType.Equal);
CriteriaOperator.Parse("ToStr(Price)='20'");
Test
Input:
|
OrderName
|
Price
| | --- | --- | |
Order1
|
10
| |
Order2
|
20
|
The result:
|
OrderName
| | --- | |
Order2
|
Selects Orders whose OrderName is equal to the Test and Value string concatenation.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderName == "Test" + "Value");
var concatOperator = new FunctionOperator(FunctionOperatorType.Concat, "Test", "Value");
CriteriaOperator criterion =
new BinaryOperator(nameof(Order.OrderName), concatOperator);
CriteriaOperator.Parse("OrderName=Concat('Test','Value')");
Test
Input:
|
OrderName
| | --- | |
TestValue
| |
SomeValue
|
The result:
|
OrderName
| | --- | |
TestValue
|
DevExpress Data library contains various Function operators to work with numeric values:
Numeric function operators
You can read more about these operators here: FunctionOperatorType
Selects Orders where the absolute value of the Price property is greater than 10
Expression:
CriteriaOperator.FromLambda<Order>(o => Math.Abs(o.Price)>10);
var functOperator = new FunctionOperator(FunctionOperatorType.Abs, new OperandProperty(nameof(Order.Price)));
var criterion = new BinaryOperator(functOperator, new ConstantValue(10), BinaryOperatorType.Greater);
CriteriaOperator.Parse("Abs(Price)>10");
Test
Input:
|
OrderName
|
Price
| | --- | --- | |
Order0
|
4
| |
Order1
|
11
| |
Order2
|
-15
|
The result:
|
OrderName
| | --- | |
Order1
| |
Order2
|
You can use the following function operators to work with dates:
Date function operators
You can read more about these operators here: FunctionOperatorType.
Selects Orders with OrderDate’s months is greater than 2.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderDate.Month > 2);
var funcOperator = new FunctionOperator(FunctionOperatorType.GetMonth, new OperandProperty(nameof(Order.OrderDate)));
var criterion = new BinaryOperator(funcOperator, new ConstantValue(2), BinaryOperatorType.Greater);
CriteriaOperator.Parse("GetMonth(OrderDate)>2");
Test
Input:
|
OrderName
|
OrderDate
| | --- | --- | |
Order0
|
15 jan 2022
| |
Order1
|
13 mar 2022
| |
Order2
|
18 may 2022
|
The result:
|
OrderName
| | --- | |
Order1
| |
Order2
|
Selects Orders with OrderDate is equal to 10-mar-2022.
Expression:
var requiredDate = new DateTime(2022, 3, 10);
// ...
CriteriaOperator.FromLambda<Order>(o => o.OrderDate == requiredDate);
new BinaryOperator(nameof(Order.OrderDate), new DateTime(2022, 3, 10));
CriteriaOperator.Parse("OrderDate=?", new DateTime(2022, 3, 10));
Test
Input:
|
OrderName
|
OrderDate
| | --- | --- | |
Order0
|
15 jan 2022
| |
Order1
|
13 mar 2022
| |
Order2
|
18 may 2022
|
The result:
|
OrderName
| | --- | |
Order1
| |
Order2
|
Selects Order instances whose OrderDate is equal to 10-mar-2022 plus 1000 milliseconds.
Expression:
var targetDate = new DateTime(2022, 3, 10).AddMilliseconds(1000);
// ...
CriteriaOperator.FromLambda<Order>(o => o.OrderDate.AddMilliseconds(1000) == targetDate);
var functionOp = new FunctionOperator(FunctionOperatorType.AddMilliSeconds, new OperandProperty(nameof(Order.OrderDate)), new ConstantValue(1000));
var criterion = new BinaryOperator(functionOp, new DateTime(2022, 3, 10).AddMilliseconds(1000), BinaryOperatorType.Equal);
CriteriaOperator.Parse("AddMilliseconds(OrderDate,1000)=?", new DateTime(2022, 3, 10).AddMilliseconds(1000));
Groups two or more operands with a logical AND or OR.
Return orders that have Price equal to 20 or OrderName equal to ‘Order3’.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.Price==20 || o.OrderName == "Order3");
var operator1 = new BinaryOperator(nameof(Order.Price), 20);
var operator2 = new BinaryOperator(nameof(Order.OrderName), "Order3");
CriteriaOperator criterion = GroupOperator.Or(operator1, operator2);
CriteriaOperator.Parse("Price=20 or OrderName='Order3'");
Test
Input:
|
OrderName
|
Price
| | --- | --- | |
Order0
|
10
| |
Order1
|
20
| |
Order2
|
30
| |
Order3
|
40
|
The result:
|
OrderName
| | --- | |
Order1
| |
Order3
|
Determines if a value matches the value on the specified list.
Get order with the OrderName property ore one of the follows values: ‘Order2’, ‘Order3’,’Description5’.
Expression:
CriteriaOperator.FromLambda<Order, bool>(o => new string[] { "Order2", "Order3", "Description5" }.Contains(o.OrderName));
new InOperator("OrderName", new string[] { "Order2", "Order3", "Description5" });
CriteriaOperator.Parse("OrderName In ('Order2', 'Order3','Description5')");
Test
Input:
|
OrderName
| | --- | |
Order0
| |
Order1
| |
Order2
| |
Order3
|
The result:
|
OrderName
| | --- | |
Order2
| |
Order3
|
Performs unary operations (like NOT or NULL).
Return orders that have Price is not between 20 and 30.
Expression:
CriteriaOperator.FromLambda<Order>(o => !(o.Price >= 20 && o.Price <= 30));
new UnaryOperator(UnaryOperatorType.Not, new BetweenOperator(nameof(Order.Price), 20, 30));
CriteriaOperator.Parse("Not Price Between (20,30)");
Test
Input:
|
OrderName
|
Price
| | --- | --- | |
Order0
|
10
| |
Order1
|
20
| |
Order2
|
30
| |
Order3
|
40
|
The result:
|
OrderName
| | --- | |
Order0
| |
Order3
|
Return order items with the Order property references to null.
Expression:
CriteriaOperator.FromLambda<OrderItem>(oi => oi.Order == null);
new UnaryOperator(UnaryOperatorType.IsNull, new OperandProperty(nameof(OrderItem.Order)));
CriteriaOperator.Parse("Order is null");
Test
Input:
|
OrderItemName
|
Order
| | --- | --- | |
OrderItem0
|
null
| |
OrderItem1
|
Order1
|
The result:
|
OrderItemName
| | --- | |
OrderItem0
|
Complex scenarios for different operators:
Selects Orders with Price equal to 99 and has OrderItems with ItemPrice equal to 10:
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Any(oi => oi.ItemPrice == 10) && o.Price == 99);
var containsCriterion = new ContainsOperator(nameof(Order.OrderItems), new BinaryOperator(nameof(OrderItem.ItemPrice), 10));
var simpleCriterion = new BinaryOperator(nameof(Order.Price), 99);
var criterion = new GroupOperator(containsCriterion, simpleCriterion);
CriteriaOperator.Parse("[OrderItems][ItemPrice = ?] and Price = ?", 10, 99);
Test
Input:
|
OrderName
|
Price
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | --- | |
Order0
|
99
| | | |
OrderItem00
|
2
| | | |
OrderItem01
|
4
| |
Order1
|
99
| | | |
OrderItem10
|
5
| | | |
OrderItem11
|
10
|
The result:
|
OrderName
| | --- | |
Order1
|
Selects Orders with OrderItems that has Company with CompanyName equal to ‘Company1’:
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Any(oi => oi.Company.CompanyName == "Company1"));
var criterionForCollection = new BinaryOperator("Company.CompanyName", "Company1");
var criterion = new ContainsOperator(nameof(Order.OrderItems), criterionForCollection);
CriteriaOperator.Parse("[OrderItems][Company.CompanyName = 'Company1']");
Test
Input:
|
OrderName
|
OrderItemName
|
CompanyName
| | --- | --- | --- | |
Order0
| | | |
OrderItem00
| | | | |
Company0
| | | |
Company2
| |
Order1
| | | |
OrderItem10
| | | | |
Company1
| | | |
Company3
|
The result:
|
OrderName
| | --- | |
Order1
|
Selects Orders with OrderDate in the two-week range from the specific date:
Expression:
var targetDate = new DateTime(2022, 8, 9);
// ...
CriteriaOperator.FromLambda<Order>(o => o.OrderDate >= targetDate.AddDays(-14) && o.OrderDate < targetDate);
var targetDate = new DateTime(2022, 8, 9);
var startDateCriterion = new FunctionOperator(FunctionOperatorType.AddDays, new OperandValue(targetDate), new OperandValue(-14));
var startOperator = new BinaryOperator(new OperandProperty(nameof(Order.OrderDate)), startDateCriterion, BinaryOperatorType.GreaterOrEqual);
var endOperator = new BinaryOperator(new OperandProperty(nameof(Order.OrderDate)), new OperandValue(targetDate), BinaryOperatorType.Less);
var criterion = new GroupOperator(GroupOperatorType.And, startOperator, endOperator);
CriteriaOperator.Parse("OrderDate >= AddDays(#2022-08-09#, -14) && OrderDate < #2022-08-09#");
Test
Input:
|
OrderName
|
OrderDate
|
OrderItemName
|
ItemPrice
| | --- | --- | --- | --- | |
Order0
|
2022-08-07
| |
Order1
|
2022-07-07
| |
Order2
|
2022-08-01
|
The result:
|
OrderName
| | --- | |
Order0
| |
Order2
|
Selects OrderItems with assigned Company and Order.OrderName equal to ‘Order1’:
Expression:
CriteriaOperator.FromLambda<OrderItem>(oi => oi.Order.OrderName == "Order1" && oi.Company != null);
var operator1 = new BinaryOperator("Order.OrderName", "Order1");
var operator2 = new FunctionOperator(FunctionOperatorType.IsNull, new OperandProperty(nameof(OrderItem.Company)));
var operator2_2 = new UnaryOperator(UnaryOperatorType.Not, operator2);
CriteriaOperator criterion = new GroupOperator(GroupOperatorType.And, operator1, operator2_2);
CriteriaOperator.Parse("Order.OrderName == 'Order1' AND !IsNull(Company)");
// ...
CriteriaOperator.Parse("Order.OrderName == 'Order1' AND Not IsNull(Company)");
// ...
CriteriaOperator.Parse("Order.OrderName == 'Order1' AND Company is not null");
Selects Orders with the maximum OrderDate:
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderDate == FromLambdaFunctions.FreeJoin<Order>(selectOrder => selectOrder.OrderColor == "red").Max(resultOrder => resultOrder.OrderDate));
var filerJoin = new BinaryOperator(nameof(Order.OrderColor), "red");
var joinOperand = new JoinOperand(nameof(Order), filerJoin, Aggregate.Max, new OperandProperty(nameof(Order.OrderDate)));
var criterion = new BinaryOperator(nameof(Order.OrderDate), joinOperand);
CriteriaOperator.Parse("OrderDate ==[<Order>][OrderColor=='red'].Max(OrderDate)");
Test
Input:
|
OrderName
|
OrderDate
|
OrderColor
| | --- | --- | --- | |
Order0
|
2022-08-07
|
red
| |
Order1
|
2022-08-08
|
yellow
| |
Order2
|
2022-08-09
|
green
| |
Order3
|
2022-08-10
|
red
| |
Order4
|
2022-08-10
|
yellow
|
The result:
|
OrderName
| | --- | |
Order3
| |
Order4
|
Selects Orders with sum of all child Positions’s PositionCount equal to 10:
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Sum(oi => oi.Positions.Sum(p => p.PositionCount)) == 10);
var positionsExpression = new AggregateOperand(new OperandProperty(nameof(OrderItem.Positions)), new OperandProperty(nameof(Position.PositionCount)), Aggregate.Sum, null);
var orderItemExpression = new AggregateOperand(new OperandProperty(nameof(Order.OrderItems)), positionsExpression, Aggregate.Sum, null);
var criterion = new BinaryOperator(orderItemExpression, new OperandValue(10), BinaryOperatorType.Equal);
CriteriaOperator.Parse("OrderItems.Sum(Positions.Sum(PositionCount))==10");
Test
Input:
|
OrderName
|
OrderItemName
|
PositionName
|
PositionCount
| | --- | --- | --- | --- | |
Order0
| | | | | |
OrderItem00
| | | | | |
Position000
|
2
| | | |
Position001
|
3
| | |
OrderItem01
| | | | | |
Position010
|
1
| | | |
Position011
|
4
| |
Order1
| | | | | |
OrderItem10
| | | | | |
Position100
|
10
| | | |
Position101
|
20
| | |
OrderItem11
| | | | | |
Position110
|
30
| | | |
Position111
|
40
|
The result:
|
OrderName
| | --- | |
Order0
|
Selects Orders that has OrderItems with OrderDate equal to parent Order’s RegistrationDate:
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Any(oi => oi.RegistrationDate == o.OrderDate));
var binaryOperator = new BinaryOperator(new OperandProperty("^.OrderDate"), new OperandProperty(nameof(OrderItem.RegistrationDate)),BinaryOperatorType.Equal);
var criterion = new AggregateOperand(nameof(Order.OrderItems), Aggregate.Exists, binaryOperator);
CriteriaOperator.Parse("[OrderItems][[^.OrderDate] == RegistrationDate]");
Test
Input:
|
OrderName
|
OrderDate
|
OrderItemName
|
RegistrationDate
| | --- | --- | --- | --- | |
Order0
|
2022-08-16
| | | | |
OrderItem00
|
2022-08-17
| | | |
OrderItem01
|
2022-08-18
| |
Order1
|
2022-08-19
| | | | | |
OrderItem10
|
2022-08-19
| | | |
OrderItem11
|
2022-08-20
|
The result:
|
OrderName
| | --- | |
Order1
|
Selects Orders with Status equal to the ‘Delayed’ enum value:
Expression:
CriteriaOperator.FromLambda<Order>(x => x.Status == OrderStatusEnum.Delayed);
new BinaryOperator(nameof(Order.Status), OrderStatusEnum.Delayed);
CriteriaOperator.Parse("Status=##Enum#dxTestSolutionXPO.Module.BusinessObjects.OrderStatusEnum,Delayed#");
Test
Input:
|
OrderName
|
Status
| | --- | --- | |
Order0
|
Active
| |
Order1
|
Delayed
|
The result:
|
OrderName
| | --- | |
Order1
|
Note
Register enums using the EnumProcessingHelper.RegisterEnum method.
Selects OrderItems with Order equal to the specific Order object:
Expression:
var obj = uow.GetObjectByKey<Order>(2);
var criterion = CriteriaOperator.FromLambda<OrderItem>(x => x.Order == obj);
var obj = uow.GetObjectByKey<Order>(2);
var criterion = new BinaryOperator(nameof(OrderItem.Order), obj);
using(uow.CreateParseCriteriaSessionScope()) {
var criterion = CriteriaOperator.Parse("Order=##XpoObject#dxTestSolutionXPO.Module.BusinessObjects.Order(2)#");
var resultCollection = new XPCollection<OrderItem>(uow, criterion).OrderBy(x => x.OrderItemName).ToList();
// ...
}
Generally, you do not need to use square brackets when you build string criteria operators.
The following criteria are equal:
CriteriaOperator.Parse("Contains(OrderName,'der1')");
CriteriaOperator.Parse("Contains([OrderName],'der1')");
You need to use square brackets if your property names match criteria language keywords:
The correct criterion:
CriteriaOperator.Parse("Contains([Contains],'ike0')");
Alternatively, you can mark a keyword-like field name with an escape character (@ sign):
CriteriaOperator.Parse("Contains(@Contains, 'ike0')");
The wrong criterion that raises an exception:
CriteriaOperator.Parse("Contains(Contains,'ike0')");
Note that the recommended way to build a criterion is to use LINQ-Like Criteria Syntax:
CriteriaOperator.FromLambda<Order>(o => o.Contains.Contains("ike0"));
The Current Object Parameter allows you to access properties of the current object. The following snippet shows how to filter a lookup by a linked MyTaskDepartment object. The lookup shows contacts where the ContactDepartment property value is the same as the current MyTaskDepartment property of MyTask:
public class MyTask : BaseObject {
// ...
[DataSourceCriteria("ContactDepartment = '@This.MyTaskDepartment.Oid'")]
public Contact NewContact3 {
get => newContact3;
set => SetPropertyValue(nameof(NewContact3), ref newContact3, value);
}
// ...
public Department MyTaskDepartment {
get => myTaskDepartment;
set => SetPropertyValue(nameof(MyTaskDepartment), ref myTaskDepartment, value);
}
// ...
}
public class Contact : BaseObject {
// ...
public Department ContactDepartment {
get => contactDepartment;
set => SetPropertyValue(nameof(ContactDepartment), ref contactDepartment, value);
}
// ...
}
The CurrentTenantId() operator returns the identifier of the current tenant in a multi-tenant application.
For instance, if the Contact class includes the Owner property of the Tenant type, you can write the following filter expression to get the list of objects associated with the current tenant: "Owner.Oid==CurrentTenantId()".
The CurrentUserId() operator returns the identifier of the current user. The following snippet shows how to use this operator to filter a reference property’s lookup:
Expression:
public class MyTask : BaseObject {
// ...
Contact _assignedTo;
[Association("Contact-Tasks")]
[DataSourceCriteria("Owner.Oid==CurrentUserId()")]
public Contact AssignedTo {
get { return _assignedTo; }
set { SetPropertyValue(nameof(AssignedTo), ref _assignedTo, value); }
}
// ...
}
public class Contact : BaseObject {
// ...
public ApplicationUser Owner {
get => owner;
set => SetPropertyValue(nameof(Owner), ref owner, value);
}
// ...
}
The IsCurrentUserId(oid) operator returns True if the current user has the specified identifier. The following snippet shows how to use this operator to filter a reference property’s lookup:
Expression:
public class MyTask : BaseObject {
// ...
[DataSourceCriteria("IsCurrentUserId(Owner.Oid)")]
public Contact NewContact1 {
get => newContact1;
set => SetPropertyValue(nameof(NewContact1), ref newContact1, value);
}
// ...
}
public class Contact : BaseObject {
// ...
public ApplicationUser Owner {
get => owner;
set => SetPropertyValue(nameof(Owner), ref owner, value);
}
// ...
}
The IsCurrentUserInRole(‘rolename’) operator determines whether the currently logged on user is assigned to a role with the specific name. The following snippet shows how to filter a reference property’s lookup;
Expression:
public class MyTask : BaseObject {
// ...
[DataSourceCriteria("IsCurrentUserInRole('Default')")]
public Contact NewContact2 {
get => newContact2;
set => SetPropertyValue(nameof(NewContact2), ref newContact2, value);
}
// ...
}
public class Contact : BaseObject {
// ...
public ApplicationUser Owner {
get => owner;
set => SetPropertyValue(nameof(Owner), ref owner, value);
}
// ...
}
The IsNewObject(obj) operator indicates whether the specified object was created but not saved to the database. The following snippet shows how to show an action for new objects only. @This is the Current Object Parameter.
Expression:
myAction1.TargetObjectsCriteria = "IsNewObject(@This)";
Free Joins (Query Non-Associated Data & Calculate Aggregations) allow you to join persistent objects conditionally, use object properties to calculate aggregate functions against matching objects, and return aggregate values as ajoin result.
Example 1
Get orders are referenced in the FreeOrderItem classes to ensure that the sum of all related FreeOrderItem objects is more than 2.
Expression:
CriteriaOperator.FromLambda<Order>(o => FromLambdaFunctions.FreeJoin<FreeOrderItem>(oi => oi.Order.Oid == o.Oid).Count() > 2);
var criteriaCondition = new BinaryOperator(new OperandProperty("^.Oid"), new OperandProperty("Order.Oid"), BinaryOperatorType.Equal);
var criteriaLeft = new JoinOperand(nameof(FreeOrderItem), criteriaCondition, Aggregate.Count, null);
var criterion = new BinaryOperator(criteriaLeft, 2, BinaryOperatorType.Greater);
var resCollection = new XPCollection<Order>(uow, criterion);
CriteriaOperator.Parse("[<FreeOrderItem>][^.Oid = Order.Oid].Count() > 2");
Test
Input:
|
FreeOrderItem
|
Order
| | --- | --- | |
FreeItem0
|
Order0
| |
FreeItem1
|
Order0
| |
FreeItem2
|
Order1
| |
FreeItem3
|
Order1
| |
FreeItem4
|
Order1
|
The result: OrderItem1
If two classes have an association between them, FreeJoins loads all data from a database to join these classes. We recommend that you use association properties instead. You can rewrite the example above as follows (the Order and OrderItem classes have an association between them):
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderItems.Count() > 2);
var criteriaLeft = new AggregateOperand(nameof(Order.OrderItems), Aggregate.Count);
var criterion = new BinaryOperator(criteriaLeft, 2, BinaryOperatorType.Greater);
CriteriaOperator.Parse("OrderItems.Count()>2");
Example 2
Get orders that are referenced in the FreeOrderItem classes to ensure that an order’s OrderDate is equal to the max FreeOrderDate from the order’s related FreeOrderItems.
Expression:
CriteriaOperator.FromLambda<Order>(o => o.OrderDate == FromLambdaFunctions.FreeJoin<FreeOrderItem>(oi => oi.FreeOrderOwnerName == o.OrderOwnerName).Max(oi => oi.FreeOrderDate));
var criteriaCondition = new BinaryOperator(new OperandProperty("^.OrderOwnerName"), new OperandProperty(nameof(FreeOrderItem.FreeOrderOwnerName)), BinaryOperatorType.Equal);
var criteriaRight = new JoinOperand(nameof(FreeOrderItem), criteriaCondition, Aggregate.Max, new OperandProperty(nameof(FreeOrderItem.FreeOrderDate)));
CriteriaOperator criterion = new BinaryOperator(new OperandProperty(nameof(Order.OrderDate)), criteriaRight, BinaryOperatorType.Equal);
CriteriaOperator.Parse("OrderDate = [<FreeOrderItem>][^.OrderOwnerName = FreeOrderOwnerName].Max(FreeOrderDate)");
Test
Input:
|
Order
|
OrderDate
|
FreeOrderItem
|
FreeOrderDate
| | --- | --- | --- | --- | |
Order1
|
3-Nov-22
| | | |
FreeItem0
|
2-Nov-22
| | | |
FreeItem1
|
1-Nov-22
| |
Order2
|
25-Nov-22
| | | |
FreeItem2
|
14-Nov-22
| | | |
FreeItem3
|
25-Nov-22
|
The result: Order2
Example 3
For each owner, get the latest (youngest) FreeOrderItems.
Expression:
CriteriaOperator.FromLambda<Order>(parentO => parentO.OrderDate == FromLambdaFunctions.FreeJoin<Order>(childO => childO.OrderOwnerName == parentO.OrderOwnerName).Max(o => o.OrderDate));
var criteriaCondition = new BinaryOperator(new OperandProperty("^.OrderOwnerName"), new OperandProperty(nameof(Order.OrderOwnerName)), BinaryOperatorType.Equal);
var criteriaRight = new JoinOperand(nameof(Order), criteriaCondition, Aggregate.Max, new OperandProperty(nameof(Order.OrderDate)));
CriteriaOperator criterion = new BinaryOperator(new OperandProperty(nameof(Order.OrderDate)), criteriaRight, BinaryOperatorType.Equal);
CriteriaOperator.Parse("OrderDate = [<Order>][^.OrderOwnerName = OrderOwnerName].Max(OrderDate)");
Test
Input:
|
Order
|
OrderDate
|
Owner
| | --- | --- | --- | |
Order0
|
3-Nov-22
|
Owner1
| |
Order1
|
4-Nov-22
|
Owner1
| |
Order2
|
6-Nov-22
|
Owner2
| |
Order3
|
5-Nov-22
|
Owner2
|
The result: Order1, Order2
XAF Applications
Free Joins-based criteria are also supported for XAF UI and Web API Service applications powered by EF Core for data access. This feature is not supported in non-XAF applications.
Upcasting (Combine Data from Base and Derived Classes) allows you to use properties from derived classes.
Selects orders that are ExtendedOrder class instances (descendants of the Order class) and has ExtendedDescription equal to "description1":
Expression:
CriteriaOperator.FromLambda<Order>(o => ((ExtendedOrder)o).ExtendedDescription == "description1");
new BinaryOperator(new OperandProperty("<ExtendedOrder>ExtendedDescription"), new OperandValue("description1"), BinaryOperatorType.Equal);
CriteriaOperator.Parse("<ExtendedOrder>ExtendedDescription='description1'");
To filter objects by type, use one of the following approaches:
The ObjectType service field. Refer to the following topic for more information: When and Why XPO Extends the Database Schema.
IsExactType function operator:
IsInstanceOfType function operator:
See Also