{"id":116,"date":"2022-01-04T11:32:36","date_gmt":"2022-01-04T11:32:36","guid":{"rendered":"https:\/\/blog.citrus-lime.com\/crmc\/?p=116"},"modified":"2022-01-04T11:32:37","modified_gmt":"2022-01-04T11:32:37","slug":"dynamic-crm-query-expressions-and-link-entities","status":"publish","type":"post","link":"https:\/\/blog.citrus-lime.com\/crmc\/dynamic-crm-query-expressions-and-link-entities\/","title":{"rendered":"Dynamic CRM Query Expressions and Link Entities"},"content":{"rendered":"\n

This article is aimed at exploring how Link Entities to represent Database JOIN Operations when developing with Dynamics CRM \u2013 and how typical Database Operations can be replicated in CRM Query Development and the Advanced Find in CRM.<\/p>\n\n\n\n

\"image\"<\/figure>\n\n\n\n

Databases 101<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

As a simple example, this may look like:<\/p>\n\n\n\n

SELECT *<\/p>

FROM Contact<\/p><\/blockquote>\n\n\n\n

This Select Statement then informs two points:<\/p>\n\n\n\n

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

    Which results in returning all the Rows and all the Columns contained in our Contact Table.<\/p>\n\n\n\n

    However this is simply data from one Table, so what if we wanted to return data about another Table alongside each Contact \u2013 say to return the details of the Company that the Contacts works for?<\/p>\n\n\n\n

    Then we would need to include a JOIN in our SELECT Statement:<\/p>\n\n\n\n

    SELECT *<\/p>

    FROM Contact<\/p>

    JOIN Account<\/p><\/blockquote>\n\n\n\n

    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.<\/p>\n\n\n\n

    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:<\/p>\n\n\n\n

    SELECT *<\/p>

    FROM Contact<\/p>

    JOIN Account<\/p>

    ON Contact.parentcustomerid = Account.accountid<\/p><\/blockquote>\n\n\n\n

    This revised Select Statement then joins each Contact Row with a corresponding Account Row when the value of the \u2018parentcustomerid\u2019 column in Contact matches that of the \u2018accountid\u2019 column in Account \u2013 therefore using \u2018parentcustomerid\u2019 as our Foreign Key, and \u2018accountid\u2019 as a Primary Key.<\/p>\n\n\n\n

    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.<\/p>\n\n\n\n

    This can then be ALOT of different Columns, and so result in a slower performing query \u2013 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:<\/p>\n\n\n\n

    SELECT Contact.fullname, Contact.jobtitle, Account.name, Account.address1_line1, Account.address1_postalcode<\/p>

    FROM Contact<\/p>

    JOIN Account<\/p>

    ON Contact.parentcustomerid = Account.accountid<\/p><\/blockquote>\n\n\n\n

    Which then redefines our Select Statement to only return the \u2018fullname\u2019 and \u2018jobtitle\u2019 Columns from Contact, and the \u2018name\u2019, \u2018address1_line1\u2019 and \u2018address1_postalcode\u2019 columns from Account.<\/p>\n\n\n\n

    However our Select Statement can be made overly lengthy by the need to define \u2018Contact.\u2019 and \u2018Account.\u2019 to denote which Table we want to read each Column back from \u2013 this is where Aliasing<\/strong> our Tables can come in handy, giving each Table an abbreviated Name over the initial long-hand name.<\/p>\n\n\n\n

    SELECT C.fullname, C.jobtitle, A.name, A.address1_line1, A.address1_postalcode<\/p>

    FROM Contact C<\/strong><\/p>

    JOIN Account A<\/strong><\/p>

    ON C.parentcustomerid = A.accountid<\/p><\/blockquote>\n\n\n\n

    Which makes a much more readable version of our Select Statement.<\/p>\n\n\n\n

    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 \u2013 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:<\/p>\n\n\n\n

    SELECT C.fullname, C.jobtitle, A.name, A.address1_line1, A.address1_postalcode<\/p>

    FROM Contact C<\/strong><\/p>

    JOIN Account A<\/strong><\/p>

    ON C.parentcustomerid = A.accountid<\/p>

    WHERE A.address1_city = \u2018London\u2019<\/p><\/blockquote>\n\n\n\n

    This then restricts our Select Statement to returning Contact Rows that are joined to an Account Row where the \u2018address1_city\u2019 Column is equal to \u2018London\u2019 \u2013 and so only returns Contacts that are linked to a Company Address in London.<\/p>\n\n\n\n

    If we wanted, we could then extend this Where clause to find both Contacts based in London or working in London:<\/p>\n\n\n\n

    SELECT C.fullname, C.jobtitle, A.name, A.address1_line1, A.address1_postalcode<\/p>

    FROM Contact C<\/strong><\/p>

    JOIN Account A<\/strong><\/p>

    ON C.parentcustomerid = A.accountid<\/p>

    WHERE A.address1_city = \u2018London\u2019 OR C.address1_city = \u2018London\u2019<\/p><\/blockquote>\n\n\n\n

    So using columns in either Contact or Account for our query.<\/p>\n\n\n\n

    At which point, we have the main three points of our Select or Query defined:<\/p>\n\n\n\n

    • Which Tables \u2013 <\/strong>Contact and Account<\/li>
    • Which Columns \u2013 <\/strong>Fullname, Jobtitle, Company Address Line 1, Company Address Postcode<\/li>
    • Which Records \u2013 <\/strong>Only Contacts based in London, or Contacts working for Companies based in London<\/li><\/ul>\n\n\n\n

      Which would the be our basic Database Query \u2013 so how does this apply to Dynamics CRM?<\/p>\n\n\n\n

      Dynamics CRM \u2013 Query Expressions and the Advanced Find<\/h3>\n\n\n\n

      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.<\/p>\n\n\n\n

      In this, instead of supplying a SQL Statement to the Database to return our Rows of Data \u2013 we supply a Query Expression to the Webservice to return a Collection of Entity records.<\/p>\n\n\n\n

      So if we compare our earlier Database approach to how this Query Expression would be handled in C# Code:<\/p>\n\n\n\n

      SQL<\/strong><\/td>C# Code Behind<\/strong><\/td><\/tr>
      SELECT firstname, lastname, jobtitle, parentcustomeridFROM Contact<\/td>QueryExpression q = new QueryExpression(“contact”);q.ColumnSet = new ColumnSet(“firstname”, “lastname”, “jobtitle”, “parentcustomerid”);<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      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.<\/p>\n\n\n\n

      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.<\/p>\n\n\n\n

      SQL<\/strong><\/td>C# Code Behind<\/strong><\/td><\/tr>
      SELECT firstname, lastname, jobtitle, parentcustomerid, A.name, A.address1_line1, A.address1_postalcodeFROM ContactJOIN Account A<\/strong>ON parentcustomerid = A.accountid<\/td>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);<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      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 <\/strong>with a OR Logical Operator to denote that the Query should return a Row when either of the defined Conditions is true.<\/p>\n\n\n\n

      SQL<\/strong><\/td>C# Code Behind<\/strong><\/td><\/tr>
      SELECT firstname, lastname, jobtitle, parentcustomerid, A.name, A.address1_line1, A.address1_postalcodeFROM ContactJOIN Account A<\/strong>ON parentcustomerid = A.accountidWHERE(address1_city = \u2018London\u2019 ORaddress1_city = \u2018Manchester\u2019)<\/td>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);<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      Binding and Presenting the Data<\/h3>\n\n\n\n

      Once we have our Query Expression, we can then present this data via a simple Portal Grid:<\/p>\n\n\n\n

      Mark-up<\/strong><\/td><\/tr>
      <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><\/td><\/tr>
      Code to Bind the Query to the Grid<\/strong><\/td><\/tr>
      contactList.ActivityDefinition.ActivitySource(“Contacts”).FixedQuery = q;contactList.ActivityDefinition.ActivitySource(“Contacts”).FixedColumnSet = true;contactList.Service = Portalbaseline.UserContext.Instance.CrmService;contactList.DataBind();<\/td><\/tr>
      User Experience<\/strong><\/td><\/tr>
      \"image\"<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

      This article is aimed at exploring how Link Entities to represent Database JOIN Operations when developing with Dynamics CRM \u2013 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<\/p>\n","protected":false},"author":43,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_price":"","_stock":"","_tribe_ticket_header":"","_tribe_default_ticket_provider":"","_tribe_ticket_capacity":"0","_ticket_start_date":"","_ticket_end_date":"","_tribe_ticket_show_description":"","_tribe_ticket_show_not_going":false,"_tribe_ticket_use_global_stock":"","_tribe_ticket_global_stock_level":"","_global_stock_mode":"","_global_stock_cap":"","_tribe_rsvp_for_event":"","_tribe_ticket_going_count":"","_tribe_ticket_not_going_count":"","_tribe_tickets_list":"[]","_tribe_ticket_has_attendee_info_fields":false,"footnotes":""},"categories":[3],"tags":[],"class_list":{"0":"post-116","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-crm"},"featured_image_src":null,"author_info":{"display_name":"jadesmith","author_link":"https:\/\/blog.citrus-lime.com\/crmc\/author\/jadesmith\/"},"_links":{"self":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/comments?post=116"}],"version-history":[{"count":1,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/116\/revisions"}],"predecessor-version":[{"id":117,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/116\/revisions\/117"}],"wp:attachment":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media?parent=116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/categories?post=116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/tags?post=116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}