Dynamics 365-五：Web服务

使用 FetchXML 构建查询

​ 若要在 D365中执行 FetchXML 查询，必须首先生成 XML 查询字符串。 创建查询字符串后，使用 IOrganizationService.RetrieveMultiple 方法执行查询字符串。

​ 注意：登录用户的特权影响返回的记录集； 将仅返回登录用户对其具有读取访问权限的记录；不要在查询中检索所有属性，否则会对性能造成负面影响（建议取用到的列）；

​ SQL转FetchXML工具

语法格式

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
   <!--相当于sql里面的 from 表名-->
   <entity name='实体名'> 
      <!--相当于sql里面的 select 字段名-->
      <attribute name='字段名' alias='别名'/> 
      <attribute name='字段名'/>
      <!--sql中联接查询-->
      <link-entity name='systemuser' from='主键' to='外键' link-type="inner">
         <!--相当于sql里面的where条件,and,or-->
         <filter type='and'>
            <condition attribute='字段名' operator='逻辑' value='值' /> 
          </filter> 
      </link-entity> 
   </entity> 
</fetch>

operator常用的值

• 算术运算

  eq (相等)，ne(不等)，lt(小于)，gt(大于)，le(小于或等于)，ge(大于或等于)

• 逻辑运算

  null（为空），not-null（不为空），in（包含），not-in（不包含），on-or-before（等于或早于这个日期），on-or-after（晚于或等于这个日期），等；查看更多

创建查询字符串

普通查询

FetchXML语句检索所有客户：

<fetch mapping='logical'> 
   <entity name='account'>
      <attribute name='accountid'/> 
      <attribute name='name'/> 
	</entity>
</fetch>

条件查询

FetchXML 语句检索负责用户的姓氏不是 Cannon 的

<fetch mapping='logical'>
   <entity name='account'> 
      <attribute name='accountid'/> 
      <attribute name='name'/> 
      <filter type='and'> 
         <condition attribute='name' operator='ne' value='Cannon' /> 
      </filter> 
   </entity> 
</fetch>

FetchXML 语句检索负责用户的姓氏不是 Cannon 的所有帐户：

<fetch mapping='logical'>
   <entity name='account'> 
      <attribute name='accountid'/> 
      <attribute name='name'/> 
      <link-entity name='systemuser' to='owninguser' link-type="inner"> 
         <filter type='and'> 
            <condition attribute='lastname' operator='ne' value='Cannon' /> 
          </filter> 
      </link-entity> 
   </entity> 
</fetch>

FetchXML 语句使用计数设置从查询返回的最大记录数：

<fetch mapping='logical' count='3'> 
   <entity name='account'>
      <attribute name='accountid'/> 
      <attribute name='name'/> 
	</entity>
</fetch>

执行查询

string fetch = @"
   <fetch mapping='logical'>
     <entity name='account'> 
        <attribute name='accountid'/> 
        <attribute name='name'/> 
        <link-entity name='systemuser' to='owninguser'> 
           <filter type='and'> 
              <condition attribute='lastname' operator='ne' value='Cannon' /> 
           </filter> 
        </link-entity> 
     </entity> 
   </fetch> "; 

EntityCollection result = _serviceProxy.RetrieveMultiple(new FetchExpression(fetch));
foreach (var c in result.Entities)   
{   
    System.Console.WriteLine(c.Attributes["name"]);   
}

查询结果：使用 RetrieveMultiple 方法执行 FetchXML查询时，返回值是包含查询结果的 EntityCollection， 然后可以循环访问实体集合

联表查询

两个表的FetchXML语句（内连接）

select a.aname,b.bname from tb_a a inner join tb_b b on a.id = b.aid

<fetch mapping="logical" version="1.0">
  <entity name="tb_a">
    <attribute name="aname" />
    <link-entity name="tb_b" from="aid" to="id" alias="b" link-type="inner">
      <attribute name="bname" />
    </link-entity>
  </entity>
</fetch>

两个表以上的FetchXML语句（内连接）

select a.aname,b.bname,c.cname from tb_a a 
inner join tb_b b on a.bid = b.id
inner join tb_c c on a.cid = c.id

