Build Criteria - Cheat Sheet | Cross-Platform Class Library

Sep 04, 2024
20 minutes to read

Tip

The Criteria Operators Cheat Sheet Github repo contains all the code samples used on this page.

AggregateOperand.Avg

Evaluates the average of the values in the collection.

Example 1

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

Example 2

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

AggregateOperand.Count

Returns the number of objects in a collection.

Example 1

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

Example 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

Example 3

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

AggregateOperand.Single

Returns a single object from the AggregateOperand.CollectionProperty that matches the specified AggregateOperand.Condition. The collection must be empty or contain exactly one object.

Example 1

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

Example 2

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

Example 3

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

AggregateOperand.Exists

Determines whether the number of elements in a collection is greater than zero.

Example

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

AggregateOperand.Exists(CriteriaOperator)

Determines whether the number of elements in a collection that match the criteria is greater than zero.

Example

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

AggregateOperand.Max

Returns the maximum value of a collection.

Example 1

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

Example 2

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

AggregateOperand.Min

Returns the minimum value of a collection.

Example 1

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

Example 2

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

AggregateOperand.Sum

Returns the sum of values from a collection.

Example 1

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

Example 2

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

BetweenOperator

Determines whether items are within the range of values.

Example

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

BinaryOperator

Performs a BinaryOperatorType operation between two operands.

Example

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

ContainsOperator

Checks if a collection contains at least one object matching a specific criteria.

Example

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

FunctionOperator.IsNullOrEmpty

Indicates whether a specified operand is a null reference or empty string.

Example

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

FunctionOperator.IsNull

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, the function returns the second operand if the first operand is null; otherwise, the first operand is returned.

Example 1

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

Example 2

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

FunctionOperator.Iif

Returns one of several specified values that depend on the values of logical expressions.

Example

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

FunctionOperator String operators

The following function operators are available for strings:

String function operators

Trim
Len
Substring
Upper
Lower
Concat
Ascii
Char
ToStr
Replace
Reverse
Insert
CharIndex
Remove
StartsWith
EndsWith
Contains
PadLeft
PadRight

You can read more about these operators here: FunctionOperatorType.

Example 1

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

Example 2

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

Example 3

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

FunctionOperator Numeric operators

There are plenty of Function operators to work with numeric values. Here is the full list:

Numeric function operators

Abs
Sqr
Cos
Sin
Atn
Exp
Log
Rnd
Tan
Power
Sign
Round
Ceiling
Floor
Max
Min
Acos
Asin
Atn2
BigMul
Cosh
Log10
Sinh
Tanh
ToInt
ToLong
ToFloat
ToDouble
ToDecimal

You can read more about these operators here: FunctionOperatorType

Example

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

FunctionOperator DateTime operators

You can use the following function operators to work with dates:

Date function operators

LocalDateTimeThisYear
LocalDateTimeThisMonth
LocalDateTimeLastWeek
LocalDateTimeThisWeek
LocalDateTimeYesterday
LocalDateTimeToday
LocalDateTimeNow
LocalDateTimeTomorrow
LocalDateTimeDayAfterTomorrow
LocalDateTimeNextWeek
LocalDateTimeTwoWeeksAway
LocalDateTimeNextMonth
LocalDateTimeNextYear
LocalDateTimeTwoMonthsAway
LocalDateTimeTwoYearsAway
LocalDateTimeLastMonth
LocalDateTimeLastYear
LocalDateTimeYearBeforeToday
IsOutlookIntervalBeyondThisYear
IsOutlookIntervalLaterThisYear
IsOutlookIntervalLaterThisMonth
IsOutlookIntervalNextWeek
IsOutlookIntervalLaterThisWeek
IsOutlookIntervalTomorrow
IsOutlookIntervalToday
IsOutlookIntervalYesterday
IsOutlookIntervalEarlierThisWeek
IsOutlookIntervalLastWeek
IsOutlookIntervalEarlierThisMonth
IsOutlookIntervalEarlierThisYear
IsOutlookIntervalPriorThisYear
IsThisWeek
IsThisMonth
IsThisYear
IsNextMonth
IsNextYear
IsLastMonth
IsLastYear
IsYearToDate
IsSameDay
InRange
InDateRange
IsJanuary
IsFebruary
IsMarch
IsApril
IsMay
IsJune
IsJuly
IsAugust
IsSeptember
IsOctober
IsNovember
IsDecember
DateDiffTick
DateDiffSecond
DateDiffMilliSecond
DateDiffMinute
DateDiffHour
DateDiffDay
DateDiffMonth
DateDiffYear
GetDate
GetMilliSecond
GetSecond
GetMinute
GetHour
GetDay
GetMonth
GetYear
GetDayOfWeek
GetDayOfYear
GetTimeOfDay
Now
UtcNow
Today
AddTimeSpan
AddTicks
AddMilliSeconds
AddSeconds
AddMinutes
AddHours
AddDays
AddMonths

You can read more about these operators here: FunctionOperatorType.

Example 1

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

Example 2

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

Example 3

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

GroupOperator

Groups two or more operands with a logical AND or OR.

Example

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

InOperator

Determines if a value matches a value in the specified list.

Example

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

UnaryOperator

Performs unary operations (like NOT or NULL).

Example 1

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

Example 2

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

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

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();
    // ...
}

Escaping and square brackets

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"));

XAF-specific Criteria Features

Current Object Parameter

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);
    }
    // ...
}

CurrentUserId

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);
    }
    // ...
}

IsCurrentUserId

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);
    }
    // ...
}

IsCurrentUserInRole

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);
    }
    // ...
}

IsNewObject

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)";

EF Core and XPO ORM-specific Criteria Features

Free Joins

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

Upcasting (Combine Data from Base and Derived Classes) allows you to use properties from derived classes.

Example

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'");

Filter by Type

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:

Expression:

//Although there is no LINQ expression that generates the IsExactType criterion you can solve this task using the following expression
CriteriaOperator.FromLambda<Order>(o => o is ExtendedOrder);

new FunctionOperator(FunctionOperatorType.Custom, new ConstantValue("IsExactType"), new OperandProperty("This"), typeof(ExtendedOrder).FullName);

CriteriaOperator.Parse("IsExactType(This,?)", typeof(ExtendedOrder).FullName);

IsInstanceOfType function operator:

Expression:

CriteriaOperator.FromLambda<Order>(o => o is ExtendedOrder);

new FunctionOperator(FunctionOperatorType.Custom, new ConstantValue("IsInstanceOfType"), new OperandProperty("This"), typeof(ExtendedOrder).FullName);

CriteriaOperator.Parse("IsInstanceOfType(This,?)", typeof(ExtendedOrder).FullName);