Microsoft Dynamics CRM (Archived)

Retrieve multiple One to Many joint

(0) Share

Report

Posted on by Glide

Imagine 2 entities: Bag <- (One to Many) -> Item

I need to retrieve all the bags with the first item I find in each:
bag1, item1
bag2, item42
bag42, item100

Let's get the data:

var query = new QueryExpression("bag");
var result = svc.RetrieveMultiple(query);
var bags = new Dictionary<Guid, Guid>();
foreach (var entity in result.Entities)
{
  var itemQuery = new QueryExpression("item");
  itemQuery.Criteria.AddCondition(new ConditionExpression("bag", ConditionOperator.Equal, entity.Id));
  var itemResult = svc.RetrieveMultiple(itemQuery);
  bags[entity.Id] = itemResult.Entities[0].Id;
}

This works but of course it's not optimized since it executes as much queries as there are bags.

Isn't there a way to bring the whole thing in one query and maybe use a LinQ GroupBy or something like that to get the first item for each bag ?

Or maybe I could use the Many to One relationship (from item to bag) but I don't know how with query linked entities.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Glide 10 on at

Like (0)

Report

Actually it's not the Guid that I need but another alternate key in the Dictionary so I can't just query the item entity alone

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

You can do this with a Query Expression and a Left Join

QueryExpression query = new QueryExpression("bag");

query.ColumnSet = new ColumnSet(true);

// if necessary add criteria on the bag entity

query.Criteria = new FilterExpression();

query.Criteria.AddCondition(...);

// Add linked entity

LinkEntity le = query.AddLink("item", "bagid", "bagid", JoinOperator.LeftOuter);

le.Columns = new ColumnSet(...);

le.EntityAlias = "item";

// Retrieve the results

EntityCollection results = service.RetrieveMultiple(query);

Hope this helps.

Was this reply helpful? Yes No
Glide 10 on at

Like (0)

Report

Thank you for you answer.

I'm getting a very explicit and precise "Generic SQL error" to which I can't find the issue.

I didn't know we could link entities from outside like this though.

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

Like (0)

Report

Hi,

not sure you can actually retrieve the first item per bag.. You might probably use FirstOrDefault to retrieve only one item per bag in your loop:

svc.RetrieveMultiple(itemQuery).FirstOrDefault

And you could use that LinkEntity as Aric suggested, but that would not give you 1 item per bag - you'll get an intersect instead (which would have multiple rows per bag - by the number of items)

Was this reply helpful? Yes No
Glide 10 on at

Like (0)

Report

Thanks @Alex,

I know that this link will get the row duplicated but maybe I still can filter them with a GroupBy().Select(g => g.First).

FirstOrDefault I don't have this available. Is this an extension ?

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

Like (0)

Report

My mistake, FirstOrDefault would be on the entities collection:

svc.RetrieveMultiple(itemQuery).Entities.FirstOrDefault();

For the group by, I'm not sure it would work on the server. Linq is translated into FetchXml, and here is what Fetch has for the aggregates:

msdn.microsoft.com/.../gg309565.aspx

But you can always query everything, use "ToList()", and, then, do the grouping

Was this reply helpful? Yes No
Glide 10 on at

Like (0)

Report

Yes I think I'll do that, perf is not optimal but it'll do.

(FirstOrDefault() is still not available in the Entities attribute)

I looked over the fetchXML technique but it seems like a pain in the arse to create queries and maintain them.

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

Like (0)

Report

For the FirstOrDefault, you might not have System.Linq namespaces added.

Just add it to the "using"

Was this reply helpful? Yes No

Community site session details

Retrieve multiple One to Many joint

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

Retrieve multiple One to Many joint

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates