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使用屬性指派查詢。
![Dynamics CRM安裝教程六：CRM産品安裝[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)