QueryExpression for N:N with Join

0
53
Role Image


Hi guys,

I have a case that I need to retrieve Extra Costs linked to a Trainee and from a specific Training. I know how to query for the Extra Costs of a Training and the Extra Costs of a Trainee. I then compare both lists and retrieve matching ones. That works but it is really a performance hit. How can i add a join to one of the query’s to combine both? 

Here is the code i am running now:

//THIS SECTION RETURNS ME ALL EXTRA COSTS FROM A TRAINING
                    ConditionExpression ceOpleidingExtraKost = new ConditionExpression("cref8_extrakosttraining", ConditionOperator.Equal, targetEntity.Id);
                    QueryExpression qeOpleidingExtraKost = new QueryExpression("cref8_extrakost");
                    qeOpleidingExtraKost.ColumnSet = new ColumnSet(true);
                    qeOpleidingExtraKost.Criteria.AddCondition(ceOpleidingExtraKost);
                    EntityCollection opleidingExtraKostResult = service.RetrieveMultiple(qeOpleidingExtraKost);

                    //THIS SECTION RETURNS ME ALL EXTRA COSTS FROM A TRAINEE
                    var query = new QueryExpression()
                    {
                        EntityName = "cref8_extrakost",
                        ColumnSet = new ColumnSet(true),
                    };
                    var link = query.AddLink("cref8_extrakost_cref8_cursist", "cref8_extrakostid", "cref8_extrakostid");
                    link.LinkCriteria = new FilterExpression()
                    {
                        Conditions =
                                    {
                                        new ConditionExpression("cref8_cursistid", ConditionOperator.Equal, relatedEntity.Id)
                                    }
                    };
                    var cursistExtraKosts = service.RetrieveMultiple(query);

                    //COMPARE BOTH EXTRA COSTS COLLECTION TO RETRIEVE ONLY THE ONES FROM A TRAINEE AND A SPECIFIC TRAINING
                    double traineeExtraKostKost=0;
                    EntityCollection TraineeTrainingExtraKostsCollection = new EntityCollection();
                    foreach (var extrakost in opleidingExtraKostResult.Entities)
                    {
                        foreach (var traineeExtraKost in cursistExtraKosts.Entities)
                        {
                            if (traineeExtraKost.Id==extrakost.Id)
                            {
                                traineeExtraKostKost += Convert.ToDouble(traineeExtraKost.Attributes.FirstOrDefault(q => q.Key == "cref8_prijs").Value);
                                TraineeTrainingExtraKostsCollection.Entities.Add(extrakost);
                            }
                        }
                        
                    }



Source link

Leave a reply

Please enter your comment!
Please enter your name here