在一个查询中加载关联实体 [英] Loading associated entities in one query

问题描述

我有以下有效的代码，但我相信它正在执行多次查找，只是为了获取与我的自定义项目实体相关联的帐户实体。

是否可以指定想要填充的关联实体而不必遍历初始结果集？

  public IList< new_project> GetAssociatedProjectsByPostcode（字符串邮政编码）
 {
 FilterExpression filter = new FilterExpression（）; 
 filter.FilterOperator = LogicalOperator.And; 
 filter.AddCondition（new ConditionExpression（ new_project_zippostalcode，ConditionOperator.Equal，postcode））; 
 
 QueryExpression query = new QueryExpression（）; 
 query.EntityName = new_project.EntityLogicalName; 
 query.ColumnSet = new ColumnSet（true）; 
 query.Criteria =过滤器; 
 
 OrganizationServiceCache serviceCache = new OrganizationServiceCache（MemoryCache.Default，base.CrmConnection）; 
 
使用（CachedOrganizationService服务=新CachedOrganizationService（CrmConnection，serviceCache））
使用（XrmServiceContext xrmServiceContext = new XrmServiceContext（service））
 {
 //运行查询返回列表
中的项目实体IList< new_project> projects = service.RetrieveMultiple（query）
 .Entities 
 .Select（item => item.ToEntity< new_project>（））
 .ToList< new_project>（）; 
 
 //定义我们要填充的关系
 Relationship accountRel = new Relationship（ new_account_new_project）; 
 
 //我们无法在跟踪已打开的情况下调用
的load属性。xrmServiceContext.MergeOption = MergeOption.NoTracking; 
 
 //浏览原始列表并获取我们的关联
 foreach（项目中的new_project np）
 xrmServiceContext.LoadProperty（np，accountRel）; 
 
退货项目； 
} 
} 
  

是否可以使用ServiceContext生成等效项？

 选择Project.Name，Account.Name 
来自项目
加入帐户ON Account.Id =项目.AccountId 
  

编辑：

按照下面的James所述使用链接实体后，我现在有了以下内容，它生成了一个实体集合，其中包含在一个查询中填充的项目和帐户，但是我无法将此平面数据集传输到分层对象结构中。

  public IList< new_project> GetAssociatedProjectsByPostcode（字符串邮政编码）
 {
 FilterExpression filter = new FilterExpression（）; 
 filter.FilterOperator = LogicalOperator.And; 
 filter.AddCondition（new ConditionExpression（ new_project_zippostalcode，ConditionOperator.BeginsWith，PostcodeUtility.RegionFromPostcode（postcode）））;; 
 
 QueryExpression query = new QueryExpression（）; 
 query.EntityName = new_project.EntityLogicalName; 
 query.ColumnSet = new ColumnSet（true）; 
 query.Criteria =过滤器; 
 
 query.LinkEntities.Add（new LinkEntity（new_project.EntityLogicalName，Account.EntityLogicalName， new_associatedaccountid， accountid，JoinOperator.Inner））; 
 query.LinkEntities [0] .Columns = new ColumnSet（true）; 
 query.LinkEntities [0] .EntityAlias = Account.EntityLogicalName; 
 
 OrganizationServiceCache serviceCache = new OrganizationServiceCache（MemoryCache.Default，base.CrmConnection）; 
 
使用（CachedOrganizationService服务=新的CachedOrganizationService（CrmConnection，serviceCache））
使用（XrmServiceContext xrmServiceContext =新的XrmServiceContext（service））
 {
 EntityCollection ec =服务。 RetrieveMultiple（query）; 
 
 // **************************************** ************************* 
 //实体集合现在已填充帐户，但它们
 //其他键值对
 //例如（String）（（（AliasedValue）ec [0] [ account.name]）。Value; 
 // ******************************************** ********************* 
 
 //将实体集合转换为我们的类结构
 IList< new_project> projects = ec.Entities 
 .Select（item => item.ToEntity< new_project>（））
 .ToList< new_project>（）; 
个退货项目； 
} 
} 
  

解决方案

是的，这应该很简单，您有两个选择。

1. FetchXml，这是xml语法，类似于tsql的方法，它没有所有功能，但是您可以使用 link-entity 进行联接，如下所示 MSDN 。




2. QueryExpression ，具有 LinkEntities 属性，可以像这样 MSDN 。




3. 您可以发出 RetrieveRequest ，填充 RelatedEntitiesQuery ，如此处所示。

---

编辑

所以实体集合看起来像返回我期望的结果（例如帐户和这些帐户的值）-我认为typeof（ec [0]）是实体吗？

所以它只是转换为早期绑定，这是错误的。

我曾经用过linq来进行crm很多，所以我开始猜测，但是从这个示例。

您可能只需要：

1. 在您的服务上启用ProxyType，显然，这是完整的早期绑定支持所必需的。


2. 将实体投射为您的早期绑定类型。

例如（来自示例）：

  _serviceProxy.EnableProxyTypes（）; 
 
