Query particular set of records with QueryExpression

Question

Is there a way to build a QueryExpression returning just a particular set of records?

I have the following Criteria Types:

First:
Returns the first n Records (i.e. select top)

Last:
Returns the last n records

Every:
Returns every n'th record

For the type "First" I can use

queryExpression.TopCount = number_of_records

But I have no Idea how I can achieve the other types of criteria. The issue is that there are quite big data volumes and if I need first to get all records and query the result for example with Linq to customize the resultset I will probably have a performance issue.

If I could build the QueryExpression just selecting exactly what I need the whole thing gets more efficient.

Does anybody have an idea on how to achieve this with a QueryExpression?

The system in question is Microsoft Dynamics CRM Online

Can you explain why you would want to query for every Nth record? — Zach Mast, Jul 12 '19 at 12:36
the user needs the ability to customize the number of returned data. the user can define queries (fetchxml) in a custom form. lets say such a query returns him > 50k records. the user then can decide to let the mechanism select just a subset of the complete resultset. this way he can control the number of returned records without the need to change the query itself which is configured as fetchxml — Martin Felber, Jul 12 '19 at 13:50

score 2 · Accepted Answer · answered Jul 12 '19 at 12:33

2

For the "last N" you can reverse the sort and use TopCount again.

For the "every Nth" you might want to consider paging the Query Expression.

Say you're looking for every 10th record. What I might do would be to set my page size to 10 (query.PageInfo.Count).

To iterate through the pages as quickly as possible I'd make my "main" query return only the GUIDs. When I retrieve a new page of GUIDs, I'd grab the first GUID and get the columns I want for that record using a separate Retrieve call.

answered Jul 12 '19 at 12:33

Aron

3,877
3
14
21

thx for you feedback....right, reversing the sort is a good point :) do you may have an example regarding the paging? – Martin Felber Jul 12 '19 at 13:12
You're welcome. The link from the answer has a QueryExpression paging example: https://learn.microsoft.com/en-us/powerapps/developer/common-data-service/org-service/page-large-result-sets-with-queryexpression – Aron Jul 12 '19 at 13:13

score 2 · Answer 2 · answered Jul 12 '19 at 12:36

Last N Records: quite simple order by particular field as descinding and then top N that's it Returns the last n records

// Instantiate QueryExpression QEaccount
var QEaccount = new QueryExpression("account");
QEaccount.TopCount = 5;

// Add columns to QEaccount.ColumnSet
QEaccount.ColumnSet.AddColumns("name", "ah_account_type", "accountid");
QEaccount.AddOrder("name", OrderType.Descending);

Every nth Record:

Do you have any particular criteria here, for example give me all accounts where country =Germany if yes then you can user condition to return particular set of records as below

// Define Condition Values
var QEaccount_address1_country = "Germany";

// Instantiate QueryExpression QEaccount
var QEaccount = new QueryExpression("account");

// Add columns to QEaccount.ColumnSet
QEaccount.ColumnSet.AddColumns("name", "ah_account_type", "accountid", "address1_country");

// Define filter QEaccount.Criteria
QEaccount.Criteria.AddCondition("address1_country", ConditionOperator.Equal, QEaccount_address1_country);

Query particular set of records with QueryExpression

2 Answers2