How to build a query to get a security role where user X does not exists in Dynamics CRM 2016 C#

Question

I need to update the list of users for a security role. How can I avoid re-associating an user that already has the role assigned?

I'm trying to build a query to get the role where user x is not in the role's list of users and then use it for the association if the user was not found. Here is what I have so far:

LinkEntity userRoles = new LinkEntity
{
    LinkFromEntityName = "role",
    LinkToEntityName = "systemuserroles",
    LinkFromAttributeName = "roleid",
    LinkToAttributeName = "roleid",
    JoinOperator = JoinOperator.LeftOuter,
    Columns = new ColumnSet(true),
    EntityAlias = "userroles",
    LinkCriteria =
    {
        Conditions =
        {
            new ConditionExpression{
                //not sure how to build the "where user x does not exists"
           }
        }
    }
};

QueryExpression roleQuery = new QueryExpression
{
    EntityName = "role",
    ColumnSet = new ColumnSet(true),
    Criteria =
    {
        Conditions = { 
            new ConditionExpression { 
                AttributeName = "name", 
                Operator = ConditionOperator.Equal, 
                Values = { "RoleName"}
            } 
        }
    },
    LinkEntities = { userRoles }
};

I need to build something like this query:

select * from role 
where role.name = "RoleName"
and not exists
(select 1 from userRoles 
where userRoles.roleid = role.roleid
and userRoles.user = "xyz")

score 0 · Accepted Answer · answered Jan 03 '18 at 17:38

Based on this post that explained it using Fetch XML, I was able to build this query that gets the role as long as the user I specify is not there:

LinkEntity userRoles = new LinkEntity
{
    LinkFromEntityName = "role",
    LinkToEntityName = "systemuserroles",
    LinkFromAttributeName = "roleid",
    LinkToAttributeName = "roleid",
    JoinOperator = JoinOperator.LeftOuter,
    Columns = new ColumnSet(true),
    EntityAlias = "userroles",
    LinkCriteria =
    {
        Conditions =
         {                         
             new ConditionExpression {
                 AttributeName = "systemuserid",
                 Operator = ConditionOperator.Equal,
                 Values = { "xyz" }
             }
         }
    }
};

QueryExpression roleQuery = new QueryExpression
{
    EntityName = "role",
    ColumnSet = new ColumnSet(true),
    Criteria =
    {
        Filters = {
            new FilterExpression{
                FilterOperator = LogicalOperator.And,
                Conditions = {
                    new ConditionExpression{
                        EntityName = "systemuserroles",
                        AttributeName = "roleid",
                        Operator = ConditionOperator.Null
                    }
                }
            }                    
        },
        Conditions = { 
                    new ConditionExpression { 
                        AttributeName = "name", 
                        Operator = ConditionOperator.Equal, 
                        Values = { "RoleName"}
                    }
                }
    },
    LinkEntities = { userRoles }
};

How to build a query to get a security role where user X does not exists in Dynamics CRM 2016 C#

1 Answers1