Microsoft Dynamics CRM (Archived)

Query Expression with Link entity inner join on 2 columns

(0) Share

Report

Posted on by Community Member

Hello All,

i want to achieve the below using query expression link entity

"Select * FROM icms_icmssubjectmatrixmapping A INNER JOIN ava_subjectmatrix B ON

A.icms_subcategory= B.ava_subjectmatrix_subjectname AND

A.icms_category= B.ava_subjectmatrix_categoryname"

my code below has to check another and condition on join

string subjectmatrixid = string.Empty;
bool ismappedcategory = true;
// CrmServiceAdapter OrgService=new CrmServiceAdapter();
SchemaMappingController mappingController = new SchemaMappingController();
QueryExpression queryExpression = new QueryExpression("icms_icmssubjectmatrixmapping");
queryExpression.LinkEntities.Add(new LinkEntity("icms_icmssubjectmatrixmapping", "ava_subjectmatrix", "icms_subcategory", "ava_subjectmatrix_subjectname", JoinOperator.Inner));
queryExpression.LinkEntities[0].EntityAlias = "Link";

queryExpression.Criteria.Conditions.Add(new ConditionExpression("icms_msoswitchboardcategory", ConditionOperator.Equal, (object)"Animal Issues"));
queryExpression.Criteria.Conditions.Add(new ConditionExpression("icms_msoswitchboardsubcategory", ConditionOperator.Equal, (object)"Animal Cruelty/Welfare"));
queryExpression.Criteria.Conditions.Add(new ConditionExpression("icms_ismappedcategory", ConditionOperator.Equal, (object)ismappedcategory));

//queryExpression.LinkEntities[0].LinkCriteria.AddCondition("ava_subjectmatrix_categoryname", ConditionOperator.Equal, icms_category);

queryExpression.LinkEntities[0].Columns = new ColumnSet("ava_subjectmatrixid", "ava_subjectmatrix_subject");
EntityCollection entitiesJS1 = CrmServiceAdapter.OrgService.RetrieveMultiple(queryExpression);

how to achive this with CRM query expression

please help

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Community Member on at

Like (0)

Report

Hi ,

I don't think you can link to multiple columns using Query Expression.

So either you :

1- do a join on only one colum like you did on subcategory and then filter the results l l locally after

2- you can use the OrganizationContext with Linq to Sql .

var orgService= CrmServiceAdapter.OrgService ;

using (var svcContext= new OrganizationServiceContext(orgService))

{

var query_where3 = from A in svcContext.icms_icmssubjectmatrixmappingSet

join B in svcContext.ava_subjectmatrixSet

on

A.icms_subcategory equals B.ava_subjectmatrix_subjectname AND

A.icms_category equals B.ava_subjectmatrix_categoryname

//where .... ( add conditions here

select new

{

//columns to retrieve

};

}

check this link form more help on the query : msdn.microsoft.com/.../gg334593.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello

thanks for the reply :)

Is there any way to USE fetchXML instead queryexpression to achive the above ,

i want to try without LINQ

Was this reply helpful? Yes No
Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

Linked Entities (either via Fetch Xml or Query expression), are based on unique identifier, so I am pretty sure the answer would be no for what you are trying to do.

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

You can use non-id fields in the conditions, but the problem is that you can only link on one set of fields. Anything else, and you can only filter using specific values, not entity fields. Which pretty much crosses out any kind of 2-field-based linkage

Was this reply helpful? Yes No
Thomas David Dayman 11,323 on at

Like (0)

Report

You can do FetchExpression instead. Its much easier to use when doing complicated linked entities.

string fetchxml = "<Your Fetch as a string>"

EntityCollection result = service.RetrieveMultiple(new FetchExpression(fetchxml));

tracer.Trace("There are {0} entities found", result.Entities.Count);

foreach (var c in result.Entities)
{

//Get Values

}

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

Actually, just got an idea.. if you created a calculated text field on both entities, you might try populating it with a combination of names (category name + subject name), and, then, you might use those calculated fields to match the entities to each other (you`d have a field to match on).

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Thanks Alex,

I did the same way, i created a flag field with the combination of the columns , and for time being working, later i change to LINQ

thank you all for your help

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello,

Is there any way atleast to do order by for link entity columns ???

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello Thomas,

is there any way to do orderby to the aliased columns in queryexpression ??

i used queryexpression.Order.Add(new orderexpression ("Link.ColumnName",OrderType.Descending));

but throwing error saying that column not found with the table :(

Was this reply helpful? Yes No