Is it possible to join multiple fields dynamically in CRM?
What I mean is something like this in SQL
select field1, field2, ..., fieldN
from ServiceAppointment
inner join bh_product
on bh_product.bh_contract = ServiceAppointment.bh_product.bh_contract
and bh_product.serviceid = ServiceAppointment.serviceid
I'm trying to come-up with something above using queryexpression but I'm not getting the desired behavior I want and I end having LOTS of records than expected. I can do this if I KNOW before hand the value of field number 2, but what at runtime I don't know and the query has to join 2 fields. My actual code is below, minus the part that I can't come up with.
This doesn't work... I'm expecting only 2 records but I get hundreds...
var leContact = new LinkEntity(ServiceAppointment.EntityLogicalName, ActivityParty.EntityLogicalName, "activityid", "activityid", JoinOperator.Inner);
leContact.LinkCriteria = new FilterExpression();
leContact.LinkCriteria.AddCondition("partyid", ConditionOperator.Equal, contactId);
queryExpression.LinkEntities.Add(leContact);
result = GetServiceActivityList(queryExpression);
var leService = new LinkEntity(ServiceAppointment.EntityLogicalName, BrightHorizons.Shared.CRM.Interface.Service.EntityLogicalName, "serviceid", "serviceid", JoinOperator.Inner);
queryExpression.LinkEntities.Add(leService);
result = GetServiceActivityList(queryExpression);
// THIS IS THE PROBLEM
var leProduct = new LinkEntity(ServiceAppointment.EntityLogicalName, bh_product.EntityLogicalName, "bh_contract", "bh_contract", JoinOperator.Inner);
var leProduct2 = new LinkEntity(ServiceAppointment.EntityLogicalName, bh_product.EntityLogicalName, "serviceid", "bh_service", JoinOperator.Inner);
queryExpression.LinkEntities.Add(leProduct2);
queryExpression.LinkEntities.Add(leProduct);
result = GetServiceActivityList(queryExpression);
//THIS ALSO DOESNT WORK
var leProduct = new LinkEntity(ServiceAppointment.EntityLogicalName, bh_product.EntityLogicalName, "bh_contract", "bh_contract", JoinOperator.Inner);
leProduct.AddLink(bh_product.EntityLogicalName, "bh_service", "bh_service", JoinOperator.Inner);
queryExpression.LinkEntities.Add(leProduct);
result = GetServiceActivityList(queryExpression);
What am I doing wrong?