-
Notifications
You must be signed in to change notification settings - Fork 4
Query Helpers
Classes for helping to create QueryExpressions that allows for some defaults to be set, like return the active entities only. Returns a TypedQueryExpression, making querying with QueryExpressions simpler/cleaner, since you can't create a QueryExpression for entity type "A", but then cast it in the RetireveMultiple to entity "B"
Most extensions on the IOrganizationService and Queries allow for specifying ColumnNameAndValuePairs. This takes into assumption that 90% of the time QueryExpressions are equals comparisons, and allows the simplification of queries:
// SELECT * FROM Contact WHERE firstName = 'Bill' AND lastName = 'Smith' AND emailAddress1 IS NULL
service.GetFirst<Contact>(
"firstname", "Bill",
"lastname", "Smith",
"emailaddress1", null);
// vs
service.GetFirst<Contact>(
new ConditionExpression("firstname", ConditionOperator.Equals, "Bill"),
new ConditionExpression("lastname", ConditionOperator.Equals, "Smith"),
new ConditionExpression("emailaddress1", ConditionOperator.IsNull));
Here are all the items that the ColumnNameAndValuePairsSupport:
This allows mixing items for the 90% common use case, as well as the last 10%
// WHERE firstName = 'Bill' AND lastName IS NOT NULL
queryExpression.Where(
"firstname", "Bill",
new ConditionExpression("lastname", ConditionOperator.IsNotNull)
);
// Equivalent SDK Query
queryExpression.Criteria.AddCondition("firstname", ConditionOperator.Equal, "Bill");
queryExpression.Criteria.AddCondition("lastname", ConditionOperator.NotNull);
// WHERE firstName = 'Bill' OR firstName = 'William'
queryExpression.Where(
"firstname", "Bill",
LogicalOperator.Or,
"firstname", "William");
// Equivalent SDK Query
var orFilter = queryExpression.Criteria.AddFilter(LogicalOperator.Or);
orFilter.AddCondition("firstname", ConditionOperator.Equal, "Bill");
orFilter.AddCondition("firstname", ConditionOperator.Equal, "William");
Also allows for combining multiple Or statements
// SQL: WHERE (firstName = 'Bill' AND lastName = 'Smith') OR (firstName = 'William' AND lastName = 'Smith')
queryExpression.Where(
"firstname", "Bill",
"lastname", "Smith",
LogicalOperator.Or,
"firstname", "William",
"lastname", "Smith");
// Equivalent SDK Query
var orFilter = queryExpression.Criteria.AddFilter(LogicalOperator.Or);
var filter1 = orFilter.AddFilter(LogicalOperator.And);
filter1.AddCondition("firstname", ConditionOperator.Equal, "Bill");
filter1.AddCondition("lastname", ConditionOperator.Equal, "Smith");
var filter2 = orFilter.AddFilter(LogicalOperator.And);
filter2.AddCondition("firstname", ConditionOperator.Equal, "William");
filter2.AddCondition("lastname", ConditionOperator.Equal, "Smith");
As well as And and Or
// SQL: WHERE lastName = 'Smith' AND (firstName = 'Bill' OR firstName = 'William')
queryExpression.Where(
"lastname", "Smith"
LogicalOperator.And,
"firstname", "Bill",
LogicalOperator.Or,
"firstname", "William");
// Equivalent SDK Query
queryExpression.Criteria.AddCondition("lastname", ConditionOperator.Equal, "Smith");
var orFilter = queryExpression.Criteria.AddFilter(LogicalOperator.Or);
orFilter.AddCondition("firstname", ConditionOperator.Equal, "Bill");
orFilter.AddCondition("firstname", ConditionOperator.Equal, "William");
Note
The LogicalOpertor.And
is only needed in the context of splitting up conditions that should be and-ed from the or conditions. Ending a Where
expression with a LogicalOpertor.And
or having two LogicalOpertor.And
s without a LogicalOpertor.Or
separating them will result in a runtime error. It may be easier to think of it as an open parenthesis that most contain an or, and closes at the very end of the expression, rather than strictly and-ing the statement above with the statement below.