How to Inner join with query expression crm

Question

I'm trying to do a simple inner join with Query Expression. I'm trying to covert this query with QE but I got always the same error. I'm doing this QE:

Entity Role = new Entity();
Role.LogicalName = "role";

Entity SystemUserRoles = new Entity();
SystemUserRoles.LogicalName = "systemuserroles";

QueryExpression query = new QueryExpression() {
  Distinct = false, EntityName = Role.LogicalName, ColumnSet = new ColumnSet("name"),

  LinkEntities = {
    new LinkEntity {
      JoinOperator = JoinOperator.Inner, LinkFromAttributeName = "roleid", LinkFromEntityName = Role.LogicalName, LinkToAttributeName = "roleid", LinkToEntityName = SystemUserRoles.LogicalName,
    }
  },

  Criteria = {
    Filters = {
      new FilterExpression {
        FilterOperator = LogicalOperator.And, Conditions = {
          new ConditionExpression("systemuserid", ConditionOperator.Equal, "9b1bf31d-ac29-e211-9826-00155d0a0b0f"),

        },
      },

    }
  }
};

ret = _service.RetrieveMultiple(query);

to get this:

SELECT b.Name
  FROM Role b
   INNER JOIN SystemUserRoles a
   ON a.RoleId=b.RoleId  
    WHERE SystemUserId = '9b1bf31d-ac29-e211-9826-00155d0a0b0f'

but this tells me the that the entity Role doesn't contain the field SystemUserId. Any idea?

Daryl · Accepted Answer · 2017-08-31T12:43:17.120

Your Condition Expression needs to go on the LinkCriteria object. In effect this is how your current query is getting converted.

SELECT b.Name
FROM Role b
INNER JOIN SystemUserRoles a
ON a.RoleId=b.RoleId  
WHERE b.SystemUserId = '9b1bf31d-ac29-e211-9826-00155d0a0b0f'

Try this query expression:

Entity role = new Entity();
role.LogicalName = "role";

Entity systemUserRoles = new Entity();
systemUserRoles.LogicalName = "systemuserroles";

QueryExpression query = new QueryExpression() {
  Distinct = false, EntityName = role.LogicalName, ColumnSet = new ColumnSet("name")
};

query.AddLink( systemUserRoles.LogicalName, "roleid", "roleid").
LinkCriteria.AddCondition("systemuserid", ConditionOperator.Equal, "9b1bf31d-ac29-e211-9826-00155d0a0b0f");

ret = _service.RetrieveMultiple(query);

Notice how a Condition is getting added to the LinkCriteria on the link rather than the query expression itself?

Update 1

As @JamesWierzba points out, there is no reason to define an entity just to use it's logical name:

QueryExpression query = new QueryExpression() {
  Distinct = false, EntityName = "role", ColumnSet = new ColumnSet("name")
};

query.AddLink("systemuserroles", "roleid", "roleid").
LinkCriteria.AddCondition("systemuserid", ConditionOperator.Equal, "9b1bf31d-ac29-e211-9826-00155d0a0b0f");

ret = _service.RetrieveMultiple(query);

And if you were using the DLaB.Xrm Nuget Package you could write it even more concisely:

var qe = QueryExpressionFactory.Create("role", "name");
qe.AddLink("systemuserroles", "roleid")
  .WhereEqual("systemuserid", "9b1bf31d-ac29-e211-9826-00155d0a0b0f");

ret = _service.RetrieveMultiple(qe);

Why did you create two Entity objects `role` and `systemUserRoles` to define their LogicalName just to retrieve that logical name string value later. Why not just use the string value — James Wierzba, Aug 30 '17 at 17:18
@JamesWierzba because that was what was in the op. I probably should have removed them in my example — Daryl, Aug 30 '17 at 21:53
Excellent response, thank you. Q: How do you specify which entity's field you are referencing if, for example, you need to check that a.date<> b.versiondate i.e. a condition that involves a field from each entity? — Zeek2, Sep 27 '18 at 07:37
e.g. I need to do something like this: LinkEntity join = bulkDeleteQuery.AddLink("dataset", "datasetguid", "datasetId"); join.LinkCriteria.AddCondition("date", ConditionOperator.Equal, "versiondate"); join.LinkCriteria.AddCondition("version", ConditionOperator.Equal, "version"); Where the first field is in one entity (dataset) and the second field is in the other entity (record). — Zeek2, Sep 27 '18 at 08:36
I believe this is where you use aliased names. You might want to ask a new question, and link to this one. — Daryl, Sep 27 '18 at 10:13

How to Inner join with query expression crm

1 Answers1

Update 1