Dynamics QueryExpression - Find entity records where fieldA equals fieldB

Question

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

Henk van Boeijen · Accepted Answer · 2016-06-22 14:36:43Z

0

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:

The attribute name (the left hand side)
An operator
(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.

answered Jun 22, 2016 at 14:36

Henk van Boeijen

8,0146 gold badges36 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brendon Colburn · Accepted Answer · 2016-06-17 13:17:10Z

Honestly I just think you're doing something wrong. The example you provide from elsewhere in Stack Overflow isn't a self-join. Maybe what you are trying to do isn't one either. If you were to provide the query that isn't working that would be insightful. What you are stating as a limitation doesn't sound accurate to me.

I believe what you are describing is a Self Join? I think it might be easier if you accomplish the same thing with this LINQ query:

using (ServiceContext svcContext = new ServiceContext(_serviceProxy))
 {
  var query_join5 = from a in svcContext.AccountSet
                    join a2 in svcContext.AccountSet
                    on a.ParentAccountId.Id equals a2.AccountId

                    select new
                    {
                     account_name = a.Name,
                     account_city = a.Address1_City
                    };
  foreach (var c in query_join5)
  {
   System.Console.WriteLine(c.account_name + "  " + c.account_city);
  }
 }

Replacing the field and entity names, of course. Check the msdn for these operations here: https://msdn.microsoft.com/en-us/library/gg334593.aspx

Collectives™ on Stack Overflow

Dynamics QueryExpression - Find entity records where fieldA equals fieldB

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related