Thursday, May 9, 2013

Retrieve Linked Entity Data using Query Expression using MS CRM 2011 SDK


Most often you need to retrieve data from primary and related entity. This data can be retrieved using CRM SDK either with FetchXML or Query Expression.  In this blog, QueryExpression is used to retrieve primary and related entity data with a single database retrieve.
Below is the syntax of how related entity columns are retrieved.

Query Syntax:-

Parent Entity:-
var query = new QueryExpression(Entityname);
var columnNames = new[] { Columns to be Added };
query.ColumnSet = new ColumnSet(columnNames);

Linked Entity:-
var colsAccount = new[] { LinkEntity Columns to be Added }; // Related Entity Fields
LinkEntity linkentity = new LinkEntity() {
LinkFromEntityName = EntityName,
LinkFromAttributeName = EntityFieldName,
LinkToEntityName = LinkEntityName,
LinkToAttributeName = LinkEntityFieldName,
JoinOperator = JoinOperator.Inner,                        
Columns = new ColumnSet(colsAccount),
EntityAlias = AliasName
                        };
query.LinkEntities.Add(linkentity);
This sample shows how to retrieve related entities data using the RetrieveMultiple method using QueryExpression. The code returns columns from the primary entity Account as well as the firstname and lastname from associated Contact record.
Parent Entity:-

 var query = new QueryExpression("account");


 var columnNames = new[] { "fullname", "accountid" };


 query.ColumnSet = new ColumnSet(columnNames);


 query.Criteria.AddCondition("accountid", ConditionOperator.Equal, new Guid(<<ENTER GUID HERE>>));
Linked Entity:-

var colsAccount = new[] { "firstname", "lastname" };


                        LinkEntity linkEntityAccount = new LinkEntity()

                        {


                            LinkFromEntityName = "account",


                            LinkFromAttributeName = "primarycontact",


                            LinkToEntityName = "contact",


                            LinkToAttributeName = "contactid",


                            JoinOperator = JoinOperator.Inner,


                            Columns = new ColumnSet(colsAccount),


                            EntityAlias = "Contacts"


                        };


 query.LinkEntities.Add(linkEntityAccount);     
// Execute Query using RetrieveMultiple
//The RetrieveMultipleRequest is for returning multiple instances of a particular type of entity.
EntityCollection _results = _serviceproxy.RetrieveMultiple(query);
        if (_results != null)
             {
                foreach (var ent in _results.Entities)
               {
     // Display “First Name” along with Alias
     Console.WriteLine((ent.Attributes["Contacts.firstname"] as AliasedValue).Value);
     // Display “Last Name” along with Alias
     Console.WriteLine((ent.Attributes["Contacts.lastname"] as AliasedValue).Value);
                           
}
                        }
 
Hope, this is helpful!

Thanks!






No comments:

Post a Comment