C# CRM QueryExpression LinkEntity join via multiple field

Question

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?

score 0 · Answer 1 · edited May 23 '17 at 10:34

0

I'm not sure if you looked into it, but joins are really easy in Linq (msdn examples). I've written Contact-Accounts joins like this:

var appContacts = (
from c in ctx.contacts
join a in ctx.accounts on c.contactid equals a.primarycontactid
where a.name.Contains("Contoso")
select new { c.contactid, c.fullname }).ToList());

There's a good thread on N:N over here.

edited May 23 '17 at 10:34

Community

1
1

answered Jul 12 '12 at 15:09

Chris Snyder

958
4
10

It's not possible according to this post http://stackoverflow.com/questions/4046711/are-composite-joins-possible-using-fetchxml-in-microsoft-dynamics-crm-4-0 – user1465073 Jul 12 '12 at 17:10
1

That post says that joins across *multiple* columns are not possible in FetchXML...but are in Linq. I'm not sure what point you are trying to make? – Chris Snyder Jul 12 '12 at 17:23
See this followup thread and tell me your thoughts :) http://stackoverflow.com/questions/11461457/crm-linq-composite-join-the-method-join-is-not-supported-error – user1465073 Jul 12 '12 at 22:50
The answerer points out that a Linq provider can implement a subset of functionality. That is the case for the CRM Linq provider. The MSDN examples I linked to provide a list of ways you can use the Linq for CRM. – Chris Snyder Jul 13 '12 at 12:24

C# CRM QueryExpression LinkEntity join via multiple field

1 Answers1

Linked