如何使用FilterExpression实现CRM记录的不区分大小写过滤？

Great question! Let's break down what's happening here and fix your issue step by step:

First, the Core Answer #

Yes, Dynamics 365/Dataverse's FilterExpression and ConditionExpression support case-insensitive filtering by default. String operations like Equal, Contains, and Like ignore case, thanks to Dataverse's default collation setting (SQL_Latin1_General_CP1_CI_AS, where CI stands for Case Insensitive).

So why isn't your input FRY PARASKEVI matching the CRM record Fry, Paraskevi? The problem lies in your code logic, not the case-sensitivity of the filtering itself.

What's Wrong with Your Current Code #

Let's walk through the scenario:

1. Input FRY PARASKEVI gets split by your regex into ["FRY", "PARASKEVI"]
2. The first sub-condition checks if fullname equals the exact input — this fails because the CRM record has a comma (Fry, Paraskevi vs FRY PARASKEVI)
3. The second sub-filter checks if fullname contains both FRY and PARASKEVI — this should work with default case-insensitive collation, but if your CRM instance uses a case-sensitive collation (rare, but possible), this would fail.
4. The third sub-filter uses In on firstname and lastname with the split fragments, but this logic is flawed: it requires firstname to be either FRY or PARASKEVI AND lastname to be either FRY or PARASKEVI, which works for ordered inputs but isn't robust for name variations.

Fixing the Case-Sensitivity & Logic Issues #

Option 1: Ensure Case-Insensitive Matching (For All Collation Types) #

If your CRM uses a case-sensitive collation, or you want to guarantee case-insensitive results regardless of settings, replace Contains with Like and standardize the case of your search terms:

// Modify the second sub-filter (fullname contains split fragments)
var filterCheckInputContainedInFullName = new FilterExpression(LogicalOperator.And);
Regex rgx0 = new Regex("[^a-zA-Z0-9]");
var tmparr0 = rgx0.Split(revenueEntity.ClientName)
                 .Where(s => !DataFeedProviderInputValidator.IsEmptyValue(s))
                 .ToArray();

// Convert each fragment to uppercase (or lowercase) and use Like with wildcards
foreach (var tmparritem in tmparr0)
{
    var normalizedTerm = tmparritem.ToUpper();
    filterCheckInputContainedInFullName.AddCondition(
        "fullname", 
        ConditionOperator.Like, 
        $"%{normalizedTerm}%"
    );
}

// Fix the third sub-filter for better name matching
var filterCheckFullNameContainedInInput = new FilterExpression(LogicalOperator.And);
var normalizedArr = tmparr0.Select(s => s.ToUpper()).ToArray();
// Match firstname OR lastname for each fragment
var nameFragmentFilter = new FilterExpression(LogicalOperator.Or);
foreach (var term in normalizedArr)
{
    var firstNameCondition = new ConditionExpression("firstname", ConditionOperator.Like, $"%{term}%");
    var lastNameCondition = new ConditionExpression("lastname", ConditionOperator.Like, $"%{term}%");
    var termFilter = new FilterExpression(LogicalOperator.Or);
    termFilter.Conditions.Add(firstNameCondition);
    termFilter.Conditions.Add(lastNameCondition);
    nameFragmentFilter.Filters.Add(termFilter);
}
filterCheckFullNameContainedInInput.Filters.Add(nameFragmentFilter);

Option 2: Use FetchXML for More Flexibility #

If you need even more control (like directly using string functions to normalize case), switch to FetchXML and convert it to a QueryExpression:

<fetch top="50">
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <filter type="or">
        <!-- Exact match (case-insensitive) -->
        <condition attribute="fullname" operator="eq" value="FRY PARASKEVI" />
        <!-- Fullname contains all split fragments -->
        <filter type="and">
          <condition attribute="fullname" operator="like" value="%FRY%" />
          <condition attribute="fullname" operator="like" value="%PARASKEVI%" />
        </filter>
        <!-- Match firstname or lastname for each fragment -->
        <filter type="and">
          <filter type="or">
            <condition attribute="firstname" operator="like" value="%FRY%" />
            <condition attribute="lastname" operator="like" value="%FRY%" />
          </filter>
          <filter type="or">
            <condition attribute="firstname" operator="like" value="%PARASKEVI%" />
            <condition attribute="lastname" operator="like" value="%PARASKEVI%" />
          </filter>
        </filter>
      </filter>
    </filter>
  </entity>
</fetch>

Then convert it to a QueryExpression in code:

var fetchXml = @"[Your FetchXML string here]";
var query = FetchXmlToQueryExpressionConverter.Convert(fetchXml);

Option 3: Validate Your CRM Collation #

Double-check your Dataverse instance's collation:

• Go to Advanced Settings > Settings > Customizations > Developer Resources
• Look for the Collation value — if it ends with _CS_AS, it's case-sensitive. You can request a collation change from Microsoft support if needed.

Final Notes #

With these fixes, your input FRY PARASKEVI should correctly match Fry, Paraskevi by:

1. Normalizing search terms to a consistent case
2. Using Like to handle partial matches with wildcards
3. Fixing the name-matching logic to be more robust for name order variations

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