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.
- QueryExpression query = new QueryExpression(“EntityALogicalName”);
- query.ColumnSet = new ColumnSet(“column1”, “coumn2”);
- // Or retrieve All Columns
- //query.ColumnSet = new ColumnSet(true);
- LinkEntity EntityB = new LinkEntity(“EntityALogicalName”, “EntityBLogicalName”, “EntityALinkAttributeName”, “EntityBLinkAttributeName”, JoinOperator.Inner);
- EntityB.Columns = new ColumnSet(“column1”, “coumn2”);
- EntityB.EntityAlias = “EntityB”;
- // Can put condition like this to any Linked entity
- // EntityB.LinkCriteria.Conditions.Add(new ConditionExpression(“statuscode”, ConditionOperator.Equal, 1));
- query.LinkEntities.Add(EntityB);
- // Join Operator can be change if there is chance of Null values in the Lookup. Use Left Outer join
- LinkEntity EntityC = new LinkEntity(“EntityALogicalName”, “EntityCLogicalName”, “EntityALinkAttributeName”, “EntityCLinkAttributeName”, JoinOperator.Inner);
- EntityC.Columns = new ColumnSet(“column1”, “coumn2”);
- EntityC.Columns = new ColumnSet(“column1”, “coumn2”);
- EntityC.EntityAlias = “EntityC”;
- query.LinkEntities.Add(EntityC);
- query.Criteria.Conditions.Add(new ConditionExpression(“status”, ConditionOperator.Equal, 1));
- var result = service.RetrieveMultiple(query);
- foreach (var entity in result.Entities)
- {
- // Get the Columns from the Entity Obj Like this. Depands on type of the Column.
- string entityAColumn1 = entity.Contains(“column1”) ? entity[“column1”].ToString() : string.Empty;
- // Use Link Entity Alias with column name
- string entityBColumn1 = entity.Contains(“EntityB.column1”) ? (entity[“EntityB.column1”] as AliasedValue).Value.ToString() : string.Empty;
- string entityCColumn1 = entity.Contains(“EntityC.column1”) ? (entity[“EntityC.column1”] as AliasedValue).Value.ToString() : string.Empty;
- }
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
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