使用QueryExpression查询

QueryExpression查询需要先新建QueryExpression对象 笔记地址

新建QueryExpression对象

1
new QueryExpression();
1
var query = new QueryExpression("new_ord_shipment");

设置查询列

  1. QueryExpression.ColumnSet.AddColumns();//设置查询列
  2. QueryExpression.ColumnSet = new ColumnSet();//设置查询列
  3. QueryExpression.ColumnSet = new ColumnSet(true);//设置查询全部列
  4. QueryExpression.ColumnSet.AllColumns = true;//设置查询全部列
1
2
3
4
5
var query = new QueryExpression("new_ord_shipment");
query.ColumnSet.AddColumns("new_ord_shipmentid", "new_contract_id", "new_userprofile_id");
query.ColumnSet = new ColumnSet("new_apply_price", "new_ord_proprice_apply_id", "new_apply_qty");
query.ColumnSet = new ColumnSet(true);
query.ColumnSet.AllColumns = true;

设置查询条件

  • 添加AND查询
    QueryExpression.Criteria.AddCondition(attributeName, ConditionOperator conditionOperator, value);

  • 添加Or查询

    FilterExpression filterExpression= new FilterExpression(LogicalOperator.Or);

    filterExpression.AddCondition(“createdon”, ConditionOperator.Today);

    filterExpression.AddCondition(“createdon”, ConditionOperator.Yesterday);

    query.Criteria.AddFilter(filterExpression); 

1
2
3
4
5
6
7
8
9
10
11
12
13
var query = new QueryExpression("new_ord_shipment");
query.ColumnSet.AllColumns = true;
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition("new_srv_worker_id", ConditionOperator.NotNull);
query.Criteria.AddCondition("new_supervisiontime", ConditionOperator.Null);
query.Criteria.AddCondition("new_supervisiontime", ConditionOperator.OnOrBefore, DateTime.Now)
query.Criteria.AddCondition("new_type", ConditionOperator.In, 3,4,5,6,7);

//创建时间等于今天或昨天
FilterExpression fe1 = new FilterExpression(LogicalOperator.Or);
fe1.AddCondition("createdon", ConditionOperator.Today);
fe1.AddCondition("createdon", ConditionOperator.Yesterday);
query.Criteria.AddFilter(fe1);

连接查询

LinkEntity(string linkFromEntityName, string linkToEntityName, string linkFromAttributeName, string linkToAttributeName, JoinOperator joinOperator)

  1. linkFromEntityName:要左连的表,即写SQL时LEFT JOIN左边的表,

  2. linkToEntityName:要连接到的表

  3. linkFromAttributeName:linkFrom表中对应的字段值

  4. linkToAttributeName:linkTo表中对应的字段值

  • 如果为主键在微软CRM中一般为实体名+id,如new_srv_userprofile的主键字段为new_srv_userprofileid

  • EntityAlias为指定的别名 

1
2
3
4
5
6
7
8
9
10
11
//左连接
LinkEntity linkEntity1 = new LinkEntity("new_ord_shipment", "new_srv_userprofile", "new_userprofile_id", "new_srv_userprofileid", JoinOperator.LeftOuter);
linkEntity1.EntityAlias = "a";
linkEntity1.Columns = new ColumnSet("new_productmodel_id");
linkEntity1.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.LinkEntities.Add(linkEntity1);
//自然连接
LinkEntity linkEntity2 = new LinkEntity("new_ord_shipment", "businessunit", "new_office_id", "businessunitid", JoinOperator.Inner);
linkEntity2.EntityAlias = "b";
linkEntity2.Columns = new ColumnSet("parentbusinessunitid");
query.LinkEntities.Add(linkEntity2);

执行查询

RetrieveMultiple(QueryExpression)

Retrieve(string entityName, Guid id, ColumnSet columnSet)

  • QueryExpression条件查询 结果为实体列表
    EntityCollection entityCollection = OrganizationServiceAdmin.RetrieveMultiple(QueryExpression);//批量导入时使用

  • 直接查询实体 结果为实体

    Entity entity = OrganizationService.Retrieve(entityName, Id, ColumnSet ); 

1
2
3
4
5
6
7
8
9
10
11
12
13
//QueryExpression条件查询
var query = new QueryExpression("new_ord_shipment");
query.ColumnSet.AddColumns("new_ord_shipmentid", "new_contract_id", "new_userprofile_id");
query.Criteria.AddCondition("new_name", ConditionOperator.Equal, ********);
var ec = OrganizationServiceAdmin.RetrieveMultiple(query);
if (ec != null && ec.Entities != null && ec.Entities.Count > 0)
{
	var item = ec.Entities[0];
    
}