<fetch mapping="logical" version="1.0">
  <entity name="tb_a">
    <attribute name="aname" />
    <link-entity name="tb_b" from="id" to="bid" alias="b" link-type="inner">
      <attribute name="bname" />
    </link-entity>
    <link-entity name="tb_c" from="id" to="cid" alias="c" link-type="inner">
      <attribute name="cname" />
    </link-entity>
  </entity>
</fetch>

select a.aname,b.bname,c.cname from tb_a a 
inner join tb_b b on a.id = b.aid
inner join tb_c c on a.id = c.aid

<fetch mapping="logical" version="1.0">
  <entity name="tb_a">
    <attribute name="aname" />
    <link-entity name="tb_b" from="aid" to="id" alias="b" link-type="inner">
      <attribute name="bname" />
    </link-entity>
    <link-entity name="tb_c" from="aid" to="id" alias="c" link-type="inner">
      <attribute name="cname" />
    </link-entity>
  </entity>
</fetch>

两个表的FetchXML语句（外连接）

select a.aname,b.bname from tb_a a left join tb_b b on a.id = b.aid

<fetch mapping="logical" version="1.0">
  <entity name="tb_a">
    <attribute name="aname" />
    <link-entity name="tb_b" from="aid" to="id" alias="b" link-type="outer">
      <attribute name="bname" />
    </link-entity>
  </entity>
</fetch>

两个表以上的FetchXML语句（外连接）

select a.aname,b.bname,c.cname from tb_a a 
left join tb_b b on a.id = b.aid 
left join tb_c c on a.id = c.aid

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
    <entity name="mcs_spmsuppliercontract" >
        <attribute name="mcs_sobes" />
        <filter type="and" >
            <condition attribute="statecode" operator="eq" value="0" />
        </filter>
        <link-entity name="mcs_spmsupplierinfo" from="mcs_spmsupplierinfoid" to="mcs_spmsupplierinfoid" link-type="outer" alias="op" >
            <filter>
                <condition attribute="mcs_name" operator="eq" value="0000005502" />
            </filter>
        </link-entity>
        <link-entity name="mcs_parts" from="mcs_partsid" to="mcs_partid" link-type="outer" alias="Parts" >
            <filter>
                <condition attribute="mcs_name" operator="eq" value="3506070-FS01" />
            </filter>
        </link-entity>
    </entity>
</fetch>

聚合函数查询

关于聚合：必看

​ 若要使用聚合，请在fetch 标签中将关键字 aggregate 设置为 true，然后指定有效的实体名称、属性名称和别名，同时必须指定要执行的聚合的类型。

​ 支持的聚合函数：sum，avg，min，max，count(*)，count（属性名称）

示例模板：

<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='entity_name'> 
      <attribute name='attribute_name' aggregate='count' alias='alias_name'/> 
   </entity> 
</fetch>

平均值

string fetch = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='user'> 
       <attribute name='age' alias='age_avg' aggregate='avg' />
    </entity> 
</fetch>";

EntityCollection data = _serviceProxy.RetrieveMultiple(new FetchExpression(fetch));

foreach (var c in data.Entities)
{
    decimal aggregate1 = decimal.Parse(((AliasedValue)c["age_avg"]).Value.ToString());
    System.Console.WriteLine("平均年龄：" + aggregate1);
}

计数

统计商品总数：相当于count(*)

string fetch = @" 
    <fetch distinct='false' mapping='logical' aggregate='true'> 
        <entity name='new_shop'> 
           <attribute name='new_shopname' alias='count' aggregate='count' />
        </entity> 
    </fetch>";
var collection = service.RetrieveMultiple(new FetchExpression(fetch));
var entity_list = collection.Entities;
foreach (var c in entity_list)
{
    int count = int.Parse(((AliasedValue)c["count"]).Value.ToString());
    Console.WriteLine($"总计：{count}件商品");
}

统计存在商品价格的商品总数：相当于count(new_shopprice)

string fetch = @" 
    <fetch distinct='false' mapping='logical' aggregate='true'> 
        <entity name='new_shop'> 
           <attribute name='new_shopprice' alias='count' aggregate='countcolumn' />
        </entity> 
    </fetch>";
var collection = service.RetrieveMultiple(new FetchExpression(fetch));
var entity_list = collection.Entities;
foreach (var c in entity_list)
{
    int count = int.Parse(((AliasedValue)c["count"]).Value.ToString());
    Console.WriteLine($"总计：{count}件商品");
}

最大值

