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:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.