Dynamics CRM 2011 LinkEntities left join issue

Question

How can I add a filter condition to the linked entity (for example email in this case)? Added filter condition to the link criteria which is giving me the duplicate rows.

The equivalent sql query should look like this.

select distinct OpportunityId
from Opportunity o 
   left join Email e on e.RegardingObjectId = o.OpportunityId 
   where o.StateCode = 1 and o.StatusCode = 3 
     and e.RegardingObjectId is null

But the QueryExpression class is doing the following way.

select distinct opportunityid 
from Opportunity o 
  left join Email e 
    on e.RegardingObjectId = o.OpportunityId 
    and e.RegardingObjectId is null
    where o.StateCode = 1 and o.StatusCode = 3

The code:

ClientCredentials Credentials = new ClientCredentials();
Credentials.Windows.ClientCredential 
  = System.Net.CredentialCache.DefaultNetworkCredentials;

Uri OrganizationUri = ""
Uri HomeRealmUri = null;
OrganizationServiceProxy orgService 
  = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null);
IOrganizationService _service = (IOrganizationService)orgService;

QueryExpression query = new QueryExpression();
query.Distinct = true;
query.EntityName = "opportunity";
query.ColumnSet = new ColumnSet(true);

FilterExpression filter1 = new FilterExpression();
filter1.FilterOperator = LogicalOperator.And;
filter1.AddCondition("statuscode", ConditionOperator.Equal,3);
filter1.AddCondition("statecode", ConditionOperator.Equal, 1);
query.Criteria = filter1;

LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.LeftOuter;
linkEntity1.LinkFromEntityName = "opportunity";
LinkEntity1.LinkFromAttributeName = "opportunityid";
linkEntity1.LinkToEntityName = "email";
linkEntity1.LinkToAttributeName = "regardingobjectid";

query.LinkEntities.Add(linkEntity1);

FilterExpression filter2 = new FilterExpression();

The issue is here at this condition. I could use filter on the LinkCriteria but not on the query since it is linked entity.

filter2.AddCondition("regardingobjectid", ConditionOperator.Null);
query.LinkEntities[0].LinkCriteria = filter2;

EntityCollection result = _service.RetrieveMultiple(query);

Console.WriteLine(result.Entities.Count());
Console.ReadKey();

score 0 · Answer 1 · answered Jun 21 '12 at 21:03

0

I'm not sure if the query posted is what you are looking for...

If it is, then you should be able to remove filter 2 and add to filter 1

filter1.AddCondition("opportunityid", ConditionOperator.Null);

But comparing the RegardingObjectId to both NULL and the OpportunityID with an AND operation shouldn't ever be true.

answered Jun 21 '12 at 21:03

Paul Way

1,966
1
13
10

I am looking for all the opportunityids which are not in the email entity.I used left join to get this in both sql and queryexpression class.The problem is that I am not able to add filter on the email entity. I changed the code to reflect regardingobjectid in filter2. – mdvr Jun 21 '12 at 22:30
@Madhavi is this for a particular email or all emails? If it's a particular email then on your left join specify the email and your final statement of your SQL example should be "and **o**.RegardingObjectId is null" – Paul Way Jun 22 '12 at 06:11
The SQL is working fine for the requirement. I was not able to give the critieria (e.regardingobject is null) in query expressions in console app. – mdvr Jun 22 '12 at 10:47

score 0 · Answer 2 · edited May 23 '17 at 12:11

I had similar problems adding conditions to linked entities. I found that i could do it using the dynamics 2013 sdk, but you would have to see if you could use the 2013 sdk against a 2011 dynamics. Please see Microsoft Dynamics Crm Sdk - Is this query possible?

The basic difference with the 2013 SDK is you can add a condition to the filter but give it an entity name which is for a linked entity. This means you don't actually add the condition to the link entity itself.

I also show in that link how to use the Linq provider to write the query which is another alternative you may want to try.

Dynamics CRM 2011 LinkEntities left join issue

2 Answers2