这是我的第491篇原创文章，写于2023年4月11日。
做个假设，我们需要获取My Active Contacts视图中的唯一Company Name和 Address 1:City的值，我用如下的示例数据来做测试，可以看到有13行记录。
筛选条件如下：
下载下来的FetchXml如下：
<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='false' distinct='true'>
<entity name='contact'>
<attribute name='entityimage_url'/>
<attribute name='fullname'/>
<attribute name='parentcustomerid'/>
<attribute name='telephone1'/>
<attribute name='emailaddress1'/>
<attribute name='contactid'/>
<attribute name='address1_city'/>
<order attribute='parentcustomerid' descending='false'/>
<filter type='and'>
<condition attribute='ownerid' operator='eq-userid'/>
<condition attribute='statecode' operator='eq' value='0'/>
</filter>
</entity>
</fetch>如果我用OData风格的来重写这个查询，那么就是，当然这两个不是完全对等的查询，用到的 EqualUserId Function 函数请参考官方文档 Web API Query Function Reference 。
https://org09943724.crm5.dynamics.com/api/data/v9.2/contacts?$select=entityimage_url,fullname,_parentcustomerid_value,telephone1,emailaddress1,contactid,address1_city&$filter=statecode eq 0 and Microsoft.Dynamics.CRM.EqualUserId(PropertyName=@p1)&@p1='ownerid'&$orderby=_parentcustomerid_value asc
以前我记得使用Web API来查询数据时候不支持聚合查询，现在开始支持了，我用这种方法来看看。支持的聚合查询请参考官方文档 Query data using the Web API 的 Aggregate and grouping results 章节。
我猜测语句应该如下写法：
https://org09943724.crm5.dynamics.com/api/data/v9.2/contacts?$apply=filter(statecode eq 0 and Microsoft.Dynamics.CRM.EqualUserId(PropertyName='ownerid'))/groupby((address1_city,parentcustomerid/name))
但是执行报错如下：
{"error":{"code":"0x0","message":"Could not find a property named 'parentcustomerid' on type 'Microsoft.Dynamics.CRM.contact'."}}如果我稍微更改下，改成类似如下的就不报错了，可能是对聚合的支持还有限吧，若有读者知道如何改写或者支持完善了我再更新本博文。
https://org09943724.crm5.dynamics.com/api/data/v9.2/contacts?$apply=filter(statecode eq 0 and Microsoft.Dynamics.CRM.EqualUserId(PropertyName='ownerid'))/groupby((familystatuscode,gendercode))
返回结果如下供参考：
既然用聚合函数做不到，我们用Flow的union来试试。
先查询出符合条件的记录和仅仅需要的字段，我这里使用Microsoft Dataverse的List rows步骤，设置如下图所示。Filter rows用的表达式是：statecode eq 0 and Microsoft.Dynamics.CRM.EqualUserId(PropertyName='ownerid') 。 Select columns用的表达式是 _parentcustomerid_value,address1_city ，记得对于查找字段和Customer类型字段，是需要在字段逻辑名称前面加上下划线，后面加上下划线和value的，否则查询会报错。
可以看到返回的内容还挺多的：
然后我们需要用 Data Operation下面的Select来构造一个仅仅需要取唯一的两个列的数组。
设置如下：
这个设置Peek code后展示的表达式是：
{
"inputs": {
"from": "@outputs('List_rows')?['body/value']",
"select": {
"parentcustomerid": "@item()?['_parentcustomerid_value']",
"address1_city": "@item()?['address1_city']"
}
}
}执行后返回的内容如下，13个元素，没错。
[
{
"parentcustomerid": "946b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Redmond"
},
{
"parentcustomerid": "966b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Issaquah"
},
{
"parentcustomerid": "986b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Duvall"
},
{
"parentcustomerid": "9a6b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Monroe"
},
{
"parentcustomerid": "9c6b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Issaquah"
},
{
"parentcustomerid": "9e6b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Issaquah"
},
{
"parentcustomerid": "a06b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Snohomish"
},
{
"parentcustomerid": "a26b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Redmond"
},
{
"parentcustomerid": "a46b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
},
{
"parentcustomerid": "a66b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Renton"
},
{
"parentcustomerid": "a26b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
},
{
"parentcustomerid": "a46b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
},
{
"parentcustomerid": "a66b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
}
]然后我们用union函数来取唯一，这里我们用Compose步骤来展示结果，当然后面需要的话可以直接用Compose步骤中的表达式即可。
设置如下，使用的表达式是：union(body('Select'),body('Select')) ，就是将前面Select步骤返回结果用uion函数来取唯一。
执行后返回的结果是，结果只有12条记录，正确。
[
{
"parentcustomerid": "946b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Redmond"
},
{
"parentcustomerid": "966b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Issaquah"
},
{
"parentcustomerid": "986b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Duvall"
},
{
"parentcustomerid": "9a6b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Monroe"
},
{
"parentcustomerid": "9c6b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Issaquah"
},
{
"parentcustomerid": "9e6b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Issaquah"
},
{
"parentcustomerid": "a06b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Snohomish"
},
{
"parentcustomerid": "a26b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Redmond"
},
{
"parentcustomerid": "a46b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
},
{
"parentcustomerid": "a66b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Renton"
},
{
"parentcustomerid": "a26b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
},
{
"parentcustomerid": "a66b96b4-49a0-ed11-aad1-002248593065",
"address1_city": "Seattle"
}
]如果我们用FetchXml来获取唯一呢？当然也是可以的，这里我们用的FetchXml是，就是将distinct属性设置为true即可获取到唯一值。
<fetch version='1.0' mapping='logical' no-lock='true' distinct='true'>
<entity name='contact'>
<attribute name='parentcustomerid'/>
<attribute name='address1_city'/>
<filter type='and'>
<condition attribute='ownerid' operator='eq-userid'/>
<condition attribute='statecode' operator='eq' value='0'/>
</filter>
</entity>
</fetch>我们还是Microsoft Dataverse的List rows来查询，如下：
但是执行起来会报错如下，因为主键没有值，但是distinct中如果返回列包括主键就没有意义了，主键是唯一的。
Key property 'contactid' of type 'Microsoft.Dynamics.CRM.contact' is null. Key properties cannot have null values.
当然直接用Web API是没问题的，示例如下：
但是我发现如果你变化一个fetchXml的写法又可以，就是用聚合查询，然后分组，取分组值就可以，特别注意要分组的列要加上不能为空的条件，如果有为空的，很可能会报错：The provided query options are not valid: System.ArgumentException: 'select' and 'expand' cannot be both null or empty.
比如我这里改成如下的FetchXml就可以了：
<fetch version='1.0' mapping='logical' no-lock='true' distinct='true' aggregate='true'>
<entity name='contact'>
<attribute name='parentcustomerid' groupby='true' alias='parentid' />
<attribute name='address1_city' groupby='true' alias='city' />
<filter type='and'>
<condition attribute='ownerid' operator='eq-userid'/>
<condition attribute='statecode' operator='eq' value='0'/>
<condition attribute='address1_city' operator='not-null' />
<condition attribute='parentcustomerid' operator='not-null' />
</filter>
</entity>
</fetch>如果要用FetchXml来做的话，还可以考虑参考下面的代码做个Custom API, 传入FetchXml，返回EntityCollection类型的参数即可。
public EntityCollection GetDistinctValuesByFetchXml(string fetchXml)
{
EntityCollection returnVal = new EntityCollection();
Entity entity;
XDocument fetchXmlDoc = XDocument.Parse(fetchXml);
var attributesElements = fetchXmlDoc.Descendants("entity").FirstOrDefault().Descendants("attribute");
var attributeNames = attributesElements.Select(t => t.Attributes("name").FirstOrDefault().Value).ToArray();
var EC = RetrieveAllRecordsByFetchXml(fetchXml, AdminService);
foreach(var item in EC.Entities)
{
entity = new Entity();
foreach (string attrName in attributeNames)
{
entity[attrName] = GetFieldValueAsString(item, attrName);
}
returnVal.Entities.Add(entity);
}
return returnVal;
}
private static string GetFieldValueAsString(Entity entity, string fieldName)
{
string returnValue = string.Empty;
if (entity.Contains(fieldName))
{
if (fieldName.Contains("."))
{
if (entity.GetAttributeValue<AliasedValue>(fieldName).Value.GetType().ToString().Contains("EntityReference"))
{
returnValue = ((EntityReference)entity.GetAttributeValue<AliasedValue>(fieldName).Value).Id.ToString();
}
else if (entity.GetAttributeValue<AliasedValue>(fieldName).Value.GetType().ToString().Contains("OptionSetValue"))
{
returnValue = ((OptionSetValue)entity.GetAttributeValue<AliasedValue>(fieldName).Value).Value.ToString();
}
else
{
returnValue = entity.GetAttributeValue<AliasedValue>(fieldName).Value.ToString();
}
}
else
{
if (entity[fieldName].GetType().ToString().Contains("EntityReference"))
{
returnValue = entity.GetAttributeValue<EntityReference>(fieldName).Id.ToString();
}
else if (entity[fieldName].GetType().ToString().Contains("OptionSetValue"))
{
returnValue = entity.GetAttributeValue<OptionSetValue>(fieldName).Value.ToString();
}
else
{
returnValue = entity[fieldName].ToString();
}
}
}
return returnValue;
}
public static EntityCollection RetrieveAllRecordsByFetchXml(string fetchXml, IOrganizationService adminService, int recordsPerPage = 5000, bool isBypassCustomPluginExecution = false)
{
EntityCollection EC = new EntityCollection();
int pageNumber = 1;
string pagingCookie = null;
while (true)
{
string xml = CreateXml(fetchXml, pagingCookie, pageNumber, recordsPerPage);
RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest
{
Query = new FetchExpression(xml)
};
if (isBypassCustomPluginExecution)
{
fetchRequest1.Parameters.Add("BypassCustomPluginExecution", true);
}
EntityCollection returnCollection = ((RetrieveMultipleResponse)adminService.Execute(fetchRequest1)).EntityCollection;
if (returnCollection.Entities != null && returnCollection.Entities.Any())
{
EC.Entities.AddRange(returnCollection.Entities);
}
if (returnCollection.MoreRecords)
{
pageNumber++;
pagingCookie = returnCollection.PagingCookie;
}
else
{
break;
}
}
return EC;
}
public static string CreateXml(string xml, string cookie, int page, int count)
{
XDocument doc = XDocument.Parse(xml);
if (!string.IsNullOrEmpty(cookie))
{
doc.Root.Add(new XAttribute("paging-cookie", cookie));
}
doc.Root.Add(new XAttribute("page", page));
doc.Root.Add(new XAttribute("count", count));
return doc.ToString(SaveOptions.DisableFormatting);
}
