如何将SQL查询转换为Microsoft.Crm.Sdk查询？适配Dynamics 365复杂逻辑

首先，咱们先拆解你原SQL的核心逻辑：找出那些满足以下条件的付款记录（new_paymentid）：

• 关联的交易状态为4，且交易日期符合特定范围（注：原SQL的日期条件可能存在逻辑问题，后面会说明）
• 付款状态不是5、6、8，且未被跟进（new_alreadyfollowup不为1或为null）
• 该付款在至少3个不同月份中，每个月都有恰好2笔符合条件的交易

由于Dynamics 365云端不支持直接执行原生SQL（除非用Azure SQL导出数据，但这不是常规做法），咱们需要用FetchXML配合ASP.NET中的OrganizationService来实现——FetchXML是Dynamics 365官方推荐的复杂查询方式，支持聚合、分组和关联查询。

步骤1：编写内层聚合FetchXML（对应原SQL的子查询） #

这个查询会统计每个付款在每个月份的符合条件交易数量：

<fetch aggregate='true' distinct='false'>
  <entity name='new_transaction'>
    <!-- 按付款ID分组 -->
    <attribute name='new_paymentid' alias='PaymentId' groupby='true' />
    <!-- 按交易月份（数字）分组 -->
    <attribute alias='TransMonth' groupby='true'>
      <function name='month'>
        <attribute name='new_transactiondate' />
      </function>
    </attribute>
    <!-- 统计每个分组的交易数量 -->
    <attribute name='new_transactionid' alias='MonthCount' aggregate='count' />
    
    <!-- 交易表的过滤条件 -->
    <filter type='and'>
      <condition attribute='new_status' operator='eq' value='4' />
      <!-- 原SQL的日期条件（注意：此条件实际无过滤效果，建议替换为最近3个月） -->
      <condition attribute='new_transactiondate' operator='on-or-after'>
        <value function='concat'>
          <value function='year'>
            <value function='addmonths'>
              <value attribute='new_transactiondate' />
              <value>-3</value>
            </value>
          </value>
          <value>-</value>
          <value function='month'>
            <value function='addmonths'>
              <value attribute='new_transactiondate' />
              <value>-3</value>
            </value>
          </value>
          <value>-01</value>
        </value>
      </condition>
    </filter>
    
    <!-- 关联付款表并添加过滤条件 -->
    <link-entity name='new_payment' from='new_paymentid' to='new_paymentid' link-type='inner' alias='p'>
      <filter type='and'>
        <condition attribute='new_paymentstatus' operator='notin' values='5,6,8' />
        <filter type='or'>
          <condition attribute='new_alreadyfollowup' operator='neq' value='1' />
          <condition attribute='new_alreadyfollowup' operator='null' />
        </filter>
      </filter>
    </link-entity>
  </entity>
</fetch>

关于日期条件的说明 #

原SQL中的日期条件New_TransactionDate >= convert(...)实际上对所有交易都成立（因为任何日期都大于等于自己往前推3个月的当月第一天），这可能是笔误。如果你的真实需求是交易日期在最近3个月内，请把上述日期条件替换为：

<condition attribute='new_transactiondate' operator='on-or-after'>
  <value function='addmonths'>
    <value function='utcnow' />
    <value>-3</value>
  </value>
</condition>

步骤2：在ASP.NET中执行FetchXML并处理结果 #

由于Dynamics 365不支持多层聚合（即聚合结果再聚合），我们需要先执行内层FetchXML，然后在C#内存中完成后续的筛选和分组：

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Linq;

// 假设你已经初始化了OrganizationService实例（service）
string innerFetchXml = @"<fetch aggregate='true' distinct='false'>
  <entity name='new_transaction'>
    <attribute name='new_paymentid' alias='PaymentId' groupby='true' />
    <attribute alias='TransMonth' groupby='true'>
      <function name='month'>
        <attribute name='new_transactiondate' />
      </function>
    </attribute>
    <attribute name='new_transactionid' alias='MonthCount' aggregate='count' />
    <filter type='and'>
      <condition attribute='new_status' operator='eq' value='4' />
      <!-- 替换成你需要的日期条件 -->
      <condition attribute='new_transactiondate' operator='on-or-after'>
        <value function='addmonths'>
          <value function='utcnow' />
          <value>-3</value>
        </value>
      </condition>
    </filter>
    <link-entity name='new_payment' from='new_paymentid' to='new_paymentid' link-type='inner' alias='p'>
      <filter type='and'>
        <condition attribute='new_paymentstatus' operator='notin' values='5,6,8' />
        <filter type='or'>
          <condition attribute='new_alreadyfollowup' operator='neq' value='1' />
          <condition attribute='new_alreadyfollowup' operator='null' />
        </filter>
      </filter>
    </link-entity>
  </entity>
</fetch>";

// 执行内层查询
EntityCollection innerResults = service.RetrieveMultiple(new FetchExpression(innerFetchXml));

// 处理结果：筛选每个月交易数=2的记录，再按付款ID分组，保留分组数>=3的付款ID
var targetPaymentIds = innerResults.Entities
    .Where(entity => 
        // 获取每个分组的交易数量，判断是否等于2
        ((int)((AliasedValue)entity.Attributes["MonthCount"]).Value) == 2)
    .GroupBy(entity => 
        // 按付款ID分组（EntityReference类型）
        ((EntityReference)((AliasedValue)entity.Attributes["PaymentId"]).Value))
    .Where(group => 
        // 保留至少有3个符合条件月份的付款
        group.Count() >= 3)
    .Select(group => 
        // 提取付款的GUID
        group.Key.Id);

// 现在targetPaymentIds就是你需要的new_paymentid集合
foreach (var paymentId in targetPaymentIds)
{
    Console.WriteLine($"符合条件的付款ID：{paymentId}");
}

关键知识点说明 #

1. FetchXML聚合与分组：通过aggregate='true'开启聚合，groupby='true'指定分组字段，用alias为聚合/分组结果命名。
2. 关联实体：用<link-entity>实现SQL的INNER JOIN，link-type='inner'对应内连接。
3. 处理AliasedValue：聚合或分组的结果会封装在AliasedValue对象中，需要通过Value属性提取实际值。
4. 日期函数：FetchXML支持addmonths、utcnow、year、month等日期函数，用于动态计算日期范围。

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