Use the QueryExpression class

In Microsoft Dataverse, you can use the QueryExpression class to build complex queries for use with the IOrganizationService.RetrieveMultiple method or the RetrieveMultipleRequest message. You can set query parameters to the QueryExpression by using the ConditionExpression, ColumnSet, and FilterExpression classes.

The QueryExpression class lets you create complex queries. The QueryByAttribute class is designed to be a simple way to search for table rows where columns match specified values.

Record count

To find out how many records the query returned, set the ReturnTotalRecordCount property to true before executing the query. When you do this, the TotalRecordCount is set. Otherwise, this value is -1.

Example

The following sample shows how to use the QueryExpression class.

//  Query using ConditionExpression and FilterExpression  
ConditionExpression condition1 = new ConditionExpression();  
condition1.AttributeName = "lastname";  
condition1.Operator = ConditionOperator.Equal;  
condition1.Values.Add("Brown");              
  
FilterExpression filter1 = new FilterExpression();  
filter1.Conditions.Add(condition1);  
  
QueryExpression query = new QueryExpression("contact");  
query.ColumnSet.AddColumns("firstname", "lastname");  
query.Criteria.AddFilter(filter1);  
  
EntityCollection result1 = _serviceProxy.RetrieveMultiple(query);  
Console.WriteLine();Console.WriteLine("Query using Query Expression with ConditionExpression and FilterExpression");  
Console.WriteLine("---------------------------------------");  
foreach (var a in result1.Entities)  
{  
    Console.WriteLine("Name: " + a.Attributes["firstname"] + " " + a.Attributes["lastname"]);  
}  
Console.WriteLine("---------------------------------------");  

Use SQL hints in a query

The QueryExpression class contains a property named QueryHints. By setting this property to one of the supported string values shown below, you can provide a hint for generated SQL text that affects the query's execution.

QueryHint value	SQL Query Option and Hint
OptimizeForUnknown	Optimize For Unknown
ForceOrder	Force Order
DisableRowGoal	use hint('Disable_Optimizer_RowGoal')
EnableOptimizerHotfixes	use hint('ENABLE_QUERY_OPTIMIZER_HOTFIXES')
LoopJoin	Loop Join
MergeJoin	Merge Join
HashJoin	Hash Join
NO_PERFORMANCE_SPOOL	NO_PERFORMANCE_SPOOL
MaxRecursion	MAXRECURSION number
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS	ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS

More information: Hints (Transact-SQL) - Query

See also

Building Queries with QueryExpression
Use the ColumnSet Class
Using the ConditionExpression Class
Using the FilterExpression Class
QueryExpression