在crm系统中 接口或者插件中 通常都会有查询的校验 实际上单查询的话 不用crm提供的方式也可以 直接用sql的方式去查，一般涉及到很多表的联动查询比如报表查询时，还是用sql更方便 一、QueryExpression查询方式 1.花挎号实例化方式，可以做多表联动查询
1 private static Entity GetDeliveryOrderDetail(string deliveryOrderNo, string deliveryRowNo)
2 {
3 QueryExpression queryExp = new QueryExpression("foton_deliveryorderdetail")
4 {
5 ColumnSet = new ColumnSet("foton_deliveryorderdetailid"),
6 NoLock = true,
7 Criteria = new FilterExpression(LogicalOperator.And)
8 {
9 Conditions =
10 {
11 new ConditionExpression("foton_rowno", ConditionOperator.Equal, deliveryRowNo)
12 }
13 },
14 LinkEntities =
15 {
16 new LinkEntity("foton_deliveryorderdetail", "foton_deliveryorder", "foton_deliveryorderid", "foton_deliveryorderid", JoinOperator.Inner)
17 {
18 LinkCriteria = new FilterExpression(LogicalOperator.And)
19 {
20 Conditions =
21 {
22 new ConditionExpression("foton_no", ConditionOperator.Equal, deliveryOrderNo)，
23 new ConditionExpression("crm_orderlinestatus",ConditionOperator.NotEqual,171060003),
24 }
25 }
26 }
27 }
28 };
29
30 Entity deliveryOrderDetail = null;
31 try
32 {
33 deliveryOrderDetail = OrgServiceUtil.Client.RetrieveMultiple(queryExp).Entities.FirstOrDefault();
34 }
35 catch (Exception e)
36 {
37 return null;
38 }
39 return deliveryOrderDetail;
40 }
2.属性赋值方式，看起来更整洁一些，个人比较常用此方式
1 private Entity checkAccountAmountIsExist(IOrganizationService service,Entity entity)
2 {
3 QueryExpression query = new QueryExpression(AccountAmount.new_amount);
4 query.Criteria.AddCondition("new_account_amount", ConditionOperator.Equal, entity.GetAttributeValue<EntityReference>("new_account_amount").Id);
5 query.Criteria.AddCondition("new_type", ConditionOperator.Equal, entity.GetAttributeValue<OptionSetValue>("new_type").Value);
6 query.ColumnSet = new ColumnSet(true);
7 query.NoLock = true;
8 Entity checkEntity = service.RetrieveMultiple(query)?.Entities.FirstOrDefault();
9 return checkEntity;
10 }
项目实战拓展：QueryExpression多选项集条件查询与多条件组合查询方式：
1 public List<Entity> GetOldFundsAccountByBusinessId(string businessEntityName, Guid businessId)
2 {
3 QueryExpression query = new QueryExpression(FundsAccount.new_funds_account);
4 switch (businessEntityName)
5 {
6 case FundsAccount.new_order:
7 query.Criteria.AddCondition(FundsAccount.new_order_field, ConditionOperator.Equal, businessId);
8 break;
9 case FundsAccount.new_salesorder:
10 query.Criteria.AddCondition(FundsAccount.new_partssalesorder_field, ConditionOperator.Equal, businessId);
11 break;
12 default:
13 break;
14 }
15 int[] typeArray = { FundsAccountType.BALANCE.GetHashCode(), FundsAccountType.BEGINBALANCE.GetHashCode(), FundsAccountType.LC.GetHashCode(), FundsAccountType.LINECREDIT_REMOVE_OUT.GetHashCode(), FundsAccountType.LINECREDIT_REMOVE_INNER.GetHashCode() };
16 ConditionExpression condition = new ConditionExpression("new_type", ConditionOperator.In, typeArray);
17 FilterExpression filter = new FilterExpression();
18 filter.AddCondition(condition);
19 query.ColumnSet = new ColumnSet(true);
20 query.NoLock = true;
21 query.Criteria.AddFilter(filter);
22 return crmOrg.RetrieveMultiple(query)?.Entities.ToList();
23 }
二、FetchXML查询方式
FetchXML查询方式个人使用最多的场景是多表联动查询
1.简单单表查询
1 private static Entity GetPurchasingInfoRecord(string infnr,Guid otdmaterialId, Guid providerId)
2 {
3 Entity entity = null;
4 string getOtdmaterialByCodeFetchXML = @"
5 <fetch mapping='logical'>
6 <entity name='foton_otdpurchasinginforecord'>
7 <all-attributes />
8 <filter type='and'>
9 <condition attribute='foton_infnr' operator='eq' value='{0}' />
10 <condition attribute='foton_otdmaterialid' operator='eq' value='{1}' />
11 <condition attribute='foton_providerid' operator='eq' value='{2}' />
12 </filter>
13 </entity>
14 </fetch>";
15 string fetchXml = string.Format(getOtdmaterialByCodeFetchXML, infnr, otdmaterialId.ToString().Replace("{","").Replace("}", ""), providerId.ToString().Replace("{", "").Replace("}", ""));
16 EntityCollection entities = ExecFetchXML(fetchXml);
17 if (entities.Entities.Count > 0)
18 {
19 entity = entities[0];
20 }
21 return entity;
22 }
23
24 /// <summary>
25 /// 根据FetchXML查询对应的Entity列表
26 /// </summary>
27 /// <param name="fetchXml">fetchxml查询语句</param>
28 /// <create>ading</create>
29 /// <returns>结果集</returns>
30 public static EntityCollection ExecFetchXML(string fetchXml)
31 {
32 try
33 {
34 return crmOrg.RetrieveMultiple(new FetchExpression(fetchXml));
35 }
36 catch (Exception ex)
37 {
38 if (ex is MessageSecurityException)
39 {
40 crmOrg = OrgServiceUtil.Client;
41 return crmOrg.RetrieveMultiple(new FetchExpression(fetchXml));
42 }
43 throw ex;
44 }
45 }
2.多表查询
（简单的插叙你语句可以通过在线sql语句转换成fetchxml的工具转化， 也可以通过相关的实体窗体右上方导航按钮中有个高级查询 自定义一个查询视图然后导出fetchxml，较为复杂的语句建议自己写） 1 /// <summary>
2 /// 税率查询FetchXML
3 /// </summary>
4 private string RateQueryFetchXML = @"
5 <fetch mapping='logical'>
6 <entity name = 'new_product_fourth'>
7 <link-entity name='new_return_analysis_detail' from='new_product_fourthid' to='new_product_fourthid' alias='analydetail' link-type='inner'>
8 <filter>
9 <condition attribute = 'new_return_analysis_detailid' operator='eq' value='{0}' />
10 </filter>
11 </link-entity>
12 <link-entity name='new_brand' from='new_brandid' to='new_brandid' link-type='outer' alias='brand'>
13 <attribute name='new_taxrate' />
14 </link-entity>
15 </entity>
16 </fetch>";
17 要取出连接表中的new_taxrate字段的值 需要注意要用AliasedValue先承接连接表的查询结果
18 private decimal GetRateByDetail(IOrganizationService service, Entity detailEntity)
19 {
20 decimal rate = 0.00m;
21 Entity rateEntity = service.RetrieveMultiple(new FetchExpression(string.Format(RateQueryFetchXML, detailEntity.Id)))?.Entities.FirstOrDefault();
22 AliasedValue rateAliasedValue = rateEntity.GetAttributeValue<AliasedValue>("brand.new_taxrate");
23 rate = rateEntity == null ? 0.00m : (rateAliasedValue == null ? 0.00m: Convert.ToDecimal(rateAliasedValue.Value));
24 return rate;
25 }
26 brand.new_taxrate取值，实际上也可以通过设置连接表要查的字段一个别名的方式，rateEntity.GetAttributeValue<AliasedValue>("taxrate");：
27 <attribute name='new_taxrate' alias='taxrate' />
拓展：
1）FetchXML中的关联表查询时，过滤条件为link表的字段是，既可以写在link表里，也可以放到最外层统一过滤 下面为js中拼接的fetchXML语句 1 function getInvoiceDetailByOrderIdFetchXML(value) {
2 var fetchXML = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
3 + '<entity name="new_relation">'
4 + '<all-attributes />'
5 + '<link-entity name="crm_vehicle" from="crm_vehicleid" to="crm_vin" link-type="outer" />'
6 + '<link-entity name="crm_ordermanage" from="crm_ordermanageid" to="crm_ordermanage" link-type="outer" />'
7 + '<filter type="and">'
8 + '<condition entityname="crm_ordermanage" attribute="crm_ordermanageid" value="' + value + '" operator="eq" />'
9 + '<condition entityname="crm_vehicle" attribute="crm_invoiceapplicationdetail" operator="not-null"/>'
10 + '<condition attribute="crm_orderlinestatus" value="171060003" operator="ne" />'
11 + '</filter>'
12 + '</entity >'
13 + '</fetch >';
14 return fetchXML;
15 }
三、QueryByAttribute方式：
QueryByAttribute queryo = new QueryByAttribute("opportunityproduct");
queryo.AddAttributeValue("foton_otddemandorerid", opportunityid.Id);
queryo.ColumnSet = new ColumnSet(true);
//queryinvoiceaccount.AddAttributeValue("statecode", 0);//属性名
EntityCollection ecinvoiceo = service.RetrieveMultiple(queryo);
标签：return,string,Dynamic,查询,query,new,Entity,CRM From： https://www.cnblogs.com/adingfirstlove/p/16598133.html