How to: Build Complex Criteria
- 6 minutes to read
This topic demonstrates advanced options for creating criteria when retrieving persistent objects using collections such as the XPCollection or filtering their contents. Please refer to the How to Build Simple Criteria topic for general information on building criteria in XPO.
Using Logical Groups
Simple criteria can be combined into logical groups to form complex criteria. For example, to retrieve all the contacts that do not live in Chicago, you can use the simple criteria as is shown in the following code example.
// Retrieves a collection of the Contact objects which match the criteria that represents
// the logical expression (DefaultAddress.City <> "Chicago").
XPCollection collection = new XPCollection(typeof(Contact),
CriteriaOperator.Parse("DefaultAddress.City != 'Chicago'")
);
The code below represents an extended version of the previous code example and demonstrates how to retrieve all the contacts that do not live in Chicago, but do work for a company. Note that these two criteria are combined using the AND operator.
// Retrieves a collection of the Contact objects which match the criteria that represents
// the logical expression (DefaultAddress.City <> "Chicago" AND not (Company is null)).
XPCollection collection = new XPCollection(typeof(Contact),
CriteriaOperator.Parse("DefaultAddress.City != 'Chicago' AND Company is not null")
);
Filtering By Querying Collection Properties
The ContainsOperator allows you to build filter criteria based on the results of querying collection properties. The following code selects a set of Contact objects that contain an Address in their Locations collection property and the City property of the Address objects found should be equal to the value of the Owner.DefaultAddress.City property, i.e. the value of the person’s DefaultAddress.City property.
Note
To reference a parent object in an OperandProperty (the Owner property in the code example below), use the “^” symbol.
// Retrieves the Contact objects that contain an address,
// and the city which is given in the address is the same as the person's city.
// The "^" symbol represents a reference to the Owner object,
// the parent object of the Locations collection.
XPCollection collection = new XPCollection(typeof(Contact),
new ContainsOperator("DefaultAddress.Owner.Locations",
new BinaryOperator(
new OperandProperty("City"), new OperandProperty("^.DefaultAddress.City"),
BinaryOperatorType.Equal
)
)
);
Using Aggregate Data
With AggregateOperand, you can calculate aggregate operations using collection properties and create criteria based on the aggregate values. Let us consider a simple example.
// Represents the Person class which contains account information.
public class Person: XPObject {
[Association("PersonAccounts")]
public XPCollection<Account> Accounts {
get { return GetCollection<Account>(nameof(Accounts)); }
}
}
// Represents the Account class which contains the amount in the account.
public class Account: XPObject {
[Association("PersonAccounts")]
public Person Owner {
get { return fOwner; }
set { SetPropertyValue(nameof(Owner), ref fOwner, value); }
}
Person fOwner;
public int Amount {
get { return fAmount; }
set { SetPropertyValue(nameof(Amount), ref fAmount, value); }
}
int fAmount;
}
To retrieve all persons who have less than 100 dollars in their cumulative accounts, you can use the following code.
XPCollection collection = new XPCollection(typeof(Person),
CriteriaOperator.Parse("Accounts.Sum(Amount) < 100")
);
Note
For a list of aggregate functions, see Aggregate.
An aggregate operation applies either to all objects within the collection or to the objects that match the explicitly specified criteria. For example, the following code selects all persons that have only one account with a zero amount.
XPCollection collection = new XPCollection(typeof(Person),
CriteriaOperator.Parse("Accounts[Amount = 0].Count == 1")
);
Narrowing Results in Complex Inheritance Hierarchies
In a complex inheritance hierarchy, querying a data store for the objects whose type is used as the type of a base class for their descendants (such as the Person class for the Company class in the example above) results in objects of the base type and their descendants being retrieved. In order to avoid an ambiguous definition of the type of objects retrieved by the collection, you can explicitly specify the object type you want to appear in your collection as is shown in the following code example.
// Retrieves only objects of the Person class except for its descendants.
XPCollection persons = new XPCollection(session, typeof(Person),
new BinaryOperator(
XPObjectType.ObjectTypePropertyName,
session.GetObjectType(session.GetClassInfo(typeof(Person)))
)
);
Querying from the Backend without Creating Objects
XPView can query information from the database and make it available in a number of “fields”, which have to be defined for the view beforehand.
The code below creates an XPView with a calculated field:
XPView view = new XPView(session, typeof(OrderEntry));
view.AddProperty("RealPrice",
"iif(RebatePercent == 0, ArticlePrice, ArticlePrice - (ArticlePrice * RebatePercent / 100))");
Since the XPView’s result set doesn’t contain objects of the given type, the view’s field from the code above can be accessed like this: