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:
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.