Dynamic CRM最常用的3种查询方式
在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);
通过字段值的方式，有些类似于QueryExpression使用属性赋值查询。