//直接查询实体
var entity = OrganizationService.Retrieve("new_ord_shipment", new Guid("0A8FEDFB-B934-E911-A12C-000C2921A033"), new ColumnSet(true));

获取查到的数据

Entity.GetAttributeValue(attributeName)

  • 获取主表字段
    var new_office_id=entity.GetAttributeValue(“new_office_id”);

  • 获取连接表字段

    连接的实体查询出来的是匿名字段(别名字段)需要用AliasedValue类型接收,接收之后的Value值为本来的类型,这个new_productmodel_id为lookup类型,所以它类型为EntityReference,或者这里也可以直接用GetAliasAttributeValue来接收查到的值

    var new_productmodel_id= entity.GetAttributeValue(“a.new_productmodel_id”).Value;

    var new_productmodel_id= entity.GetAliasAttributeValue(“a.new_productmodel_id”); 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
//QueryExpression条件查询
var query = new QueryExpression("new_ord_shipment");
query.ColumnSet.AddColumns("new_ord_shipmentid", "new_contract_id", "new_userprofile_id", "new_office_id", "new_ord_sales_id", "new_finalaccount_id", "new_selltype");
query.Criteria.AddCondition("new_name", ConditionOperator.Equal, ********););
//连接表1
LinkEntity linkEntity1 = new LinkEntity("new_ord_shipment", "new_srv_userprofile", "new_userprofile_id", "new_srv_userprofileid", JoinOperator.LeftOuter);
linkEntity1.EntityAlias = "a";
linkEntity1.Columns = new ColumnSet("new_productmodel_id");
linkEntity1.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.LinkEntities.Add(linkEntity1);
//连接表2
LinkEntity linkEntity2 = new LinkEntity("new_ord_shipment", "businessunit", "new_office_id", "businessunitid", JoinOperator.LeftOuter);
linkEntity2.EntityAlias = "b";
linkEntity2.Columns = new ColumnSet("parentbusinessunitid");
query.LinkEntities.Add(linkEntity2);
//连接表3
LinkEntity linkEntity3 = new LinkEntity("new_ord_shipment", "new_ord_sales", "new_ord_sales_id", "new_ord_salesid", JoinOperator.LeftOuter);
linkEntity3.EntityAlias = "c";
linkEntity3.Columns = new ColumnSet("new_tel", "new_dealers_id");
linkEntity3.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.LinkEntities.Add(linkEntity3);
//查询
var ec = OrganizationServiceAdmin.RetrieveMultiple(query);
var createEntity = new Entity("new_insurance_sell");//实体对象
//如果结果不为空
if (ec != null && ec.Entities != null && ec.Entities.Count > 0)
{
	var item = ec.Entities[0];
    //主实体的主键字段类型是Guid
    createEntity["new_ord_shipmentid"] = new EntityReference("new_ord_shipment",item.GetAttributeValue<Guid>("new_ord_shipmentid"));
    if (item.Contains("new_selltype"))
    {
        createEntity["new_saletype"] = item.GetAttributeValue<OptionSetValue>("new_selltype");
    }
    if (item.Contains("new_contract_id"))
    {
        createEntity["new_contract_id"] = item.GetAttributeValue<EntityReference>("new_contract_id");
    }
    // 连接的实体查询出来的是匿名字段(别名字段)需要用AliasedValue类型接收,接收之后的Value值为本来的类型,这个new_productmodel_id为lookup类型,所以它类型为EntityReference
    //或者这里也可以直接用GetAliasAttributeValue<EntityReference>来接收查到的值,
    //比如这里也可用item.GetAliasAttributeValue<EntityReference>("a.new_productmodel_id")
    if (item.Contains("a.new_productmodel_id"))
    {
        createEntity["new_productmodel_id"] = item.GetAttributeValue<AliasedValue>("a.new_productmodel_id").Value;
    }
    if (item.Contains("c.new_dealers_id"))
    {
        createEntity["new_discustomer"] = item.GetAliasAttributeValue<EntityReference>("c.new_dealers_id");
    }
}
OrganizationService.Create(createEntity);//执行创建实体


