Grab list of users exists in a relation in dynamics crm

Question

I've many to many relationship in dynamics crm form Order to System users and From following snippet I am trying to grab the list of users that exits in this relationship. But this throw the exception that Additional information: 'SystemUser' entity doesn't contain attribute with Name = 'salesorderid'..

Any idea, what I am missing in it?

private void grabRelatedUsers(IOrganizationService service){
 QueryExpression sysUsersQuery = new QueryExpression()
            {
                EntityName = "systemuser",
                ColumnSet = new ColumnSet("systemuserid"),
                LinkEntities = {
                    new LinkEntity()  {
                    LinkFromEntityName = "systemuser",
                    LinkToEntityName = "new_salesorder_systemuser",
                    LinkFromAttributeName = "systemuserid",
                    LinkToAttributeName = "systemuserid",
                    JoinOperator = JoinOperator.Inner
                    },
                    new LinkEntity() {
                        LinkFromEntityName = "new_salesorder_systemuser",
                        LinkToEntityName = "salesorder",
                        LinkFromAttributeName = "salesorderid",
                        LinkToAttributeName = "salesorderid",
                        JoinOperator = JoinOperator.Inner,
                        LinkCriteria = new FilterExpression() {
                            Conditions = {
                                new ConditionExpression("salesorderid",ConditionOperator.Equal,orderId)
                            }
                        }
                    },
                    new LinkEntity().AddLink("systemuser","systemuserid","systemuserid")
                    },

            };

            EntityCollection subGridUsers = service.RetrieveMultiple(sysUsersQuery);
}

Pawel Gradecki Pawel Gradecki · Accepted Answer · 2017-03-16T23:51:20

You didn't nest the Linked entities properly. Have a look at fetchXML that is generated for your scenario:

<fetch version="1.0" output-format="xml-platform" mapping="logical">
  <entity name="systemuser">
    <attribute name="systemuserid" />
    <link-entity name="new_salesorder_systemuser" from="systemuserid" to="systemuserid" visible="false" intersect="true">
      <link-entity name="salesorder" from="salesorderid" to="salesorderid" alias="ab">
        <filter type="and">
          <condition attribute="salesorderid" operator="eq" value="XXXXXXXXXXXXXXX" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

As you can see the linked entities must be nesting and also, mecause that's out of the box N:N relationship, you cannot link salesorder from new_salesorder_systemuser, but from systemuser. So the QueryExpression should look like that:

QueryExpression sysUsersQuery = new QueryExpression()
{
    EntityName = "systemuser",
    ColumnSet = new ColumnSet("systemuserid"),
    LinkEntities = {
        new LinkEntity
        {
            LinkFromEntityName = "systemuser",
            LinkToEntityName = "new_salesorder_systemuser",
            LinkFromAttributeName = "systemuserid",
            LinkToAttributeName = "systemuserid",
            JoinOperator = JoinOperator.Inner,
            LinkEntities =
            {
                new LinkEntity()
                {
                    EntityAlias = "ab",
                    LinkFromEntityName = "systemuser",
                    LinkToEntityName = "salesorder",
                    LinkFromAttributeName = "salesorderid",
                    LinkToAttributeName = "salesorderid",
                    JoinOperator = JoinOperator.Inner,
                    LinkCriteria = new FilterExpression()
                    {
                        Conditions = {
                            new ConditionExpression("salesorderid",ConditionOperator.Equal, "sfsdf")
                        }
                    }
                }
            }
        }
    }
};

If you have problem generating QueryExpressions, simply do the query using Advanced Find, download fetchXml and convert it to QueryExpression using SDK FetchXmlToQueryExpressionRequest message. Here you have some samples: https://msdn.microsoft.com/en-us/library/hh547457.aspx

Grab list of users exists in a relation in dynamics crm

1 Answers