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.
   
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
      
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;
   

   

4. Use the Simplified Connection to connect to your CRM instance. In this demonstration, the following code is for an on-premise instance of CRM.
   
5. 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
   

6. 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:
   

7. 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);
   

8. 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:
   

   

9. 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"] }
                           }
                       }
               }
           }
       }
   };
   

10. 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: