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

Answer 1

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.

Answer 2

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);