c# - Dynamics CRM QueryExpression 嵌套的 LinkedEntity
问题描述
我正在尝试从Contact
实体中检索数据,将其加入Function
实体,然后将其加入Function
实体Account
。
这个想法是使用单个查询来检索联系人数据以及其他两个中的一些额外字段。
我能够从Function
链接实体加入和检索数据,但我无法理解如何使用嵌套链接实体。
QueryExpression queryExpression = new QueryExpression(Contact.EntityLogicalName);
queryExpression.ColumnSet = new ColumnSet(Contact.Fields.pix_GZGId, Contact.Fields.Id, Contact.Fields.FirstName, Contact.Fields.LastName,
Contact.Fields.MiddleName, Contact.Fields.GenderCode, Contact.Fields.EMailAddress1, Contact.Fields.EMailAddress2,
Contact.Fields.Telephone2, Contact.Fields.MobilePhone, Contact.Fields.pix_Gastlessenoptin);
FilterExpression emailFilter = new FilterExpression(LogicalOperator.Or);
emailFilter.AddCondition(Contact.Fields.EMailAddress1, ConditionOperator.Equal, email);
emailFilter.AddCondition(Contact.Fields.EMailAddress2, ConditionOperator.Equal, email);
FilterExpression nameFilterAll = new FilterExpression(LogicalOperator.And);
nameFilterAll.AddCondition(Contact.Fields.FirstName, ConditionOperator.Equal, firstname);
nameFilterAll.AddCondition(Contact.Fields.LastName, ConditionOperator.Equal, lastname);
if (!String.IsNullOrEmpty(middlename))
{
nameFilterAll.AddCondition(Contact.Fields.MiddleName, ConditionOperator.Equal, middlename);
}
FilterExpression nameFilterPartial = new FilterExpression(LogicalOperator.And);
nameFilterPartial.AddCondition(Contact.Fields.FirstName, ConditionOperator.BeginsWith, firstname.Substring(0,1));
nameFilterPartial.AddCondition(Contact.Fields.LastName, ConditionOperator.Equal, lastname);
FilterExpression nameFilterLast = new FilterExpression();
nameFilterLast.AddCondition(Contact.Fields.LastName, ConditionOperator.Equal, lastname);
FilterExpression completeNameFilter = new FilterExpression(LogicalOperator.Or);
completeNameFilter.Filters.Add(nameFilterAll);
completeNameFilter.Filters.Add(nameFilterPartial);
completeNameFilter.Filters.Add(nameFilterLast);
queryExpression.Criteria = new FilterExpression(LogicalOperator.And);
queryExpression.Criteria.Filters.Add(emailFilter);
queryExpression.Criteria.Filters.Add(completeNameFilter);
queryExpression.AddOrder(Contact.Fields.LastName, OrderType.Descending);
//this is the problem part - it doesn't seem to do anything.
LinkEntity linkedFunctions = new LinkEntity(Contact.EntityLogicalName, pix_functie.EntityLogicalName,
Contact.Fields.Id, pix_functie.Fields.pix_Persoon, JoinOperator.LeftOuter);
linkedFunctions.Columns = new ColumnSet(pix_functie.Fields.Id, pix_functie.Fields.pix_Organisatie,
pix_functie.Fields.pix_isgzgfunction, pix_functie.Fields.pix_omschrijving, pix_functie.Fields.CreatedOn);
linkedFunctions.EntityAlias = ConfigKeys.PixFunctieAlias;
FilterExpression accountFilterExpression = new FilterExpression();
//accountFilterExpression.Conditions.Add(new ConditionExpression(pix_functie.Fields.pix_isgzgfunction, ConditionOperator.Equal, true));
linkedFunctions.AddLink(Account.EntityLogicalName, pix_functie.Fields.pix_Organisatie, Account.Fields.Id, JoinOperator.LeftOuter);
linkedFunctions.LinkEntities[0].LinkCriteria = accountFilterExpression;
linkedFunctions.LinkEntities[0].EntityAlias = "linkedAccountAlias";
queryExpression.LinkEntities.Add(linkedFunctions);
request.Query = queryExpression;
RetrieveMultipleResponse response = (RetrieveMultipleResponse)_context.Execute(request);
这让我从Function
链接实体中获取数据没有问题,然后我对其进行分组、排序和处理:
IEnumerable<Microsoft.Xrm.Sdk.Entity> entities = response.EntityCollection.Entities
.OrderByDescending(x => (x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_isgzgfunction) != null ? (bool?)
x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_isgzgfunction).Value : new Nullable<bool>()).HasValue)
.ThenByDescending(x => (x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_isgzgfunction) != null ? (bool?)
x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_isgzgfunction).Value : null))
.ThenByDescending(x => (x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.CreatedOn) != null ? (DateTime?)
x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.CreatedOn).Value : new Nullable<DateTime>()).HasValue)
.ThenByDescending(x => (x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.CreatedOn) != null ? (DateTime?)
x.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.CreatedOn).Value : null));
List<TtNugGzgDataContract.Entities.Contact> lvl1Contacts = new List<TtNugGzgDataContract.Entities.Contact>();
List<TtNugGzgDataContract.Entities.Contact> lvl2Contacts = new List<TtNugGzgDataContract.Entities.Contact>();
foreach(var entity in entities)
{
Contact contact = entity.ToEntity<Contact>();
TtNugGzgDataContract.Entities.Contact contactToAdd = new TtNugGzgDataContract.Entities.Contact()
{
externalID = contact.Id,
firstname = contact.FirstName,
lastname = contact.LastName,
insertion = contact.MiddleName,
gender = Converter.GetApiGender(contact.GenderCodeEnum),
phone = !String.IsNullOrEmpty(contact.Telephone2) ? contact.Telephone2 : contact.MobilePhone,
newsletter = contact.pix_GastlessenoptinEnum == pix_nieuwsbriefoptin.Toestaan
};
contactToAdd.entityID = entity.GetAttributeValue<AliasedValue>("linkedAccountAlias" + "." + Account.Fields.pix_GZGId) != null ?
(string)entity.GetAttributeValue<AliasedValue>("linkedAccountAlias" + "." + Account.Fields.pix_GZGId).Value : null;
contactToAdd.function = entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_omschrijving) != null ?
(string)entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_omschrijving).Value : null;
contactToAdd.isGzg = entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_isgzgfunction) != null ?
(bool?)entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_isgzgfunction).Value : null;
contactToAdd.createdOn = entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.CreatedOn) != null ?
(DateTime?)entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.CreatedOn).Value : null;
EntityReference reference = entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_Organisatie) != null ?
(EntityReference)entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_Organisatie).Value : null;
contactToAdd.accId = (entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_Organisatie) != null ?
(EntityReference)entity.GetAttributeValue<AliasedValue>(ConfigKeys.PixFunctieAlias + "." + pix_functie.Fields.pix_Organisatie).Value : new EntityReference()).Id;
if (!String.IsNullOrEmpty(contact.EMailAddress1) && contact.EMailAddress1.Equals(email, StringComparison.InvariantCultureIgnoreCase))
{
contactToAdd.email = contact.EMailAddress1;
contactToAdd.isSecondaryEmail = false;
lvl1Contacts.Add(contactToAdd);
} else
{
contactToAdd.email = contact.EMailAddress2;
contactToAdd.isSecondaryEmail = true;
lvl2Contacts.Add(contactToAdd);
}
}
但我无法摆脱entityID
嵌套的链接实体。AFAIK 别名是正确的,我什至尝试在它前面加上第一级链接实体的别名,但没有效果。
谁能指出我正确的方向?
解决方案
我建议不要手动滚动您的 QueryExpression,而是使用一个工具来为您完成。
- 下载并安装 XrmToolBox ( http://www.xrmtoolbox.com )。
- 从插件商店(在工具菜单上)下载 FetchXml Builder。
- 在图形界面中使用 FetchXml 构建您的查询。
- 在“视图”菜单上,选择“QueryExpression”。
- 右侧会弹出一个窗口,显示为 FetchXml 查询生成的 QueryExpression。
希望有帮助!
推荐阅读
- php - php - 检查条件中的命名空间
- ms-access - 筛选日期以搜索 PLC 项目
- groovy - 如何使用 Groovy 更改单个测试套件中的两个端点?
- python - 需要帮助使用 BeautifulSoup/requests 解析漫画图像
- javascript - 如何合并具有不同颜色的网格并且只有一个绘制调用?
- microsoft-graph-api - Azure Graph API - 无法使用各种 $Filter 语句检索 SSPR 数据
- python - 有没有办法将自定义别名添加到 pipenv shell 中?
- excel - Vlookup表将工作表复制到其他工作簿vba
- javascript - 调整浏览器大小时我的导航消失了
- temenos-quantum - Kony:从 android 设备访问主机名