Support Portal ContactGet in touch

Dynamic CRM Query Expressions and Link Entities

   Words by CRM Consultancy

   on 08/04/2016 13:06:34

imageThis 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, parentcustomerid

FROM 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_postalcode

FROM Contact

JOIN Account A

ON parentcustomerid = A.accountid

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";

 

q.LinkEntities.Add(le);

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_postalcode

FROM Contact

JOIN Account A

ON parentcustomerid = A.accountid

WHERE

(address1_city = ‘London’ OR

address1_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);

Binding and Presenting the Data

Once we have our Query Expression, we can then present this data via a simple Portal Grid:

Mark-up

<ActivityDefinition DefaultSort="0" RowLength="15" SaveState="true" DefaultSortDirection="desc" ShowImage="false" FilterMode="true" sScrollY="0" FixedHeight="36" RowSelect="true" Export="false">

    <Columns>

        <crmcs:CWListDefinitionColumn DisplayName="First Name" Type="String" WidthType="Percent" Width="10"></crmcs:CWListDefinitionColumn>

        <crmcs:CWListDefinitionColumn DisplayName="Last Name" Type="String" WidthType="Percent" Width="20"></crmcs:CWListDefinitionColumn>

        <crmcs:CWListDefinitionColumn DisplayName="Jobtitle" Type="String" WidthType="Percent" Width="10" ShowFilter="true"></crmcs:CWListDefinitionColumn>

        <crmcs:CWListDefinitionColumn DisplayName="Company" Type="EntityReference" WidthType="Percent" Width="25" ShowFilter="true"></crmcs:CWListDefinitionColumn>

        <crmcs:CWListDefinitionColumn DisplayName="Company Address" Type="String" WidthType="Percent" Width="20" ShowFilter="true"></crmcs:CWListDefinitionColumn>

        <crmcs:CWListDefinitionColumn DisplayName="Company Postcode" Type="String" WidthType="Percent" Width="10" ShowFilter="true"></crmcs:CWListDefinitionColumn>

    </Columns>

    <ActivitySources>

        <crmcs:CWActivityReadfrom EntityName="contact" Enabled="true" OwnerField="ownerid" Name="Contacts" PrimaryKeyField="contactid">

            <columns>

                <crmcs:CWActivityReadfromColumn ActivityColumn="First Name" DatabaseName="firstname" Type="String" MaxCharacters="50" />

                <crmcs:CWActivityReadfromColumn ActivityColumn="Last Name" DatabaseName="lastname" Type="String" MaxCharacters="50" />

                <crmcs:CWActivityReadfromColumn ActivityColumn="Jobtitle" DatabaseName="jobtitle" Type="String" MaxCharacters="60" />

                <crmcs:CWActivityReadfromColumn ActivityColumn="Company" DatabaseName="parentcustomerid" Type="EntityReference" MaxCharacters="60" />

                <crmcs:CWActivityReadfromColumn ActivityColumn="Company Address" DatabaseName="A.address1_line1" Type="String" MaxCharacters="60" />

                <crmcs:CWActivityReadfromColumn ActivityColumn="Company Postcode" DatabaseName="A.address1_postalcode" Type="String" MaxCharacters="60" />

            </columns>

        </crmcs:CWActivityReadfrom>

    </ActivitySources>

</ActivityDefinition>

Code to Bind the Query to the Grid

contactList.ActivityDefinition.ActivitySource("Contacts").FixedQuery = q;

contactList.ActivityDefinition.ActivitySource("Contacts").FixedColumnSet = true;

contactList.Service = Portalbaseline.UserContext.Instance.CrmService;

contactList.DataBind();
User Experience
image

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.