Query N to N relationship

Having and N to N relationship between points and parameters, from a point ID we want to retrieve all the related parameters.


 EntityCollection getEPParameters(IOrganizationService serv, Guid POINTid)
        {
            QueryExpression query = new QueryExpression()
            {   //Parameters entity
                EntityName = "mile_emissionpointparameters",
                ColumnSet = new ColumnSet(true),
                LinkEntities = 
                        {
                            new LinkEntity
                            {
                                LinkFromEntityName = "mile_emissionpointparameters", //Parameters table
                                LinkFromAttributeName = "mile_emissionpointparametersid", //Parameters table id
                                LinkToEntityName = "mike_mile_emissionpoint_mile_emissionpointpara", // N to N entity 
                                LinkToAttributeName = "mile_emissionpointparametersid", 
                                LinkCriteria = new FilterExpression
                                {
                                    FilterOperator = LogicalOperator.And,
                                    Conditions = 
                                    {
                                        new ConditionExpression
                                        {
                                            AttributeName = "mile_emissionpointid",
                                            Operator = ConditionOperator.Equal,
                                            Values = { POINTid }  // Point id
                                        }
                                    }
                                }
                            }
                        }
            };

            // Obtain results from the query expression.
            return serv.RetrieveMultiple(query);

        }

If what we need is to get a list of entities filtered by some values of the other entities in the N to N relationship we just need to use nested LinkEntities. Imagine we have an N 2 N between control measures and eventRevisions. We want to get all the measures related to any open – semiopen (L_status 3 or 4) revisions.


QueryExpression objQueryExpression = new QueryExpression("lema_controlmeasure");
objQueryExpression.ColumnSet = new ColumnSet(true);
objQueryExpression.Distinct = true;

// Add the filter using the N to N middle table 
LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Inner;
linkEntity1.LinkFromEntityName = "L_controlmeasure";
linkEntity1.LinkFromAttributeName = "L_controlmeasureid";
linkEntity1.LinkToEntityName = "L_controlmeasure_hazardouseventrevision";
linkEntity1.LinkToAttributeName = "L_controlmeasureid";

// To be able to do a double Link we use another LinkEntity within the the original linked Entity
LinkEntity InnerLinkEntity = new LinkEntity();
InnerLinkEntity.JoinOperator = JoinOperator.Inner;
InnerLinkEntity.LinkFromEntityName = "L_controlmeasure_hazardouseventrevision";
InnerLinkEntity.LinkFromAttributeName = "L_hazardouseventrevisionid";
InnerLinkEntity.LinkToEntityName = "L_hazardouseventrevision";
InnerLinkEntity.LinkToAttributeName = "L_hazardouseventrevisionid";

// Filter of the inner Join by a value of the second entity of the N 2 N
InnerLinkEntity.LinkCriteria.AddCondition("L_status", ConditionOperator.In, 3, 4);

//Add this link to the original link
linkEntity1.LinkEntities.Add(InnerLinkEntity);

objQueryExpression.LinkEntities.Add(linkEntity1);   

EntityCollection results = _service.RetrieveMultiple(objQueryExpression);

Following this idea of having a InnerLinkEntity, we could add more innerLinkEntities being able to query N relationships at once!

I hope it was helpfull!!!

Cheers,

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s