Dynamics 365——查询

• • LINQ 查询
  • FetchXml查询
  • QueryExpression 查询
  • 那种查询方式更佳

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的使用方法

string fetch = "" +
                  "" +
                    "" +
                    "" +
                    "" +
                      "" +
                    "" +
                    "" +
                      "" +
                      "" +
                        "" +
                      "" +
                    "" +
                  "" +
                "";
FetchExpression fetchxml = new FetchExpression(fetch);
DataCollection 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 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);
}

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

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

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