Banner

Search Related Dynamics CRM Entities via API

Back to Blog Listing


Below is an example of how to search using QueryExpression on two related entities.

Background:

This example is from a project where there are account entities that hold information on companies. Each account also has the option to have one or many "domains". Domains are a custom entity that are merely a way of distinguishing accounts, e.g. if a single company has multiple offices then there would be several account entities but a single domain shared between them. For this example I am returning a list of companies that all share the same given domain.

Code:

Dim CompanyList As New List(Of CompanyDetail)
Dim util As New Utilities
Dim serProxy As OrganizationServiceProxy = util.SetupCRMWebService()
  
Using serProxy
serProxy.EnableProxyTypes()
Dim ser As IOrganizationService = CType(serProxy, IOrganizationService)
  
Dim query As New Query.QueryExpression(new_domain.EntityLogicalName)
query.ColumnSet = New Query.ColumnSet("new_domain", "new_domainid", "new_account")
  
query.LinkEntities.Add(New Query.LinkEntity(new_domain.EntityLogicalName, Account.EntityLogicalName, "new_account", "accountid", Global.Microsoft.Xrm.Sdk.Query.JoinOperator.Inner))
  
query.LinkEntities(0).Columns.AddColumns("new_activitycategory", "address1_line1", "address1_line2", "new_bulkmembership", "address1_country", _
"new_county", "name", "address1_postalcode", "new_regioncode", "address1_telephone1", "address1_line3", "accountid")
query.LinkEntities(0).EntityAlias = "acc"
  
query.Criteria.AddCondition("new_domain", Global.Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, Domain)
  
Dim resultList As EntityCollection = ser.RetrieveMultiple(query)
  
For Each r As Entity In resultList.Entities
  
Dim company As New CompanyDetail
With company
.Address1 = r("acc.address1_line1").value
.Address2 = r("acc.address1_line2").value
'.BuildingName = acc.new_buildingname
.CompanyGUID = r("acc.accountid").value.ToString
.CompanyName = r("acc.name").value
.Country = r("acc.address1_country").value
  
.Postcode = r("acc.address1_postalcode").value
.Telephone = r("acc.address1_telephone1").value
.Town = r("acc.address1_line3").value
End With
  
CompanyList.Add(company)
  
Next
  
End Using

Detail:

Your primary entity defined must be the one you wish to make the query against (ie in my case I want to search where the domain name value is of that given, therefore, the primary entity is new_domain)

The line "query.LinkEntities.Add(New Query.LinkEntity(new_domain.EntityLogicalName, Account.EntityLogicalName, "new_account", "accountid", Global.Microsoft.Xrm.Sdk.Query.JoinOperator.Inner))" is where you link your second entity.

Once linked if you need access to the linked entity's attributes then add an alias name ("query.LinkEntities(0).EntityAlias = "acc""). This will allow you to then later retrieve those attributes using the alias e.g. r("acc.address1_line1").value

Additions:

  1. In order to reference an option set from your linked entity with entity alias name you can use the following:

     

    If Not r("acc.new_county").value Is Nothing Then
    .County = r("acc.new_county").Value.value
    End If

     

    the "value" of r("acc.new_county") is an option set value, this is why if there has been an option selected for the option set then in order to return that value you must have the ".value.value"

  2. As with this case you will be searching using a key lookup rather than early bound entity classes if you add a column to the column set and then the entity has no value set for that column then you will get an error message indicating that the given key was not present in the dictionary. In order to avoid this check if the key is present before accessing it, i.e.

    If r.Attributes.ContainsKey("acc.new_buildingname") Then
    .BuildingName = r("acc.new_buildingname").value
    End If


11 Nov 2013

About the Author

James CTwo is our blogging alter ego for the Dynamics CRM and Web experts here at C2. We have a passion for anything CRM, Web Design or Social Media. We love writing about it! Find us on YouTube & Twitter.

comments powered by Disqus

Our White Papers

Discover the content that matters to you on business transformation. Explore how you can drive customer engagement, empower your team, optimise your businesses and revolutionise your products with the next digital revolution. For more information visit our white paper library.

Library

C2 Newsletter

Our newsletter is sent on a quarterly basis, offering detailed insights into all aspects of business transformation with Microsoft cloud technologies. We also promote upcoming events and special offers throughout the year. Sign up to get involved today.

Dynamics 365 Office 365 Power BI Silver Partner