Query Intersect Entity And Its Attribute

Introduction

When we set up a system N:N relationship between two entities, an intersect entity is created internally which is not accessible directly. Let’s say we have N:N relationship between the case and workorder entity.

And if you try to see attributes of this entity, you will find the following attributes.

So, in case we want to query this entity, we need to query it based on the related entities. If you are looking for the sample code to query the intersect entity and get its attribute, this post will help you.

Requirement

Query the above intersect entity and check if there is any work order associated to the case. If yes, we want to get the workorderid.

Solution

As said we can’t query intersect entity directly, but we can query it using case or work order. In our case, we will be querying it with the case entity. We are going to use QueryExpression class here and will be using its AddLink method to get the data from the intersect entity. We can use the following code.

//Create Query Expression.
QueryExpression query = new QueryExpression()
{
EntityName = "incident",
ColumnSet = new ColumnSet("title"),
LinkEntities =
{
new LinkEntity
{
LinkToEntityName = "him_incident_msdyn_workorder", //name of the intersect entity
LinkFromAttributeName="incidentid",
LinkToAttributeName = "incidentid",
Columns=new ColumnSet(new string[]{"msdyn_workorderid" }), //workorder id in interset entity
EntityAlias="intersect", //add alias name to refer attribute easily
LinkCriteria = new FilterExpression
{
FilterOperator = LogicalOperator.And,
Conditions =
{
new ConditionExpression
{
AttributeName = "incidentid",
Operator = ConditionOperator.Equal,
Values = { caseid }
}
}
}
}
}
};
// Obtain results from the query expression.
EntityCollection results = service.RetrieveMultiple(query);

Now, to get the workorderid, we need to use AliasedValue class here. For example, to get the first workorderid, we need to use the following code.

Guid workorderid =new Guid(result.Entities[0].GetAttributeValue<AliasedValue>("intersect.msdyn_workorderid").Value.ToString());

Hope it will help someone!