已取回帐户=（Account）_serviceProxy.Retrieve（ account，_accountId，cols）; 
 
 namedAccount.Address1_PostalCode = 98052; 
  

检索只是返回一个Entity（EntityCollection.Entities的类型），并且强制转换似乎在这里起作用。 / p>

I have the following code which works but I believe it is performing multiple look ups just to get the "Account" entity that is associated with my custom "Project" entity.

Is it possible to specify which associated entities you would like populated without having to loop through the initial result set?

    public IList<new_project> GetAssociatedProjectsByPostcode(string postcode)
    {
        FilterExpression filter = new FilterExpression();
        filter.FilterOperator = LogicalOperator.And;
        filter.AddCondition(new ConditionExpression("new_project_zippostalcode", ConditionOperator.Equal, postcode));

        QueryExpression query = new QueryExpression();
        query.EntityName = new_project.EntityLogicalName;
        query.ColumnSet = new ColumnSet(true);
        query.Criteria = filter;

        OrganizationServiceCache serviceCache = new OrganizationServiceCache(MemoryCache.Default, base.CrmConnection);

        using (CachedOrganizationService service = new CachedOrganizationService(CrmConnection, serviceCache))
        using (XrmServiceContext xrmServiceContext = new XrmServiceContext(service))
        {
             //Run the query to return the project entities in a list
             IList<new_project> projects = service.RetrieveMultiple(query)
                                            .Entities
                                            .Select(item => item.ToEntity<new_project>())
                                            .ToList<new_project>();

             //Define the relationships we want populated
             Relationship accountRel = new Relationship("new_account_new_project");

             //We cannot call load property with tracking turned on 
             xrmServiceContext.MergeOption = MergeOption.NoTracking;

             //Loop through the original list and get our associations
             foreach (new_project np in projects)
                 xrmServiceContext.LoadProperty(np, accountRel);

             return projects;
        }            
    }

Is it possible to produce the equivalent with the ServiceContext?

Select Project.Name, Account.Name
From Project
Join Account ON Account.Id = Project.AccountId

EDIT :

After using the link entities as described by James below I now have the following which produces an entity collection with projects and accounts populated in one query but I cannot transfer this flat dataset into the hierarchical object structure.

        public IList<new_project> GetAssociatedProjectsByPostcode(string postcode)
    {
        FilterExpression filter = new FilterExpression();
        filter.FilterOperator = LogicalOperator.And;
        filter.AddCondition(new ConditionExpression("new_project_zippostalcode", ConditionOperator.BeginsWith, PostcodeUtility.RegionFromPostcode(postcode)));

        QueryExpression query = new QueryExpression();
        query.EntityName = new_project.EntityLogicalName;
        query.ColumnSet = new ColumnSet(true);
        query.Criteria = filter;

        query.LinkEntities.Add(new LinkEntity(new_project.EntityLogicalName, Account.EntityLogicalName, "new_associatedaccountid", "accountid", JoinOperator.Inner));
        query.LinkEntities[0].Columns = new ColumnSet(true);
        query.LinkEntities[0].EntityAlias = Account.EntityLogicalName;

        OrganizationServiceCache serviceCache = new OrganizationServiceCache(MemoryCache.Default, base.CrmConnection);

        using (CachedOrganizationService service = new CachedOrganizationService(CrmConnection, serviceCache))
        using (XrmServiceContext xrmServiceContext = new XrmServiceContext(service))
        {
            EntityCollection ec = service.RetrieveMultiple(query);

            //*****************************************************************
            //The entity collection is now populated with the accounts but they
            //are just additional key value pairs
            //e.g. (String)((AliasedValue)ec[0]["account.name"]).Value;
            //*****************************************************************

            //Turn the entity collection into our class structure
            IList<new_project> projects = ec.Entities
                                           .Select(item => item.ToEntity<new_project>())
                                           .ToList<new_project>();
            return projects;
        }
    }

解决方案

Yeah, this should be pretty straight forward, you have a couple of options.

1. FetchXml, this is an xml syntax, similar in approach to tsql, it doesn't have all the features but you can do joins with link-entity as shown here MSDN.

2. QueryExpression, has a LinkEntities property which can be used like this MSDN.

3. You could issue a RetrieveRequest, populating the RelatedEntitiesQuery as shown here.

---

Edit

So the entity collection looks like its returning the results I would expect (e.g. the accounts and values of those accounts) - I take it typeof(ec[0]) is Entity?

So its just the conversion to an early bound which is going wrong.

I havnt used linq to crm much, so I'm starting to guess, but from looking at this example.

You may just need to:

1. EnableProxyTypes on your service, apparently this is required for full early bound support.
2. Cast the entity to your early bound type.

For example (from the sample):

_serviceProxy.EnableProxyTypes();

Account retrievedAccount = (Account)_serviceProxy.Retrieve("account", _accountId, cols);

retrievedAccount.Address1_PostalCode = "98052";

Retrieve just returns an Entity (the type of EntityCollection.Entities) and casting seems to work here.

这篇关于在一个查询中加载关联实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！