Retrive data from MS CRM by QueryExpression

Many time we needs to retrieve data from MS CRM, for that we can user power of MS CRM SDK with query expression.

Retrieve simple data with some conditions form a single entity

QueryExpression query = new QueryExpression(Entitylogicalname);
query.ColumnSet = new ColumnSet(true);

query.Criteria.AddCondition(“mobileno”, ConditionOperator.NotNull);

query.Criteria.AddCondition(“surname”, ConditionOperator.Equal, “Kant”);

EntityCollection queryData = CRMConnection._organizationService.RetrieveMultiple(query);

Add sorting into query

QueryExpression query = new QueryExpression(Entitylogicalname);
query.ColumnSet = new ColumnSet(true);

query.AddOrder(“createdon”, OrderType.Descending);

EntityCollection queryData = CRMConnection._organizationService.RetrieveMultiple(query);

To retrieve data from 1:N mappings
public static EntityCollection IsEntityExist(string Entitylogicalname, Dictionary<string, string> filter)
{
//create query
QueryExpression query = new QueryExpression(Entitylogicalname);
query.ColumnSet = new ColumnSet(true);

//add filter
FilterExpression condition = query.Criteria.AddFilter(LogicalOperator.And);
foreach (var item in filter)
{
condition = AddFilter(ref condition, item.Key, item.Value);
}
condition.AddCondition(“statecode”, ConditionOperator.Equal, 0);
// pass query to service proxy
return CRMConnection._organizationService.RetrieveMultiple(query);
}

public static FilterExpression AddFilter(ref FilterExpression condition, string attribute, string attributeValue)
{
condition.AddCondition(attribute, ConditionOperator.Equal, attributeValue);
return condition;
}

==================================================================================

To retrieve data from N:N mappings

For ex. we want all security roles of system user then..

/////////
private EntityCollection GetUserSecurityRoles(IOrganizationService service, Guid userID)
{
// Create a QueryExpression.
QueryExpression qe = new QueryExpression();
qe.EntityName = “role”;
qe.ColumnSet = new ColumnSet(true);

LinkEntity linkRole = new LinkEntity(“role”, “systemuserroles”, “roleid”, “roleid”, JoinOperator.Inner);
LinkEntity linkSysUserRoles = new LinkEntity(“systemuserroles”, “systemuser”, “systemuserid”, “systemuserid”, JoinOperator.Inner);

// The condition is to find the user ID.
ConditionExpression conditionForuser = new ConditionExpression(“systemuserid”, ConditionOperator.Equal, userID);

linkSysUserRoles.LinkCriteria = new FilterExpression();
linkSysUserRoles.LinkCriteria.AddCondition(conditionForuser);

linkRole.LinkEntities.Add(linkSysUserRoles);
qe.LinkEntities.Add(linkRole);

// Execute the query.
EntityCollection bec = service.RetrieveMultiple(qe);
return bec;

}

OR

///
/// get association data for the ID
///

/////////////////////
public EntityCollection GetEntityAssociateRecords(string firstEntityName, string secondEntityName, Guid firstEntityID, Guid secondEntityID, string intersectTableName, string firstEntityMatchID, string secondEntityMatchID)
{
// Create a QueryExpression.
QueryExpression queryEntityAssociates = new QueryExpression();
queryEntityAssociates.EntityName = secondEntityName;
queryEntityAssociates.ColumnSet = new ColumnSet(true);

// Set up the join between the second entity
// and the intersect table intersectTableName.
LinkEntity linkSecondEntity = new LinkEntity();
linkSecondEntity.LinkFromEntityName = secondEntityName;
linkSecondEntity.LinkFromAttributeName = secondEntityMatchID;
linkSecondEntity.LinkToEntityName = intersectTableName;
linkSecondEntity.LinkToAttributeName = secondEntityMatchID;

// Set up the join between the intersect table
// intersectTableName and the first entity.
LinkEntity linkFirstEntity = new LinkEntity();
linkFirstEntity.LinkFromEntityName = intersectTableName;
linkFirstEntity.LinkFromAttributeName = firstEntityMatchID;
linkFirstEntity.LinkToEntityName = firstEntityName;
linkFirstEntity.LinkToAttributeName = firstEntityMatchID;

//alternat
//LinkEntity linkEntity2 = new LinkEntity(secondEntityName, intersectTableName, secondEntityMatchID, secondEntityMatchID, JoinOperator.Inner);
//LinkEntity linkEntity1 = new LinkEntity(intersectTableName, firstEntityName, firstEntityMatchID, firstEntityMatchID, JoinOperator.Inner);

// The condition is to find the first entity ID.
ConditionExpression conditionForFirstEntity = new ConditionExpression(firstEntityMatchID, ConditionOperator.Equal, firstEntityID);

linkFirstEntity.LinkCriteria = new FilterExpression();
linkFirstEntity.LinkCriteria.AddCondition(conditionForFirstEntity);

// The condition is to find the second entity ID.
ConditionExpression conditionForSecondEntity = new ConditionExpression(secondEntityMatchID, ConditionOperator.Equal, secondEntityID);

linkSecondEntity.LinkEntities.Add(linkFirstEntity);
linkSecondEntity.LinkCriteria.AddCondition(conditionForSecondEntity);
queryEntityAssociates.LinkEntities.Add(linkSecondEntity);

// Execute the query.
return CRMConnection._organizationService.RetrieveMultiple(queryEntityAssociates);

}
============================================

Associate N:N

///
/// Associate entities for N:N relation
///

///////////////public bool AssignFirstEntityToSecondEntity(Guid firstEntityID, string firstEntityName, Guid secondEntityID, string secondEntityName, string relationName)
{
try
{
// Add the 2nd to the 1st entity
CRMConnection._organizationService.Associate(
firstEntityName,
firstEntityID,
new Relationship(relationName),
new EntityReferenceCollection() { new EntityReference(secondEntityName, secondEntityID) });
return true;
}
catch (Exception ex)
{
DGHelper.LogException(” Association issue, details: ” + ex.Message);
return false;
}
}

Advertisements

One thought on “Retrive data from MS CRM by QueryExpression

  1. Pingback: Linq Entity with filter | CRM Practice

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