How to fetch related CRM entity records using Link Entities in single call?

Problem Statement:

We have multiple related entities in CRM. We want to fetch data from those entities by performing join operations on their Lookup fields.

Solutions:

  1. Fetch records from all the required entities and use LINQ or Loops to perform joins by checking conditions on lookup fields.

Disadvantages:

  1. Makes multiple calls to CRM which impacts on performance of an application
  2. We fetch unnecessary data as we don’t perform join operation after all entities’ data is fetched which also impacts on performance of an application
  3. Use Link Entity class to apply join conditions on Lookup fields and fetch data from different entities in single call.

Advantages:

  1. Filters data before fetching the data to get only required records.
  2. Call to the CRM is made only once which improves performance.

LinkEntity Class:

  1. Use of LinkEntity class:

LinkEntity allows to connect multiple related entities in CRM to perform Join Operation. We can add as many LinkEntity objects in QueryExpression to perform various Join Operation and filter the data while fetching. For more information visit: http://msdn.microsoft.com/en-us/library/bb957073.aspx

  1. How to use LinkEntity class?

In the examples given below, I have used the constructors of LinkEntity class to perform Join Operations on multiple related entities.

For more information visit: http://msdn.microsoft.com/en-us/library/bb957073.aspx

Examples:

Note: In this article, I am going to explain only approach 2 i.e. how to use Link Entities to fetch data from related CRM entities.

Assumptions:

We have 5 CRM entities and are related as shown below.

Example 1: Fetch All Active Employee records and their companies (Simple Join)

  1. How to perform Join Operation?
FilterExpression employeeFilter = newFilterExpression(LogicalOperator.And);employeeFilter.AddCondition("statecode", ConditionOperator.Equal, 0);FilterExpression companyFilter = newFilterExpression(LogicalOperator.And);companyFilter.AddCondition("statecode", ConditionOperator.Equal, 0);QueryExpression expression = newQueryExpression()

{

EntityName = "new_employee",

ColumnSet = newColumnSet(newString[] { "employeename", "employeeid" }),

Criteria = employeeFilter

};

LinkEntity leCompany = newLinkEntity("new_employee", "new_company", "new_empcompanyid", "new_companyid", JoinOperator.LeftOuter);

leCompany.EntityAlias = "company";

leCompany.Columns.AddColumns("companyname", "companyaddress");

leCompany.LinkCriteria = companyFilter;

expression.LinkEntities.Add(leCompany);

EntityCollection results =  DAL.RetrieveMultiple(expression);
  1. How to read fields from each records?
String employeename, companyname, companyaddress;foreach (Entity record in results.Entities){employeename = record.Contains(“employeename”) ? Convert.ToString(record[“employeename”]) : “”;companyaddress = record.Contains(“company.companyaddress”) ? Convert.ToString(((AliasedValue)record[“company.companyaddress”]).Value) : “”;companyname = record.Contains(“company.companyname”) ? Convert.ToString(((AliasedValue)record[“company.companyname”]).Value) : “”;}
  1. Explanation:

In this example, I have simply performed Left Join on Company and Employee entity and fetched Employee and their associated companies in the result set. I have also shown that how to retrieve related entities’ fields from the result set.

Example 2: Fetch employees with their company data. Fetch only employees who are in ‘Accounts’ department (Multiple Join operations on same entity)

  1. How to perform Join Operation?
FilterExpression employeeFilter = newFilterExpression(LogicalOperator.And);employeeFilter.AddCondition(“statecode”, ConditionOperator.Equal, 0);FilterExpression companyFilter = newFilterExpression(LogicalOperator.And);companyFilter.AddCondition(“statecode”, ConditionOperator.Equal, 0);FilterExpression departmentFilter = newFilterExpression(LogicalOperator.And);

departmentFilter.AddCondition(“statecode”, ConditionOperator.Equal, 0);

departmentFilter.AddCondition(“new_departmentname”, ConditionOperator.Equal, “Accounts”);

QueryExpression expression = newQueryExpression()

{

EntityName = “new_employee”,

ColumnSet = newColumnSet(newString[] { “employeename”, “employeeid” }),

Criteria = employeeFilter

};

LinkEntity leCompany = newLinkEntity(“new_employee”, “new_company”, “new_empcompanyid”, “new_companyid”, JoinOperator.LeftOuter);

leCompany.EntityAlias = “company”;

leCompany.Columns.AddColumns(“companyname”, “companyaddress”);

leCompany.LinkCriteria = companyFilter;

LinkEntity leDepartment = newLinkEntity(“new_employee”, “new_department”, “new_departmentid”, “new_departmentid”, JoinOperator.Inner);

leDepartment.LinkCriteria = departmentFilter;

leDepartment.EntityAlias = “department”;

leDepartment.Columns.AddColumns(“departmentname”);

expression.LinkEntities.Add(leCompany);

expression.LinkEntities.Add(leDepartment);

EntityCollection results = DAL.RetrieveMultiple(expression);

  1. Explanation:

This is similar example as Example 1, but in this I have shown how to perform multiple join operations on the same entity. Here the join is performed between Company and Employee (Left Join) and Employee and Department (Inner Join). Also shown how to restrict data by applying filters in the related entities.

Example 3: Fetch employees, their company information and the branch of the company (Multiple Join Operations on different entities).

  1. How to perform Join Operation?
FilterExpression employeeFilter = newFilterExpression(LogicalOperator.And);employeeFilter.AddCondition(“statecode”, ConditionOperator.Equal, 0);FilterExpression companyFilter = newFilterExpression(LogicalOperator.And);companyFilter.AddCondition(“statecode”, ConditionOperator.Equal, 0);FilterExpression branchFilter = newFilterExpression(LogicalOperator.And);

branchFilter.AddCondition(“statecode”, ConditionOperator.Equal, 0);

QueryExpression expression = newQueryExpression()

{

EntityName = “new_employee”,

ColumnSet = newColumnSet(newString[] { “employeename”, “employeeid” }),

Criteria = employeeFilter

};

LinkEntity leCompany = newLinkEntity(“new_employee”, “new_company”, “new_empcompanyid”, “new_companyid”, JoinOperator.LeftOuter);

leCompany.EntityAlias = “company”;

leCompany.Columns.AddColumns(“companyname”, “companyaddress”);

leCompany.LinkCriteria = companyFilter;

LinkEntity leBranch = leCompany.AddLink(“new_branch”, “new_branchid”, “new_branchid”, JoinOperator.Inner);

leBranch.LinkCriteria = branchFilter;

leBranch.EntityAlias = “branch”;

leBranch.Columns.AddColumns(“branchname”);

expression.LinkEntities.Add(leCompany);

EntityCollection results = DAL.RetrieveMultiple(expression);

  1. How to read fields from each record?
String employeename, companyname, companyaddress, branchname;foreach (Entity record in results.Entities){employeename = record.Contains(“employeename”) ?Convert.ToString(record[“employeename”]) : “”;companyaddress = record.Contains(“company.companyaddress”) ? Convert.ToString(((AliasedValue)record[“company.companyaddress”]).Value) : “”;companyname = record.Contains(“company.companyname”) ? Convert.ToString(((AliasedValue)record[“company.companyname”]).Value) : “”;branchname = record.Contains(“branch.branchname”) ? Convert.ToString(((AliasedValue)record[“branch.branchname”]).Value) : “”;

}

  1. Explanation:

In this example, I have shown how to perform multiple joins on different entities. Here the join is performed between Employee and Company entities. Another join operation is performed between Company and Branch entities.

Advertisements

One thought on “How to fetch related CRM entity records using Link Entities in single call?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s