Dynamics 365——查询

LINQ 查询

var queryopportunityproduct = from c in organizationServiceContext.CreateQuery("lsh_opp_product")
                              join a in organizationServiceContext.CreateQuery("lsh_order")
                              on c["lsh_order_id"] equals a["lsh_orderid"]
                              where a["lsh_evolve"].Equals("100000001")
                              where c["lsh_products_type"].Equals("100000000")
                              select new
                              {
                                  opportunityproductname = c["lsh_name"],
                                  productName = c["lsh_productid"],
                                  orderName = a["lsh_name"]
                              };
foreach (var item in queryopportunityproduct)
{
    Console.WriteLine(item.opportunityproductname.ToString() + "-----" + item.orderName.ToString());
}

LINQ 查询需要引用Microsoft.Xrm.Sdk类库中的OrganizationServiceContext类。CreateQuery里放Entity的LogicalName就是数据库中的表。其余的编写与平常的LINQ没有区别，但有些LINQ的语法是有限制的，详情请参考dynamincs 365 CRM Linq晚期的用法和Linq的使用方法

FetchXml查询

string fetch = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
                  "<entity name='lsh_opp_product'>" +
                    "<attribute name='lsh_name' />" +
                    "<order attribute='lsh_name' descending='false' />" +
                    "<filter type='and'>" +
                      "<condition attribute='lsh_products_type' operator='eq' value='100000000' />" +
                    "</filter>" +
                    "<link-entity name='lsh_order' from='lsh_orderid' to='lsh_order_id' link-type='inner' alias='ac'>" +
                      "<attribute name='lsh_name' />" +
                      "<filter type='and'>" +
                        "<condition attribute='lsh_evolve' operator='eq' value='100000001' />" +
                      "</filter>" +
                    "</link-entity>" +
                  "</entity>" +
                "</fetch>";
FetchExpression fetchxml = new FetchExpression(fetch);
DataCollection<Entity> entities = server.RetrieveMultiple(fetchxml).Entities;
if (entities != null && entities.Count > 0)
{
    foreach (Entity item in entities)
    {
        Console.WriteLine(item["lsh_name"].ToString() +"-----"+ ((AliasedValue)item["ac.lsh_name"]).Value);
    }
}

FetchXml是在系统里配好条件直接拿来用的，在这不做过多解释。

QueryExpression 查询

QueryExpression query = new QueryExpression
{
    EntityName = "lsh_opp_product",
    ColumnSet = new ColumnSet("lsh_name", "lsh_productid"),
    Criteria = new FilterExpression
    {
        FilterOperator = LogicalOperator.And,
        Conditions ={
             new ConditionExpression{
                  AttributeName = "lsh_products_type",
                  Operator = ConditionOperator.Equal,
                  Values = {100000000}
             }
        }
    },
    LinkEntities = {
        new LinkEntity{
            LinkToEntityName ="lsh_order",
            LinkToAttributeName = "lsh_orderid",
            LinkFromEntityName = "lsh_opp_product",
            LinkFromAttributeName = "lsh_order_id",
            Columns = new ColumnSet("lsh_name"),
            LinkCriteria = new FilterExpression
                {
                    FilterOperator = LogicalOperator.And,
                    Conditions =
                    {
                        new ConditionExpression
                        {
                             AttributeName = "lsh_evolve",
                             Operator = ConditionOperator.Equal,
                             Values = {100000001 }
                        }
                    }
            }
        }
    }
};
EntityCollection entities = server.RetrieveMultiple(query);
foreach (var item in entities.Entities)
{
    Console.WriteLine(item["lsh_name"].ToString() + "-----" + ((AliasedValue)item["lsh_order1.lsh_name"]).Value);
}

在这里稍作解释，用到的属性太多

属性	详情
EntityName	主实体
ColumnSet	需要查询字段的系统架构名称
AttributeName	条件字段名称
Operator	比较符
Values	比较值
LinkEntities	链接实体
LinkToEntityName	链接实体架构名称
LinkToAttributeName	链接属性列
LinkFromEntityName	主实体名称
LinkFromAttributeName	主实体链接实体字段名称
LinkCriteria	链接表的条件

那种查询方式更佳

说完了这三种查询方式，那哪种方式性能更佳，编写更方便呢？上图片

第一种为LINQ 查询、第二种为FetchXml查询、第三种为QueryExpression 查询。数据方面我准备了5000+条。从性能上来看，QueryExpression 性能更佳，但是编写代码复杂，可以把QueryExpression 查询优化成一个方法，每次就不用写这么多代码啦! 不过有不写代码的方式（FetchXml查询），为何不直接用呢!!!