Dynamics QueryExpression-查找fieldA等于fieldB的实体记录 [英] Dynamics QueryExpression - Find entity records where fieldA equals fieldB

问题描述

我正在尝试更改Dynamics crm中的某些数据. 为此，我想编写一个插件来搜索具有特定条件的实体.

到目前为止，还不算特别.

我可以执行QueryExpression并搜索实体A的字段A"具有该值"(例如字符串)的地方

现在不同的是，我要测试字段A"等于字段B".

在T-SQL中 选择*其中table1.fielda = table1.fieldb

所以我想将一个实体记录的属性值匹配到同一实体记录的属性值.

我搜索并找到了一种可以进行联接的方法(可以在此处找到API参考: CRM 2011:类型为'System.InvalidOperationException的未处理异常' 发生.从范围"引用，但未定义

(from cl in context.CreateQuery<ContractDetail>()
join a in context.CreateQuery<Account>()
on cl.CustomerId.Id equals a.AccountId
where cl.StateCode.Value == 0
where cl.new_SupportedBy == a.Name
select cl).ToList();

引发错误 ConsoleApplication1.exe中发生未处理的'System.InvalidOperationException'类型的异常

更多信息可在该线程中找到 CRM 2011:类型为'System.InvalidOperationException的未处理异常' 发生.从范围"引用，但未定义

因此，最终似乎无法以美观的方式对其进行存档.

似乎我最终将要搜索要查看的所有实体记录，并根据在for-每个循环中确定的条件来查找记录. :(((

解决方案

在Dynamics CRM中，Linq查询基于QueryExpression构建，因此继承了其局限性.在QueryExpression中，过滤是使用ConditionExpression对象完成的. ConditionExpression始终具有两个或三个参数:

1. 属性名称(左侧)
2. 操作员
3. (可选)一个或多个文字值

不能通过属性名称来标识值.

因此，当无法通过联接获得理想的结果时，唯一的选择是查询比实际需要更多的记录，然后进行过滤.

在完全信任(仅在前提条件下)模式下执行的插件中，实际上可以直接访问CRM数据库并执行SQL查询，但这有点麻烦.

I'm trying to alter some data in dynamics crm. To achive that, I want to write a plugin that searches for entities having speacial criteria.

so far, not that special.

I could do a QueryExpression and search where "field A" of entits A has "that value" (String, for example)

What is different now is, that I want to test "field A" is equal to "field B".

In T-SQL it would be select * where table1.fielda = table1.fieldb

so I want to match an attribute value of one entity record to an attribute value of the same entity record.

I searched and found a way where you can do joins (The API reference could be found here: https://msdn.microsoft.com/en-us/library/dn481591.aspx) on tables in the QueryExpression but I'm not able to find out how to use it for my porpuse and if it hits my requirements or if it's the wrong way.

So, did someone do that?

Thx

----UPDATE----

What I tried so far: Based on the post provided By @Brendon Colburn I tried to build my self join LINQ Query.

Building the Query, unfortunately, had a limitation for what I was trying to achive:

You can't build querys where you want to compare a field to another.

Example Code from: CRM 2011: An unhandled exception of type 'System.InvalidOperationException' occurred. referenced from scope '', but it is not defined

(from cl in context.CreateQuery<ContractDetail>()
join a in context.CreateQuery<Account>()
on cl.CustomerId.Id equals a.AccountId
where cl.StateCode.Value == 0
where cl.new_SupportedBy == a.Name
select cl).ToList();

throws the error An unhandled exception of type 'System.InvalidOperationException' occurred in ConsoleApplication1.exe

More Information could be found in the thread CRM 2011: An unhandled exception of type 'System.InvalidOperationException' occurred. referenced from scope '', but it is not defined

So, in the end it doesn't seem to be possible to archive it the good looking way.

It seems I will be ending up to do a search over all entity records I want to look at and find the records based on conditions I determine in a for - each loop. :(((

解决方案

In Dynamics CRM Linq queries are built upon the QueryExpression and thus inherit its limitations. In the QueryExpression filtering is done using ConditionExpression objects. A ConditionExpression always has two or three parameters:

1. The attribute name (the left hand side)
2. An operator
3. (Optional) One or more literal values

A value cannot be identified by an attribute name.

So, when you cannot get the desired result with joins, your only option is to query more records than you actually need and filter them afterwards.

In plugins executing in full trust (On Premise only) mode it actually is possible to access the CRM database directly and execute SQL queries, but this is a bit of a hack.

这篇关于Dynamics QueryExpression-查找fieldA等于fieldB的实体记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！