How to programmatically get user with specific role in MS CRM business unit

Question

I'm trying to retrieve user with specific role from MS CRM 2011 business unit via C# plugin, however I stuck in composing right query for this. Roles linked to users via N:N relationship and I'm struggling to find example query for this case.

For now I came up with following:

var entity = organizationService.Retrieve(entityName, entityId, new ColumnSet(new string[] { "new_unit" }));

if (entity.Attributes.Keys.Contains("new_unit"))
{
    QueryExpression query = new QueryExpression("systemuser");

    query.ColumnSet = new ColumnSet(new string[] { "systemuserid" });
    query.Distinct = true;
    query.Criteria = new FilterExpression();
    query.Criteria.AddCondition("businessunitid", ConditionOperator.Equal, ((EntityReference)entity.Attributes["new_unit"]).Id);
}

I not sure to which entity I need to link systemuser and how, to achieve the goal retrieve user with specific role and business unit.

I can get easily the name of the role or it's Guid, but what I should do next with it?

Daryl Daryl · Accepted Answer · 2013-03-07T13:33:58

You have to use the AddLink methods to perform joins.

This should be what you need:

QueryExpression query = new QueryExpression("systemuser");

query.ColumnSet = new ColumnSet(new string[] { "systemuserid" });
query.Distinct = true;
query.Criteria = new FilterExpression();
query.Criteria.AddCondition("businessunitid", ConditionOperator.Equal, ((EntityReference)entity.Attributes["new_unit"]).Id);
query.AddLink("systemuserroles", "systemuserid", "systemuserid").
    AddLink("role","roleid", "roleid").
        LinkCriteria.AddCondition("name", ConditionOperator.Equal, "MyRoleName");

var users = organizationService.RetrieveMultiple(query);

And if you can easily get the RoleId, you can skip the Add link to the Role Entity, and just add your LinkCriteria Condition to the SystemUserRoles entity:

QueryExpression query = new QueryExpression("systemuser");

query.ColumnSet = new ColumnSet(new string[] { "systemuserid" });
query.Distinct = true;
query.Criteria = new FilterExpression();
query.Criteria.AddCondition("businessunitid", ConditionOperator.Equal, ((EntityReference)entity.Attributes["new_unit"]).Id);
query.AddLink("systemuserroles", "systemuserid", "systemuserid").
    LinkCriteria.AddCondition("roleid", ConditionOperator.Equal, roleIdGuid);

var users = organizationService.RetrieveMultiple(query);

How to programmatically get user with specific role in MS CRM business unit

1 Answers