Dynamics CRM 2011 - Getting set of N:1 related entities with QueryExpression

Question

I'm working on a custom web service for a CRM 2011 deployment, and I'm now making an overview which relates to some of our entities. We have a custom product entity with details such as the product name and category, and we have an agreement entity, which links the product to the customer and contains product details specific to that customer. agreement and product have an N:1 relationship.

I'm now making an overview page that will be displayed on the customer page in CRM, which should display all agreements on that customer. For this display, I also need to retrieve some information about the products these agreements link to, which will be used to group the agreements on the overview page. I've been unable to find any relevant/specific examples on this, and I'm stuck trying to find a feasible way of querying for the data I need.

The way I imagine it, I would like to use two queries to get the required data. First, a query that gets all the agreements on the customer. Then pass this list to a second query, which returns all the products that intersect the first list. Is this possible using a QueryExpression, or do I need to loop over the agreement list and run a separate query for each individual association? I'd prefer to avoid FetchXML if possible.

score 3 · Answer 1 · answered Mar 21 '13 at 12:37

3

If I understand your question right, you'll just need to add a LinkEntity to your Product entity on the first query. You can also specify attributes of the Product entity to return as well, but they get returned as AliasedValues, so be aware of that.

answered Mar 21 '13 at 12:37

Daryl

18,592
9
78
145

Do they **have to** be returned as *AliasedEntity* instances? What if we don't use an alias - just return then named defaultly? +1 for links. – Konrad Viltersten Mar 21 '13 at 21:32
@KonradViltersten, If you ever want to return attributes from more than one Entity using Query Expressions, any attributes not from the main entity will be returned as aliased values. The name could be the same but the type of the values returned will be AliasedValues. – Daryl Mar 22 '13 at 01:08
I added an extension method for handling that and then got so convenient that I forgot about the code behind. How embarrassing... :) – Konrad Viltersten Mar 22 '13 at 08:28
@KonradViltersten what was the extension method on? The Entity? The IOrganizationService? – Daryl Mar 22 '13 at 12:25
I was fetching all three types used to build up a marketing list and I was in a lot of pains with linked-entity here, different names of name there. One of my first projects. Not fully proud, haha. (And, of course, I was joking in my reply. You know that, right?) – Konrad Viltersten Mar 23 '13 at 00:32
@KonradViltersten of course, everyone knows my way is better ;) I'm still not sure how you have an extension method that completely hides the aliased value though... – Daryl Mar 23 '13 at 04:33
Shortened version: One wants to fetch an entity with a field *f* that sometimes is null. In such case, one needs to substitute *f* for an other filed *g* that comes form a linked entity. It was annoying to have two possible types (`x as String` or `x as AliasedValue... as String`) there. So I wrote a class that hides that operation for me. The extension method, if I recall correctly, was targeting *Entity* and allowed it to *GetSplitTypeValueAsString*. So I wasn't really hiding the real data - rather not having to know about it. Have I answered your question? – Konrad Viltersten Mar 23 '13 at 12:52

score 1 · Answer 2 · edited May 23 '17 at 10:32

1

Adding to Daryls answer, you can see this post for an example on how to link entities. MSDN is confusing on the subject (at least to me).

I'm proposing a nice structure of query expression there, while Daryl presents a more compact equivalent. (Mine is of course better. :D )

edited May 23 '17 at 10:32

Community

1
1

answered Mar 21 '13 at 21:33

Konrad Viltersten

36,151
76
250
438

score 0 · Accepted Answer · answered Apr 04 '13 at 10:50

I looked at LinkEntity, but I eventually achieved what I wanted to do with ConditionOperator.In on the second query, like this:

query.Criteria.AddCondition(
    new ConditionExpression(
        "myprefix_productid",
        ConditionOperator.In,
        agreementList )
);

Where agreementList is an array of Guids. I tried this before posting the question as well, but without wrapping the parameters in new ConditionExpression(), which didn't work. Presumably this was because the implicit way invoked the wrong overload for the AddCondition function.

Useful links in the other answers though, I'll look into them as well!

Dynamics CRM 2011 - Getting set of N:1 related entities with QueryExpression

3 Answers3