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