0

I have a complex M:M:M query I can't figure out. My case: I need to retrieve all extra costs related to a trainee, from a particular training.
The relations are as follow:

  • Trainee <-> Training M:M
  • Trainee <-> Extra Costs M:M
  • Training <-> Extra costs 1:M

My code currently looks like this but i don't even know if this is the way to go:

if (context.MessageName == "Update" && context.Stage == 20)
        {
            
            targetEntity = context.InputParameters["Target"] as Entity;
            var courseID = targetEntity.Id;

            QueryExpression Query = new QueryExpression { EntityName = "cref8_extrakost", ColumnSet = new ColumnSet(true) };

            // LINK BETWEEN A TRAINING AND A CURSIST
            LinkEntity TrainingCursist = new LinkEntity
            {
                LinkFromEntityName = "cref8_cursist",
                LinkToEntityName = "cref8_opleiding_cref8_cursist",
                LinkFromAttributeName = "cref8_cursistid",
                LinkToAttributeName = "cref8_cursistid",
                Columns = new ColumnSet(true),
                JoinOperator = JoinOperator.Inner
            };
            TrainingCursist.LinkCriteria.AddCondition("cref8_opleidingid", ConditionOperator.Equal, courseID);

            // LINK BETWEEN A CURSIST AND A EXTRA COST
            LinkEntity ExtraCostCursist = new LinkEntity
            {
                LinkFromEntityName = "cref8_extrakost",
                LinkToEntityName = "cref8_extrakost_cref8_cursist ",
                LinkFromAttributeName = "cref8_extrakost",
                LinkToAttributeName = "cref8_cursistid",
                JoinOperator = JoinOperator.Inner,
                Columns = new ColumnSet(true)
            };
            ExtraCostCursist.LinkCriteria.AddCondition("cref8_cursistid", ConditionOperator.Equal, "34b5de6b - 0758 - ec11 - 8f8f - 000d3aad43bd");

            Query.LinkEntities.Add(TrainingCursist);
            Query.LinkEntities.Add(ExtraCostCursist);

            var collection = service.RetrieveMultiple(Query);
        }

Any help would be appreciated

Best Regards,
Anthony

AnthonyDob
  • 5
  • 5
  • It's all wrong here. Why you wrote this `Query.LinkEntities.Add(TrainingCursist);` there is no any relationship for the `cref8_cursist` and `cref8_opleiding_cref8_cursist` entities with `cref8_extrakost` entity. Also, here `ExtraCostCursist.LinkCriteria.AddCondition("cref8_cursistid", ConditionOperator.Equal, "34b5de6b-0758-ec11-8f8f-000d3aad43bd");` you're comparing Guid value with string value. You should convert your string to Guid first, like this: `ExtraCostCursist.LinkCriteria.AddCondition("cref8_cursistid", ConditionOperator.Equal, new Guid("34b5de6b-0758-ec11-8f8f-000d3aad43bd"))`; – Arsen Dec 21 '21 at 13:48
  • I recommend you to create two separate QueryExpressions. One for `Trainee <-> Training M:M`. Another one for `Trainee <-> Extra Costs M:M` (with LinkEntity for `Training <-> Extra costs 1:M`). Retrieve these two collections and try to get your data programmatically. After getting it done you can think about combining this two queries into one complex query. – Arsen Dec 21 '21 at 13:55
  • @Arsen I changed my code. I now have a collection with only the Extra Costs from a Training. Is it possible to filter this Extra Cost collection by a specified Cursist? – AnthonyDob Dec 21 '21 at 14:36
  • @Arsen with my little knowledge I managed to have a list of Extra costs from a Trainee and a List of Extra Costs from a Training. If I compare those I would also know which Extra Costs from the Training, the Trainee is related to. I think it would be a bit scuffed and bad performance but I'm struggling to make the though query – AnthonyDob Dec 21 '21 at 14:57

0 Answers0