How can I filter on attributes from a linked entity, outside of LinkEntity?

Question

I'd like to make a complex query, I don't care about it being fetchXML or queryexpression, as long as it works :-)

I want to create either a query with a NOT IN(select from) structure, or its equivalent LEFT OUTER JOIN using a WHERE IS NULL.

The query is something like this:

SELECT t1.*
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.xid = t2.id
WHERE t2.id IS NULL

(query structure with an example: http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/)

As you can see, the tricky bit is "t2.id" in the where-clause. I have found no way to filter on this value using any tools or docs. Is it even possible?

Answer 1

This is one of the things that are not possible with CRM queries. There are (not necessarily workable) alternatives:

• Retrieve the relevant values from t2 and query t1 using ConditionOperator.NotIn (in case NotIn is actually supported; not all available ConditionOperator values can really be handled by the CrmService).
• Fully retrieve both tables and join them in memory (using LINQ or the like).
• Do the actual join via SQL (which of course requires direct database access), return just the IDs and retrieve the result via the CrmService.