How to retrieve data from multiple entities in CRM

(0) Share

Report

Posted on by Community Member

Hi,

I have an entity called "A" and it has two lookup fields to entities "B" and "C". Now I want to retrieve data from all the three entities using CRM SDK. Can anyone please help.

Thanks,

Nikhil

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (1)

Guido Preite 54,084 Moderator on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

The simplest way (if you don't want to deal with LINQ or in-time structures) is to retrieve first the main record and after retrieve the related entities.

The performance is not optimized but it will work:

Entity mainEntity = service.Retrieve("new_entitya", recordAId, new ColumnSet(true));

if (mainEntity["new_lookupb"] != null) {

EntityReference bRef = (EntityReference)mainEntity["new_lookupb"];

EntityB = service.Retrieve(bRef.LogicalName, bRef.Id, new ColumnSet(true));

}

if (mainEntity["new_lookupc"] != null) {

EntityReference cRef = (EntityReference)mainEntity["new_lookupc"];

EntityC = service.Retrieve(cRef.LogicalName, cRef.Id, new ColumnSet(true));

}

Was this reply helpful? Yes No
Verified answer

Waqar Sohail on at

Like (2)

Report
RE: How to retrieve data from multiple entities in CRM

Hi Nikhil, You can use Query Expression to retrieve Linked Entities.

QueryExpression query = new QueryExpression("EntityALogicalName"); query.ColumnSet = new ColumnSet("column1", "coumn2"); // Or retrieve All Columns //query.ColumnSet = new ColumnSet(true); LinkEntity EntityB = new LinkEntity("EntityALogicalName", "EntityBLogicalName", "EntityALinkAttributeName", "EntityBLinkAttributeName", JoinOperator.Inner); EntityB.Columns = new ColumnSet("column1", "coumn2"); EntityB.EntityAlias = "EntityB"; // Can put condition like this to any Linked entity // EntityB.LinkCriteria.Conditions.Add(new ConditionExpression("statuscode", ConditionOperator.Equal, 1)); query.LinkEntities.Add(EntityB); // Join Operator can be change if there is chance of Null values in the Lookup. Use Left Outer join LinkEntity EntityC = new LinkEntity("EntityALogicalName", "EntityCLogicalName", "EntityALinkAttributeName", "EntityCLinkAttributeName", JoinOperator.Inner); EntityC.Columns = new ColumnSet("column1", "coumn2"); EntityC.Columns = new ColumnSet("column1", "coumn2"); EntityC.EntityAlias = "EntityC"; query.LinkEntities.Add(EntityC); query.Criteria.Conditions.Add(new ConditionExpression("status", ConditionOperator.Equal, 1)); var result = service.RetrieveMultiple(query); foreach (var entity in result.Entities) { // Get the Columns from the Entity Obj Like this. Depands on type of the Column. string entityAColumn1 = entity.Contains("column1") ? entity["column1"].ToString() : string.Empty; // Use Link Entity Alias with column name string entityBColumn1 = entity.Contains("EntityB.column1") ? (entity["EntityB.column1"] as AliasedValue).Value.ToString() : string.Empty; string entityCColumn1 = entity.Contains("EntityC.column1") ? (entity["EntityC.column1"] as AliasedValue).Value.ToString() : string.Empty; }

You can use Left Outer join if there is possibility that any lookup values can be Null. You can access the Link Attribute using LinkEntity Alias mention in code. I hope this will also work.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

Thanks Waqar.

Was this reply helpful? Yes No
Stuie 255 on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

Hi

I have a similar scenario except i have 5 fields in entity A that relate to data in entity B.

In entity A i have;

firstchoice
secondchoice

thirdchoice

etc

so...

entity A firstchoice - need to get -> entity B field1name, field2name

entity A secondchoice - need to get ->entity B field1name, field2name

etc

Does this make sense?

Was this reply helpful? Yes No
Stuie 255 on at

Like (0)

Report
RE: How to retrieve data from multiple entities in CRM

Would I repeat this for each of the five?

LinkEntity EntityB = new LinkEntity("EntityALogicalName", "EntityBLogicalName", "EntityALinkAttributeName", "EntityBLinkAttributeName", JoinOperator.Inner); EntityB.Columns = new ColumnSet("column1", "coumn2"); EntityB.EntityAlias = "EntityB"; // Can put condition like this to any Linked entity // EntityB.LinkCriteria.Conditions.Add(new ConditionExpression("statuscode", ConditionOperator.Equal, 1)); query.LinkEntities.Add(EntityB);

Was this reply helpful? Yes No
Waqar Sohail on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

Hi Stuie,

What I understand you have one Entity and it have 5 different relationship with other Entity B?

and you want to get data based on these relation.

I am correct?

Regards

Was this reply helpful? Yes No
Stuie 255 on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

Hi

Yes I have Entity A and that has 5 fields in it which are quid's (lookup).

Those quid's relate to Entity B in which I need two field values per entity A quid.

Was this reply helpful? Yes No
Waqar Sohail on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

Dear Stuie,

Can you please explain with example in data form,

Lets take example like this,

Account entity have two lookup's of contact entity,

1. PrimaryContact

2. SecondaryContact

So you want to query Account and get contact fields as well based on above two relationship.

Please explain your requirement.

Regards

Was this reply helpful? Yes No
Stuie 255 on at

Like (0)

Report

RE: How to retrieve data from multiple entities in CRM

Hi

OK I have two entities

application & studychoice

application is the main entity for which i need to get most values and includes five fields called

firstchoice
secondchoice
thirdchoice
forthchoice
fifthchoice

studychoice has numerous fields and has a unique id's of studychoiceid

for each of the id's above in application i need fields name and university from studychoice

I hope this makes sense.

Many thanks

Was this reply helpful? Yes No
Suggested answer

imayur 630 on at

Like (1)

Report
RE: How to retrieve data from multiple entities in CRM

Hi,

This can be achieved using query expression.

Please refer below code

QueryExpression searcQuery = new QueryExpression("new_a"); searcQuery.NoLock = true; searcQuery.ColumnSet = new ColumnSet("new_a", "somefields"); 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 = "new_a", LinkFromAttributeName = "new_aid", LinkToEntityName = "new_b", LinkToAttributeName = "new_aRefid", JoinOperator = JoinOperator.LeftOuter, Columns = new ColumnSet("new_name"),//select fields for Entity B EntityAlias = "B" }; 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" }; searcQuery.LinkEntities.Add(linkEntityB); searcQuery.LinkEntities.Add(linkEntityC); EntityCollection entityACollection = crm.RetrieveMultiple(searcQuery);

In result you will get join result for all 3 entities

Was this reply helpful? Yes No