Thursday, 17 September 2015

How to retrieve data from multiple entities in CRM

I have an entity called "A" and it has two lookup fields to entities "B" and "C". Now I want to retrieve data from all the three entities using CRM SDK.


  1. QueryExpression query = new QueryExpression("EntityALogicalName");
  2. query.ColumnSet = new ColumnSet("column1", "coumn2");
  3. // Or retrieve All Columns
  4. //query.ColumnSet = new ColumnSet(true);
  5.  
  6. LinkEntity EntityB = new LinkEntity("EntityALogicalName", "EntityBLogicalName", "EntityALinkAttributeName", "EntityBLinkAttributeName", JoinOperator.Inner);
  7. EntityB.Columns = new ColumnSet("column1", "coumn2");
  8. EntityB.EntityAlias = "EntityB";
  9. // Can put condition like this to any Linked entity
  10. // EntityB.LinkCriteria.Conditions.Add(new ConditionExpression("statuscode", ConditionOperator.Equal, 1));
  11. query.LinkEntities.Add(EntityB);
  12.  
  13. // Join Operator can be change if there is chance of Null values in the Lookup. Use Left Outer join
  14. LinkEntity EntityC = new LinkEntity("EntityALogicalName", "EntityCLogicalName", "EntityALinkAttributeName", "EntityCLinkAttributeName", JoinOperator.Inner);
  15. EntityC.Columns = new ColumnSet("column1", "coumn2");
  16. EntityC.Columns = new ColumnSet("column1", "coumn2");
  17. EntityC.EntityAlias = "EntityC";
  18. query.LinkEntities.Add(EntityC);
  19.  
  20. query.Criteria.Conditions.Add(new ConditionExpression("status", ConditionOperator.Equal, 1));
  21.  
  22. var result = service.RetrieveMultiple(query);
  23.  
  24. foreach (var entity in result.Entities)
  25. {
  26. // Get the Columns from the Entity Obj Like this. Depands on type of the Column.
  27. string entityAColumn1 = entity.Contains("column1") ? entity["column1"].ToString() : string.Empty;
  28. // Use Link Entity Alias with column name
  29. string entityBColumn1 = entity.Contains("EntityB.column1") ? (entity["EntityB.column1"] as AliasedValue).Value.ToString() : string.Empty;
  30. string entityCColumn1 = entity.Contains("EntityC.column1") ? (entity["EntityC.column1"] as AliasedValue).Value.ToString() : string.Empty;
  31. }

 You can use Left Outer join if there is possibility that any lookup values can be Null. You can access the Link Attribute using LinkEntity Alias mention in code. I hope this will also work.

The below Url might give more information and syntax for linked entites 
http://congruentdynamics.blogspot.co.uk/2013/05/retrieve-linked-entity-data-using-query.html

https://lakshmanindian.wordpress.com/2012/11/30/retrieve-data-using-fetchxml-with-multiple-link-entities-in-crm-2011/

No comments:

Post a Comment