Dynamic CRM Query Expressions and Link Entities

This article is aimed at exploring how Link Entities to represent Database JOIN Operations when developing with Dynamics CRM – and how typical Database Operations can be replicated in CRM Query Development and the Advanced Find in CRM.

Databases 101

If we were retrieving a block of data from a Database, we would first write a SELECT Statement that would define what data we wanted to Retrieve.

As a simple example, this may look like:

SELECT *
FROM Contact

This Select Statement then informs two points:

Which Tables – We want to read Rows of Data from the Contact Table or View
Which Columns – We should retrieve all Columns, as denoted by the * character
Which Records – There is no Filter, and all rows of data in the Contact Table will be returned

Which results in returning all the Rows and all the Columns contained in our Contact Table.

However this is simply data from one Table, so what if we wanted to return data about another Table alongside each Contact – say to return the details of the Company that the Contacts works for?

Then we would need to include a JOIN in our SELECT Statement:

SELECT *
FROM Contact
JOIN Account

This asks our Database to return rows from the Contact Table joined with rows from the Account Table – however this would struggle as we have not specified the details for how each Contact Row would be matched to a corresponding Row in the Account Table.

To correct this, we would need to specify the Foreign Key in the Contact Table that would join with a Primary Key in the Account Table to inform the Select Statement on how to join a Contact Row and Account Row together:

SELECT *
FROM Contact
JOIN Account
ON Contact.parentcustomerid = Account.accountid

This revised Select Statement then joins each Contact Row with a corresponding Account Row when the value of the ‘parentcustomerid’ column in Contact matches that of the ‘accountid’ column in Account – therefore using ‘parentcustomerid’ as our Foreign Key, and ‘accountid’ as a Primary Key.

At this point, our Select Statement will return all the Rows of Contact, and all the Columns from both the Contact Table and the Account Table.

This can then be ALOT of different Columns, and so result in a slower performing query – which is where our * character performs a hindrance! Ideally we should replace this * with the actual list of Columns from Contact and from Account that we want our Select Statement to return:

SELECT Contact.fullname, Contact.jobtitle, Account.name, Account.address1_line1, Account.address1_postalcode
FROM Contact
JOIN Account
ON Contact.parentcustomerid = Account.accountid

Which then redefines our Select Statement to only return the ‘fullname’ and ‘jobtitle’ Columns from Contact, and the ‘name’, ‘address1_line1’ and ‘address1_postalcode’ columns from Account.

However our Select Statement can be made overly lengthy by the need to define ‘Contact.’ and ‘Account.’ to denote which Table we want to read each Column back from – this is where Aliasing our Tables can come in handy, giving each Table an abbreviated Name over the initial long-hand name.

SELECT C.fullname, C.jobtitle, A.name, A.address1_line1, A.address1_postalcode
FROM Contact C
JOIN Account A
ON C.parentcustomerid = A.accountid

Which makes a much more readable version of our Select Statement.

The other element is that our Contact Table may contain hundreds or thousands of rows of data for different Contacts, and our Select Statement may not need all this quantity of data bringing back from the Database – which is where we would add a WHERE clause into our Select Statement to control the volume of Rows being brought back from the DB:

SELECT C.fullname, C.jobtitle, A.name, A.address1_line1, A.address1_postalcode
FROM Contact C
JOIN Account A
ON C.parentcustomerid = A.accountid
WHERE A.address1_city = ‘London’

This then restricts our Select Statement to returning Contact Rows that are joined to an Account Row where the ‘address1_city’ Column is equal to ‘London’ – and so only returns Contacts that are linked to a Company Address in London.

If we wanted, we could then extend this Where clause to find both Contacts based in London or working in London:

SELECT C.fullname, C.jobtitle, A.name, A.address1_line1, A.address1_postalcode
FROM Contact C
JOIN Account A
ON C.parentcustomerid = A.accountid
WHERE A.address1_city = ‘London’ OR C.address1_city = ‘London’

So using columns in either Contact or Account for our query.

At which point, we have the main three points of our Select or Query defined:

Which Tables – Contact and Account
Which Columns – Fullname, Jobtitle, Company Address Line 1, Company Address Postcode
Which Records – Only Contacts based in London, or Contacts working for Companies based in London

Which would the be our basic Database Query – so how does this apply to Dynamics CRM?

Dynamics CRM – Query Expressions and the Advanced Find

Whilst Dynamics CRM uses the SQL Database Platform as the Data Layer of the Application, we rarely talk directly to SQL and instead work through the main CRM Webservice API for our Select or Query Statements.

In this, instead of supplying a SQL Statement to the Database to return our Rows of Data – we supply a Query Expression to the Webservice to return a Collection of Entity records.

So if we compare our earlier Database approach to how this Query Expression would be handled in C# Code:

SQL	C# Code Behind
SELECT firstname, lastname, jobtitle, parentcustomeridFROM Contact	QueryExpression q = new QueryExpression(“contact”);q.ColumnSet = new ColumnSet(“firstname”, “lastname”, “jobtitle”, “parentcustomerid”);

This gives us the basic Select Statement as a Query that can be sent to the CRM Webservice to retrieve the multiple Contact records we are looking for.

We can then expand upon this to look at how a JOIN operator would then map to CRM via the Link Entity element of the Query Expression.

SQL

C# Code Behind

SELECT firstname, lastname, jobtitle, parentcustomerid, A.name, A.address1_line1, A.address1_postalcodeFROM ContactJOIN Account AON parentcustomerid = A.accountid

We can then look at the Where Filter element of this query between SQL and the CRM API, and review how a OR Clause can be implemented via a FilterExpression with a OR Logical Operator to denote that the Query should return a Row when either of the defined Conditions is true.

SQL

C# Code Behind

SELECT firstname, lastname, jobtitle, parentcustomerid, A.name, A.address1_line1, A.address1_postalcodeFROM ContactJOIN Account AON parentcustomerid = A.accountidWHERE(address1_city = ‘London’ ORaddress1_city = ‘Manchester’)

QueryExpression q = new QueryExpression(“contact”);q.ColumnSet = new ColumnSet(“firstname”, “lastname”, “jobtitle”, “parentcustomerid”); LinkEntity le = new LinkEntity(“contact”, “account”, “parentcustomerid”, “accountid”, JoinOperator.Inner);le.Columns = new ColumnSet(“name”, “address1_line1”, “address1_postalcode”);le.EntityAlias = “A”; FilterExpression f = new FilterExpression(LogicalOperator.Or);f.AddCondition(“address1_city”, ConditionOperator.Equal, “London”);f.AddCondition(“address1_city”, ConditionOperator.Equal, “Manchester”); q.Criteria.AddFilter(f);q.LinkEntities.Add(le);