计算数据的最大值时，不考虑空值，会使用零

string fetch = @" 
    <fetch distinct='false' mapping='logical' aggregate='true'> 
        <entity name='new_shop'> 
           <attribute name='new_shopprice' alias='max' aggregate='max' />
        </entity> 
    </fetch>";
var collection = service.RetrieveMultiple(new FetchExpression(fetch));
var entity_list = collection.Entities;
foreach (var c in entity_list)
{
    int max = int.Parse(((AliasedValue)c["max"]).Value.ToString());
    Console.WriteLine($"最高价格：{max}");
}

最小值

计算数据的最小值时，不考虑空值，会使用零

string fetch = @" 
    <fetch distinct='false' mapping='logical' aggregate='true'> 
        <entity name='new_shop'> 
           <attribute name='new_shopprice' alias='min' aggregate='min' />
        </entity> 
    </fetch>";
var collection = service.RetrieveMultiple(new FetchExpression(fetch));
var entity_list = collection.Entities;
foreach (var c in entity_list)
{
    int min = int.Parse(((AliasedValue)c["min"]).Value.ToString());
    Console.WriteLine($"最低价格：{min}");
}

总和

计算数据的总和，不考虑空值，会使用零

string fetch = @" 
    <fetch distinct='false' mapping='logical' aggregate='true'> 
        <entity name='new_shop'> 
           <attribute name='new_shopprice' alias='sum' aggregate='sum' />
        </entity> 
    </fetch>";
var collection = service.RetrieveMultiple(new FetchExpression(fetch));
var entity_list = collection.Entities;
foreach (var c in entity_list)
{
    int sum = int.Parse(((AliasedValue)c["sum"]).Value.ToString());
    Console.WriteLine($"汇总价格：{sum}");
}

综合示例

string fetch = @" 
    <fetch distinct='false' mapping='logical' aggregate='true'> 
        <entity name='new_shop'> 
           <attribute name='new_shopname' alias='count' aggregate='countcolumn' />
           <attribute name='new_shopprice' alias='max' aggregate='max' />
           <attribute name='new_shopprice' alias='min' aggregate='min' />
           <attribute name='new_shopprice' alias='sum' aggregate='sum' />
        </entity> 
    </fetch>";
var collection = service.RetrieveMultiple(new FetchExpression(fetch));
var entity_list = collection.Entities;
foreach (var c in entity_list)
{
    int count = int.Parse(((AliasedValue)c["count"]).Value.ToString());
    decimal max = decimal.Parse(((AliasedValue)c["max"]).Value.ToString());
    decimal min = decimal.Parse(((AliasedValue)c["min"]).Value.ToString());
    decimal sum = decimal.Parse(((AliasedValue)c["sum"]).Value.ToString());
    Console.WriteLine($"总计：{count}件商品,最高价格：{max}，最低价格：{min}，总价格：{sum}");
}

分组查询

说明：只允许有 aggregate 属性和 groupby 属性的列出现在 FetchXML 语句中

按链接的实体进行分组

string groupby2 = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='name' alias='opportunity_count' aggregate='countcolumn' /> 
       <link-entity name='systemuser' from='systemuserid' to='ownerid'>
           <attribute name='parentsystemuserid' alias='managerid' groupby='true' />
       </link-entity> 
    </entity> 
</fetch>";

EntityCollection groupby2_result = _serviceProxy.RetrieveMultiple(new FetchExpression(groupby2));

foreach (var c in groupby2_result.Entities)
{
      int? aggregate10a = (int?)((AliasedValue)c["opportunity_count"]).Value;
}

按日期分组

// 年
<attribute name='actdate' groupby='true' dategrouping='year' alias='year' />

// 季度
<attribute name='actdate' groupby='true' dategrouping='quarter' alias='quarter' />

// 月
<attribute name='actdate' groupby='true' dategrouping='month' alias='quarter' />

// 周
<attribute name='actdate' groupby='true' dategrouping='week' alias='quarter' />

// 日
<attribute name='actdate' groupby='true' dategrouping='day' alias='quarter' />

排序查询

string fetch = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='new_order'>
        <attribute name='new_ordername' alias='count' aggregate='count'/>
        <attribute name='new_ordername' alias='new_ordername' groupby='true'/>
        <order attribute='new_ordername' descending='false' />
    </entity>
</fetch>";