//直接查询实体
var entity = OrganizationService.Retrieve("new_ord_shipment", new Guid("0A8FEDFB-B934-E911-A12C-000C2921A033"), new ColumnSet(true));
var old_new_price = entity.GetAttributeValue<dynamic>("new_price");

使用实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
var query = new QueryExpression("new_ord_shipment");
query.ColumnSet.AddColumns("new_ord_shipmentid", "new_contract_id", "new_userprofile_id", "new_office_id", "new_ord_sales_id", "new_finalaccount_id", "new_selltype");
query.Criteria.AddCondition("new_name", ConditionOperator.Equal, new_ord_shipmentid);
//产品档案
LinkEntity linkEntity1 = new LinkEntity("new_ord_shipment", "new_srv_userprofile", "new_userprofile_id", "new_srv_userprofileid", JoinOperator.LeftOuter);
linkEntity1.EntityAlias = "a";
linkEntity1.Columns = new ColumnSet("new_productmodel_id");
linkEntity1.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.LinkEntities.Add(linkEntity1);
//销售大区
LinkEntity linkEntity2 = new LinkEntity("new_ord_shipment", "businessunit", "new_office_id", "businessunitid", JoinOperator.LeftOuter);
linkEntity2.EntityAlias = "b";
linkEntity2.Columns = new ColumnSet("parentbusinessunitid");
query.LinkEntities.Add(linkEntity2);
//销售员工资料
LinkEntity linkEntity3 = new LinkEntity("new_ord_shipment", "new_ord_sales", "new_ord_sales_id", "new_ord_salesid", JoinOperator.LeftOuter);
linkEntity3.EntityAlias = "c";
linkEntity3.Columns = new ColumnSet("new_tel", "new_dealers_id");
linkEntity3.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.LinkEntities.Add(linkEntity3);
var entityCollection = OrganizationServiceAdmin.RetrieveMultiple(query);

    var createEntity = new Entity("new_insurance_sell");
if (entityCollection != null && entityCollection.Entities != null && entityCollection.Entities.Count > 0)
{
    var item = entityCollection.Entities[0];
    //发货通知单
    createEntity["new_ord_shipmentid"] = new EntityReference("new_ord_shipment", item.GetAttributeValue<Guid>("new_ord_shipmentid"));
    //车辆VIN码
    if (item.Contains("new_userprofile_id"))
    {
        createEntity["new_userprofile_id"] = item.GetAttributeValue<EntityReference>("new_userprofile_id");
    }
    //合同申请单
    if (item.Contains("new_contract_id"))
    {
        createEntity["new_contract_id"] = item.GetAttributeValue<EntityReference>("new_contract_id");
    }
    //产品型号
    if (item.Contains("a.new_productmodel_id"))
    {
        createEntity["new_productmodel_id"] = item.GetAttributeValue<AliasedValue>("a.new_productmodel_id").Value;
    }
    //销售大区
    if (item.Contains("b.parentbusinessunitid"))
    {
        createEntity["new_sellregion"] = item.GetAttributeValue<AliasedValue>("b.parentbusinessunitid").Value;
    }
    //代表处 new_office_id
    if (item.Contains("new_office_id"))
    {
        createEntity["new_selloffice"] = item.GetAttributeValue<EntityReference>("new_office_id");
    }
    //销售员工
    if (item.Contains("new_ord_sales_id"))
    {
        createEntity["new_ord_sales_id"] = item.GetAttributeValue<EntityReference>("new_ord_sales_id");
    }
    //销售人员电话
    if (item.Contains("c.new_tel"))
    {
        createEntity["new_phonenumber"] = item.GetAttributeValue<AliasedValue>("c.new_tel").Value;
    }
    //经销商
    if (item.Contains("c.new_dealers_id"))
    {
        createEntity["new_discustomer"] = item.GetAliasAttributeValue<EntityReference>("c.new_dealers_id");
    }
    //最终客户
    if (item.Contains("new_finalaccount_id"))
    {
        createEntity["new_finalaccount_id"] = item.GetAttributeValue<EntityReference>("new_finalaccount_id");
    }
    //经销/直销
    if (item.Contains("new_selltype"))
    {
        createEntity["new_saletype"] = item.GetAttributeValue<OptionSetValue>("new_selltype");
    }
}
//所属组织
createEntity["new_organisation_id"] = new EntityReference("businessunit", new Guid("0A8FEDFB-B934-E911-A12C-000C2921A033"));
OrganizationService.Create(createEntity);