Select a percentage of records using CRM 2011 Dynamic Entity

Question

I'm developing service which consumes CRM 2011 data via dynamic entities (as in, Microsoft.Xrm.Sdk.Entity, the late-binding method). I'm deliberately not using Xrm.cs method (early binding) in an attempt to keep my solution generic.

Also, I want to avoid connecting to a CRM database directly (e.g. EDMX) as this would stop my solution being usable for a hosted CRM (e.g. with no direct DB access).

I have the following (simplified) requirement, I'm really struggling with the selection criteria:

A random 7% of records needs to be selected (and updated).

In SQL, the selection criteria would be relatively easy - I know how to select a random percentage of records. Something like:

SELECT TOP 7 PERCENT * FROM
(
    SELECT TOP 1000 NEWID() AS Foo, [someColumns]
    FROM [someTable]
)
AS Bar ORDER BY Bar.Foo ASC

This works perfectly. I gather the LINQ equivalent is something like:

from e in someEntities
orderby Guid.NewGuid()
select e;

There's a problem though, I don't know of a way to use LINQ with CRM 2011 dynamic entities - instead they insist on using either some restrictive QueryExpression classes/syntax, or fetchXML, as seen on this page (MSDN).

I've identified the following options for fulfilling this requirement:

1. Using dynamic entities, return the whole record set into a List, then simply choose a random selection by index. This however involves returning up to 10,000 records over an internet data service, which may be slow/insecure/etc.

2. Use a fetchXML statement. Unfortunately I don't know fetchXML, so I don't know if it's possible to do things like COUNT, TOP, PERCENT or NEWID().

3. Use Xrm.cs and LINQ, or use a Stored Procedure, or a SQL view. All of these options mean tying the solution down to either direct database connectivity and/or early binding, which is not desirable.

4. Say no to the customer.

Any advise would be greatly appreciated! Can fetchXML perform this query? Is there a better way to do this?

Answer 1

FetchXML does not support this, so you are down to either 1 or 3. And you are right, 3 would only work in the On Premise version, as you can't connect directly to SQL with the CRM Online product. However, that's the one I would go with unless you are absolutely sure the customer will be moving to CRM Online. If you must go with 1, you can at least limit the returned columns to only be the GUID of the record to decrease the payload size. Then when you select your random records, just go get their additional columns if needed (of course this could end up being slower due to "chattiness" depending on how many random records you are dealing with).

Answer 2

Dynamics CRM 2011, at this point, can't give you the degree of querying power that SQL and other LINQ providers can give, so I really believe you'll want to say no to the customer and move to the on-premise version if he/she wants that kind of flexibility.

With that said, a variant of method #1 is to, rather than fetch all rows at once and then choose your random set, fetch a random set from the entity one row at a time until you have the number of rows you want. The downside of this method is that instead of one call to the DB, there are many, which slows down the overall retrieve speed. A POC is below.

As for #2, I believe it's possible to handle all of your request, with some degree of success, using fetchXml. In fact, the only way to get aggregated data is by using fetchXml, and it also supports paging.

As for #3, native SQL is your best bet to get everything you want out of your data at this point, but that notwithstanding, while the LINQ provider is limited, it's a lot easier to transition SQL statements to LINQ than to fetchXML, and it does support late-binding/dynamic entities.

//create a list of random numbers
List<int> randomNumbers = new List<int>();

//declare a percentage of records you'd like to retrieve
double pctg = 0.07;

//use FetchXML to count the # of rows in the table
string fetchXml = @"<fetch aggregate='true'>
<entity name='salesorder'>
<attribute name='salesorderid' aggregate='count' alias='countIds' distinct='false' />
</entity>
</fetch>";
EntityCollection result = _service.RetrieveMultiple(new FetchExpression(fetchXml));
int rowCount = int.Parse(result.Entities[0].FormattedValues["countIds"].Replace(",", ""));

//initalize the random number list for paging
for (int i = 0; i < Math.Ceiling(pctg * rowCount); i++)
{
    randomNumbers.Add((new Random(unchecked((int)(DateTime.Now.Ticks >> i)))).Next(rowCount - 1));
}
randomNumbers.Sort();

//page through the rows one at a time until you have the number of rows you want
using (OrganizationServiceContext osc = new OrganizationServiceContext(_service))
{
    foreach (int r in randomNumbers)
    {
        foreach (var er in (from c in osc.CreateQuery("salesorder")
                            //not especially useful to use the orderby option as you can only order by entity attributes
                            //orderby c.GetAttributeValue<string>("name")
                            select new
                            {
                                name = c.GetAttributeValue<string>("name")
                            }).Skip(r).Take(1))
        {
            Console.WriteLine(er.name);
        }

    }
}