CRM中字符串类型数字排序异常及QueryExpression优化问询

Ah, that classic string vs numeric sorting gotcha! It’s super frustrating when 9 ends up above 10 because the system’s comparing character codes instead of actual values. While padding with leading zeros works, I get wanting a cleaner solution using QueryExpression—let’s break down how to do this.

Why the Problem Happens #

First, a quick recap: When you sort a string field, CRM uses lexicographical (character-by-character) sorting. Since the ASCII value of '9' is higher than '1', "9" gets ranked above "10" every time.

Solutions Using QueryExpression #

Unfortunately, QueryExpression doesn’t have a built-in way to cast a string field to an integer directly for sorting—but we can work around this with FetchXML (which does support type casting) or by using a custom sort clause.

Option 1: FetchXML Cast + Convert to QueryExpression #

FetchXML allows you to use the cast function to treat the string field as an integer during sorting. You can write the FetchXML query, then convert it to a QueryExpression if you need to stick with that API.

Here’s an example FetchXML that casts your string ID field to an integer and sorts descending:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="your_entity_name">
    <attribute name="ID" />
    <order attribute="ID" descending="true">
      <attribute name="ID" alias="casted_id" >
        <cast type="int" />
      </attribute>
    </order>
  </entity>
</fetch>

Then convert this FetchXML to a QueryExpression in your code:

// Load the FetchXML string
string fetchXml = @"<fetch version=""1.0"" output-format=""xml-platform"" mapping=""logical"" distinct=""false"">
  <entity name=""entity"">
    <attribute name=""ID"" />
    <order attribute=""ID"" descending=""true"">
      <attribute name=""ID"" alias=""casted_id"" >
        <cast type=""int"" />
      </attribute>
    </order>
  </entity>
</fetch>";

// Convert to QueryExpression
QueryExpression query = FetchXmlToQueryExpressionConverter.Convert(fetchXml);

// Execute the query as usual
EntityCollection entityCollection = service.RetrieveMultiple(query);

Option 2: Custom Sort Clause in QueryExpression #

If you prefer to build the QueryExpression directly, you can use the OrderExpression.CustomOrderByClause property to specify a SQL-like cast. Note: This requires your CRM organization to allow custom SQL in queries (check system settings—though this has security implications, so use cautiously).

Example code:

QueryExpression query = new QueryExpression("entity");
query.ColumnSet.AddColumn("ID");

// Create an OrderExpression with custom casting
OrderExpression order = new OrderExpression();
order.AttributeName = "ID";
order.OrderType = OrderType.Descending;
// Specify the custom sort clause to cast the string to int
order.CustomOrderByClause = "CAST(ID AS INT) DESC";

query.Orders.Add(order);

// Execute the query
EntityCollection entityCollection = service.RetrieveMultiple(query);

Pros and Cons vs. Leading Zeros #

• Leading zeros: Simple, no query changes, but makes the field less user-friendly (e.g., "0010" instead of "10") and requires data cleanup if existing records don’t have padding.
• Query-side casting: Keeps the raw data clean, works on existing records, but relies on FetchXML or custom SQL (which may be restricted in some orgs).

Final Notes #

Make sure to test these queries in your environment—some CRM versions might have slight syntax differences for the cast function in FetchXML. Also, if the string field ever contains non-numeric values, the cast will fail, so you’ll want to add filters to exclude those if needed!

内容的提问来源于stack exchange，提问作者Moran Barzilay