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 will be set. Otherwise, this value will be -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 which affects the query's execution.

QueryHint value	SQL Query Option and Hint
OptimizeForUnknown	Optimize For Unknown
ForceOrder	Force Order
Recompile  (see note)	Recompile
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

Important

In general, the “Recompile” query hint should be avoided. This query hint will require extra computation and database resources for every execution of the query on the database. Please read Microsoft’s documentation on the “OPTION (RECOMPILE)” query hint, or work with Microsoft customer support, to find out more about the appropriate use cases of this query hint.

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