Редагувати

Поділитися через

Query data using QueryExpression

  • Стаття

The QueryExpression class, together with other classes in the Microsoft.Xrm.Sdk.Query namespace, provides an object model to compose complex queries to retrieve records from Dataverse using the IOrganizationService.RetrieveMultiple method. Compare options when querying data using the SDK for .NET

Compose a query

Use QueryExpression to compose dynamic queries that you can modify without the string/xml manipulation required using FetchXml.

All queries are based on a single table. Use the QueryExpression class to select the table the query retrieves data from.

Object initialization style

The following example represents a simple QueryExpression query that returns the Name column of the first five rows from the Account table using the object initializer so that the query is defined in a single assignment.

public static EntityCollection SimpleExample(IOrganizationService service) {

   QueryExpression query = new("account")
   {
         ColumnSet = new ColumnSet("name"),
         TopCount = 5
   };

   return service.RetrieveMultiple(query);
}

When the query instance is initialized, you can:

Property assignment style

You can compose the same query without the QueryExpression(String) constructor or object initialization style and just set the properties to the instantiated instance as shown in the following example:

public static EntityCollection SimpleExample(IOrganizationService service)
{

   QueryExpression query = new();
   query.EntityName = "account";
   query.ColumnSet.AddColumn("name");
   query.TopCount = 5;

   return service.RetrieveMultiple(query);
}

This example shows how you can:

Examples in this documentation will use a combination of object initialization and property assignment styles. As queries become more complex, the object initialization style can become unwieldy. You can always define the query properties separately and add them to the query by setting the properties or using the available methods.

Limit the number of rows

To limit the number of rows returned, use the QueryExpression.TopCount property. Without setting the TopCount property, Dataverse returns up to 5,000 rows.

Alternatively, specify a number of records to return using paging. Don't use the TopCount property when you request pages of data. Learn how to request paged results

You can't use TopCount property when you request a count of rows using the PagingInfo.ReturnTotalRecordCount property. Learn to count rows

Return distinct results

Use the QueryExpression.Distinct property to require the query to exclude any duplicate values in the results.

If you use the Distinct property, you must add at least one OrderExpression to the QueryExpression.Orders property to have consistent paging.

When you use the Distinct property, the results returned don't include primary key values for each record because they represent an aggregation of all the distinct values.

Retrieve Data

As explained in Query data using the SDK for .NET, QueryExpression is one of three types derived from the QueryBase class, so you can pass it to the IOrganizationService.RetrieveMultiple Method to get an EntityCollection containing the results.

EntityCollection results = service.RetrieveMultiple(query);

Tip

Try using the QueryExpression sample code to use this method.

You can also use the RetrieveMultipleRequest class set the query to the RetrieveMultipleRequest.Query property to sent the request using the IOrganizationService.Execute Method.

RetrieveMultipleRequest request = new()
{
      Query = query
};
var response = (RetrieveMultipleResponse)service.Execute(request);

EntityCollection results = response.EntityCollection;

Use the RetrieveMultipleRequest class when you want to:

Refine your query

After you select the table to start your query with, refine the query to get the data you need. The following articles explain how to complete your query.

Article Task
Select columns Specify which columns of data to return.
Join tables Specify which related tables to return in the results.
Order rows Specify the sort order of the rows to return.
Filter rows Specify which rows of data to return.
Page results Specify how many rows of data to return with each request.
Aggregate data How to group and aggregate the data returned.
Count number of rows How to get a count of the number of rows returned.
Performance optimizations How to optimize performance

Limitations

There are some things that you can do using FetchXml that QueryExpression doesn't support.

Important

If you use the FetchXmlToQueryExpression message with either the SDK FetchXmlToQueryExpressionRequest class or Web API FetchXmlToQueryExpression function, any capabilities not supported by QueryExpression are not applied and there will be no error.

Community tools

The XrmToolBox FetchXMLBuilder is a free tool to compose and test FetchXml requests, but it also generates code for QueryExpression queries using the same designer experience.

Note

Tools created by the community are not supported by Microsoft. If you have questions or issues with community tools, contact the publisher of the tool.

Use QueryExpression as a message parameter

You also use QueryExpression as a parameter for Dataverse operations such as the following messages:

Message Name SDK for .NET Request class Web API Operation
BackgroundSendEmail BackgroundSendEmailRequest BackgroundSendEmail action
BulkDetectDuplicates BulkDetectDuplicatesRequest BulkDetectDuplicates action
BulkDelete BulkDeleteRequest BulkDelete action
FullTextSearchKnowledgeArticle FullTextSearchKnowledgeArticleRequest FullTextSearchKnowledgeArticle action
QueryExpressionToFetchXml QueryExpressionToFetchXmlRequest QueryExpressionToFetchXml action
SendBulkMail SendBulkMailRequest SendBulkMail action
SyncBulkOperation SyncBulkOperationRequest SyncBulkOperation action
Rollup RollupRequest Rollup function

Note

Web API Operations other than BulkDelete, SyncBulkOperation, and QueryExpressionToFetchXml action can use FetchXml via the FetchExpression complex type. While the Web API contains the structures to compose queries using QueryExpression, such as the QueryExpression, ColumnSet, and FilterExpression complex types, there is currently no way to use these to retrieve data with QueryExpression using the Web API as you can with FetchXml. This means it isn't possible to test the results of the query you would send as a parameter using Web API.

Next steps

Learn how to select columns.

Select columns

Try some queries.

QueryExpression sample code