Microsoft Dynamics CRM (Archived)

How to retrieve multiple entity field values with multiple condition?

(0) Share

Report

Posted on by Community Member

Hi,

I am facing some difficulties to fetch multiple entities with multiple condition. I tried following method,

Before that let me explain my concept and why i am seeking advice?

I am fetching field values with two entities, Namely,

Entity1 = Account

Entity2 = Account Segmentation

I want to retrieve Account Id, Account Name, Address from Account Entity based on Region(EMEA)(This region field in Account) and Distirbutor(This distributor field in Account segmentation)

def GetLinkToQuery(entityName1,entityName2):

//For Account Entity

condition1 = ConditionExpression();
condition1.AttributeName = "AttributeName1";
condition1.Operator = ConditionOperator.Equal;
condition1.Values.Add("AttributeValue1");

//For Account Segmentation Entity

condition2 = ConditionExpression();
condition2.AttributeName = "AttributeName2";
condition2.Operator = ConditionOperator.Equal;
condition2.Values.Add("AttributeValue2");

filter1 = FilterExpression();
filter1.Conditions.Add(condition1);
filter1.Conditions.Add(condition2);

query = QueryExpression(entityName1(Account),entityName2(AccountSegmentation));

query.ColumnSet = ColumnSet(True)

query.Criteria.AddFilter(filter1);

EntityA = LinkEntity("account", "gnh_accountsegmentation", "accountid", "gnh_accountid", JoinOperator.Inner)

query.LinkEntities.Add(EntityA)

e = Entity()

e.LogicalName = "account"

q = Entity()

q.LogicalName = "accountsegmentation"

test = MSCrmService.RetrieveMultiple(GetLinkToQuery(e.LogicalName,q.LogicalName)).Entities

Hope you understand my query and please help me guys.

Thanks

Srini

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

jestuder 158 on at

Like (0)

Report
Why not use LINQ, its much easier in my opinion:

var oAccountData = (from a in context.CreateQuery("account") join as in context.CreateQuery("accountsegmentation") on a.id equals as.accountid where a["AttributeName1"] == "AttributeValue1" && as[""AttributeName2""] == "AttributeValue2" select new { AccountID == a["accountid"], AccountName == a["name"], Address == a["address"] }).Single();

The above code will need tweaking to make it work, but I wanted to show how it would look, compared to what you provided. To make it work you would want to create a C# model that you will use to store the data you get back. Also the .single on the end only returns one record. If you are getting mulitple records, then you will want a List<YourAccountModelClass> and replace the .single with ToList().

Here is a link about joins in linq statements: https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause

Here is a link about Linq qith CRM: https://msdn.microsoft.com/en-us/library/gg328328.aspx

In my example I use late binding. If you are using early binding then you will need your early binding class in your project.

Was this reply helpful? Yes No
Suggested answer

ARIFNIIT 1,391 on at

Like (0)

Report

try to use fetchXml. use link entity in fetchxml to retrieve related entity fields and you can apply condition on both entity. let me know your requirement clearly i will create fetchxml query.

please varify if its help you.

Was this reply helpful? Yes No
abm abm 31 Most Valuable Professional on at

Like (0)

Report

Hi Srini,

Please see the below function. Change the filter attribute type accordingly.

public EntityCollection RetrieveAccount(string regionema, string distributor)

{

QueryExpression query = new QueryExpression("account")

{

Criteria = new FilterExpression(LogicalOperator.And)

{

Conditions =

{

new ConditionExpression("regionema", ConditionOperator.Equal, regionema),

}

},

LinkEntities =

{

new LinkEntity("account", "gnh_accountsegmentation", "accountid", "gnh_accountid", JoinOperator.Inner)

{

LinkCriteria =

{

Conditions =

{

new ConditionExpression("distributor", ConditionOperator.Equal, distributor)

}

}

}

}

};

return MSCRMService.RetrieveMultiple(query);

}

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

You can use the following sample as well if you want to use Query Expression:

QueryExpression query = new QueryExpression()

{

EntityName = "account",

ColumnSet = columns,

LinkEntities =

{

new ConditionExpression(attributeName, ConditionOperator.Equal, attribute1),

},

LinkEntities =

{

new LinkEntity()

{

LinkToEntityName = "accountsegmentation",

LinkToAttributeName = "accountid",

LinkFromEntityName = "account",

LinkFromAttributeName = "accountid",

LinkCriteria =

{

Conditions =

{

new ConditionExpression(acctSegAttrName, ConditionOperator.Equal, attribute2)

}

},

}

}

};

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

imayur 630 on at

Like (0)

Report
Hi,

Is it mandatory to use linq?

Here are two approaches

Approach 1 : Query Expression

QueryExpression searcQuery = new QueryExpression("account"); searcQuery.NoLock = true; searcQuery.ColumnSet = new ColumnSet("accountid", "name"); searcQuery.Criteria.FilterOperator = LogicalOperator.Or; searcQuery.Criteria.AddCondition("new_name", ConditionOperator.Equal, param); searcQuery.Orders.Add(new OrderExpression("createdon", OrderType.Descending)); LinkEntity linkEntityB = new LinkEntity() { LinkFromEntityName = "account", LinkFromAttributeName = "accountid", LinkToEntityName = "new_accountSegmentation", LinkToAttributeName = "new_accountSegmentationid", JoinOperator = JoinOperator.LeftOuter, // Type of join Columns = new ColumnSet("new_name"),//select fields for Entity B EntityAlias = "B" }; //Optional Third entity LinkEntity linkEntityC = new LinkEntity() { LinkFromEntityName = "new_a", LinkFromAttributeName = "new_aid", LinkToEntityName = "new_c", LinkToAttributeName = "new_aRefid", JoinOperator = JoinOperator.LeftOuter, Columns = new ColumnSet("new_name"), //select fields for Entity C EntityAlias = "C" }; //Filter for link entity you can use Filter expression as well linkEntityB.LinkCriteria.AddCondition("new_name", ConditionOperator.Equal, "sadsa"); linkEntityC.LinkCriteria.AddCondition("new_name", ConditionOperator.Equal, "asdasdsa"); searcQuery.LinkEntities.Add(linkEntityB); searcQuery.LinkEntities.Add(linkEntityC); EntityCollection entityACollection = crm.RetrieveMultiple(searcQuery);

Approach 2: Fetch XML

string fetchQuery = @"<?xml version='1.0' ?>"; fetchQuery += @"<fetch distinct='false' mapping='logical' output-format='xml-platform' no-lock='true' version='1.0'>"; fetchQuery += @"<entity name='account' >"; fetchQuery += @"<attribute name='name' />"; fetchQuery += @"<attribute name='createdon' />"; fetchQuery += @"<filter type='or' >"; fetchQuery += @"<condition attribute='name' value='" + param + "' operator='eq' />"; fetchQuery += @"</filter>"; fetchQuery += @"<link-entity name='new_accountSegmentation' alias='AccountSegmentation' to='accountid' from='new_accountSegmentationid' link-type='outer' >"; fetchQuery += @"<attribute name='new_name' />"; fetchQuery += @"<attribute name='createdon' />"; fetchQuery += @"<filter type='or' >"; fetchQuery += @"<condition attribute='new_name' value='" + param + "' operator='eq' />"; fetchQuery += @"</filter>"; fetchQuery += @"</link-entity>"; fetchQuery += @"</entity>"; fetchQuery += @"</fetch>"; EntityCollection aCollection = orgService.RetrieveMultiple(new FetchExpression(fetchQuery));

Was this reply helpful? Yes No