Use QueryExpression to Find User Roles and Teams

The following demonstration illustrates how to get the Roles and Teams of a specific Dynamics CRM user.

  1. In Visual Studio 2013, start a New Project and choose the Console Application In this demonstration, the project name will be CRMUserRolesTeams.
    2015-11-22_11h17_30
  2. In Solution Explorer, expand the References folder and add the following assemblies:
    1. Microsoft.Crm.Sdk.Proxy
    2. Microsoft.Xrm.Client
    3. Microsoft.Xrm.Sdk
    4. System.Runtime.Serialization
      2015-11-22_11h28_06
  3. In the code view for Program.cs replace the using statements with the following:
using System;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

2015-11-22_11h32_54

  1. Use the Simplified Connection to connect to your CRM instance. In this demonstration, the following code is for an on-premise instance of CRM.
    2015-11-22_12h17_44
  2. Create your QueryExpression to find a particular CRM user. In this demonstration, the QueryExpression returns the first and last name of a CRM user using their email address (internalemailaddress).  Other fields can be used like systemuserid or domainname.
QueryExpression systemUserQE = new QueryExpression
{
    EntityName = "systemuser",
    ColumnSet = new ColumnSet("firstname", "lastname"),
    Criteria =
    {
        Conditions = {
            new ConditionExpression {
                AttributeName = "internalemailaddress",
                Operator = ConditionOperator.Equal,
                Values = { "homer@simpson.com" }
            }
        }
    }
};

Alternatively, if you use a different field like systemuserid, the QueryExpression would look like this:

QueryExpression systemUserQE = new QueryExpression
{
    EntityName = "systemuser",
    ColumnSet = new ColumnSet("firstname", "lastname"),
    Criteria =
    {
        Conditions = {
            new ConditionExpression {
                AttributeName = "systemuserid",
                Operator = ConditionOperator.Equal,
                Values = { "999877FD-3B91-E511-80C9-005056302D52" }
            }
        }
    }
};

There are alternative methods of constructing the QueryExpression.  I prefer the above notation only because it’s easier to read and understand.  You can write the same QueryExpression in the method described on Microsoft’s SDK documentation:

QueryExpression suQE = new QueryExpression();
suQE.EntityName = "systemuser";
suQE.ColumnSet = new ColumnSet("firstname", "lastname");
 
ConditionExpression suCE = new ConditionExpression();
suCE.AttributeName = "systemuserid";
suCE.Operator = ConditionOperator.Equal;
suCE.Values.Add("999877FD-3B91-E511-80C9-005056302D52");
 
suQE.Criteria.AddCondition(suCE);

Since the systemuserid is a Guid data type, the ConditionExpression can be written like this:

ConditionExpression suCE = new ConditionExpression();
suCE.AttributeName = "systemuserid";
suCE.Operator = ConditionOperator.Equal;
suCE.Values.Add(new Guid("{999877FD-3B91-E511-80C9-005056302D52}"));

This is a screenshot of the CRM user account
2015-11-22_12h25_28

  1. Execute the QueryExpression and output the first and last name of the CRM user
EntityCollection systemUserEC = service.RetrieveMultiple(systemUserQE);
 
foreach (Entity suE in systemUserEC.Entities)
{
    Console.WriteLine(suE.Attributes["firstname"] + " " + suE.Attributes["lastname"]);
}

 

Build the solution and run the Console Application.  The output should look like this:

2015-11-22_12h55_04

  1. Use the following QueryExpression to query the CRM user’s roles.
QueryExpression rolesQE = new QueryExpression
{
    EntityName = "role",
    ColumnSet = new ColumnSet("name"),
    LinkEntities = {
        new LinkEntity
        {
            LinkFromEntityName = "role",
            LinkFromAttributeName = "roleid",
            LinkToEntityName = "systemuserroles",
            LinkToAttributeName = "roleid",
            LinkCriteria = new FilterExpression
            {
                FilterOperator = LogicalOperator.And,
                    Conditions =
                    {
                        new ConditionExpression
                        {
                            AttributeName = "systemuserid",
                            Operator = ConditionOperator.Equal,
                            Values = { suE.Attributes["systemuserid"] }
                        }
                    }
            }
        }
    }
};

The same QueryExpression can be written as such:

QueryExpression rQE = new QueryExpression();
rQE.EntityName = "role";
rQE.ColumnSet = new ColumnSet("name");
 
LinkEntity rLE = new LinkEntity();
rLE.LinkFromEntityName = "roleid";
rLE.LinkFromAttributeName = "roleid";
rLE.LinkToEntityName = "systemuserroles";
rLE.LinkToAttributeName = "roleid";
 
FilterExpression rFE = new FilterExpression();
rFE.FilterOperator = LogicalOperator.And;
 
ConditionExpression rCE = new ConditionExpression();
rCE.AttributeName = "systemuserid";
rCE.Operator = ConditionOperator.Equal;
rCE.Values.Add(suE.Attributes["systemuserid"]);
 
rFE.Conditions.Add(rCE);
rLE.LinkCriteria.AddFilter(rFE);
rQE.LinkEntities.Add(rLE);

 

  1. Execute the QueryExpression and output the number of roles and the name to the roles.
EntityCollection rolesEC = service.RetrieveMultiple(rolesQE);
 
Console.WriteLine("Roles: " + rolesEC.Entities.Count.ToString());
foreach (Entity rE in rolesEC.Entities)
{
    Console.WriteLine("- " + rE.Attributes["name"]);
}

Build the solution and run the Console Application.  The output should look like this:

2015-11-22_18h54_27

2015-11-22_18h55_13

  1. Use the following QueryExpression to query the CRM user’s teams.
QueryExpression teamsQE = new QueryExpression
{
    EntityName = "team",
    ColumnSet = new ColumnSet("name"),
    LinkEntities = {
        new LinkEntity
        {
            LinkFromEntityName = "team",
            LinkFromAttributeName = "teamid", 
            LinkToEntityName = "teammembership",
            LinkToAttributeName = "teamid",
            LinkCriteria = new FilterExpression
            {
                FilterOperator = LogicalOperator.And,
                    Conditions =
                    {
                        new ConditionExpression
                        {
                            AttributeName = "systemuserid",
                            Operator = ConditionOperator.Equal,
                            Values = { suE.Attributes["systemuserid"] }
                        }
                    }
            }
        }
    }
};

 

  1. Execute the QueryExpression and output the number of teams and the name to the teams.
EntityCollection teamsEC = service.RetrieveMultiple(teamsQE);
 
Console.WriteLine("Teams: " + teamsEC.Entities.Count.ToString());
foreach (Entity tE in teamsEC.Entities)
{
    Console.WriteLine("- " + tE.Attributes["name"]);
}

Build the solution and run the Console Application.  The output should look like this:
2015-11-22_19h37_45

2015-11-22_19h39_09

Leave a Reply

Your email address will not be published. Required fields are marked *