Redaguoti

Bendrinti naudojant

Query data by 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 by 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 or XML manipulation required when 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 by 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 you initialize the query instance, you can:

Property assignment style

You can compose the same query without the QueryExpression(String) constructor or object initialization style. Just set the properties on 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 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. If you don't set the TopCount property, Dataverse returns up to 5,000 rows for standard tables and 500 for elastic tables.

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

You can't use the TopCount property when you request a count of rows by 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. 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 to set the query to the RetrieveMultipleRequest.Query property. Then, send the request by 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

FetchXml supports some capabilities that QueryExpression doesn't.

Important

If you use the FetchXmlToQueryExpression message with either the SDK FetchXmlToQueryExpressionRequest class or Web API FetchXmlToQueryExpression function, any capabilities that QueryExpression doesn't support aren't applied and no error occurs.

Community tools

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

Note

Microsoft doesn't support tools created by the community. If you have questions or issues with community tools, contact the publisher of the tool.

Use QueryExpression as a message parameter

You can 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's currently no way to use these structures to retrieve data with QueryExpression by using the Web API as you can with FetchXml. This limitation means you can't test the results of the query you send as a parameter by using Web API.

Next steps

Learn how to select columns.

Select columns

Try some queries.

QueryExpression sample code

  • Last updated on