Friday 2 November 2012

Dynamics CRM 2011 LinkEntities left join issue

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();

No comments:

Post a Comment