Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Retrieve multiple One ...
Microsoft Dynamics CRM (Archived)

Retrieve multiple One to Many joint

(0) ShareShare
ReportReport
Posted on by Glide 10

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

  • ashlega Profile Picture
    ashlega 34,475 on at
    RE: Retrieve multiple One to Many joint

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

    Just add it to the "using"

  • Glide Profile Picture
    Glide 10 on at
    RE: Retrieve multiple One to Many joint

    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.

  • ashlega Profile Picture
    ashlega 34,475 on at
    RE: Retrieve multiple One to Many joint

    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

  • Glide Profile Picture
    Glide 10 on at
    RE: Retrieve multiple One to Many joint

    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 ?

  • ashlega Profile Picture
    ashlega 34,475 on at
    RE: Retrieve multiple One to Many joint

    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)

  • Glide Profile Picture
    Glide 10 on at
    RE: Retrieve multiple One to Many joint

    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.

  • Suggested answer
    Aric Levin Profile Picture
    Aric Levin 30,188 on at
    RE: Retrieve multiple One to Many joint

    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.

  • Glide Profile Picture
    Glide 10 on at
    RE: Retrieve multiple One to Many joint

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans