Dynamics 365 CE中使用FetchXML进行聚合运算

微软动态CRM专家罗勇 ，回复328或者20190429可方便获取本文，同时可以在第一间得到我发布的最新博文信息，follow me！

Dynamics 365 Customer Engagement可以通过使用FetchXML执行如下的聚合运算，如果你还只知道循环记录来计算的话你就OUT了，当然一次参与计算的记录默认不能超过5万行，若超过的话会报错。

• sum (求和)
• avg (求平均值)
• min (求最小值)
• max (求最大值)
• count(*) (记录计数)
• count(attribute name) (字段计数)

下面这个代码是一个例子，根据其关联的父记录，获取父记录的主属性的值，在后面加上序号来作为子记录主字段的值：

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

namespace Plugins
{
    public class PreLuoYontDemoEntityCreate : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity currentEntity = (Entity)context.InputParameters["Target"];
                IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
                try
                {
                    int detailsCount = 0;
                    var returnRequestEF = currentEntity.GetAttributeValue<EntityReference>("new_returnrequest");
                    if(returnRequestEF == null)
                    {
                        throw new InvalidPluginExecutionException("Field - Return Request is required!");
                    }
                    string LuoYontDemoEntity_count = string.Format(@"<fetch aggregate='true' mapping='logical' distinct='false'>
  <entity name='new_luoyongdemoentity'>
    <attribute name='new_luoyongdemoentityid' alias='luoyongdemoentityid_count' aggregate='count' />
    <filter type='and'>
      <condition attribute='new_returnrequest' operator='eq' value='{0}' />
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
  </entity>
</fetch>", returnRequestEF.Id);
                    EntityCollection LuoYontDemoEntity_count_result = service.RetrieveMultiple(new FetchExpression(LuoYontDemoEntity_count));
                    if(if (LuoYontDemoEntity_count_result.Entities.Count >= 1 && LuoYontDemoEntity_count_result.Entities[0].Contains("LuoYontDemoEntity_count") && LuoYontDemoEntity_count_result.Entities[0]["LuoYontDemoEntity_count"] != null && ((AliasedValue)LuoYontDemoEntity_count_result.Entities[0]["LuoYontDemoEntity_count"]).Value != null))
                    {
                        detailsCount = (Int32)((AliasedValue)LuoYontDemoEntity_count_result.Entities[0]["luoyongdemoentityid_count"]).Value;
                    }
                    currentEntity["new_name"] = service.Retrieve(returnRequestEF.LogicalName, returnRequestEF.Id, new ColumnSet("new_name")).GetAttributeValue<string>("new_name") + "-" + (detailsCount + 1).ToString("000");
                }
                catch (FaultException<OrganizationServiceFault> ex)
                {
                    throw new InvalidPluginExecutionException("An error occurred in PreLuoYontDemoEntityCreate.", ex);
                }
                catch (Exception ex)
                {
                    tracingService.Trace("PreLuoYontDemoEntityCreate unexpected exception: {0}", ex.Message);
                    throw;
                }
            }
        }
    }
}

 值得提一句的是，如果是对货币字段进行计算，其结果是货币类型，获得货币的数值需要先强制转换为货币，再获取其Value。

更多的详情请参考官方文档：Use FetchXML aggregation