C# crm linQ LinkEntities

Question

I need to query from a CRM entity some values where a linked entity contains a string inside its name.

I try to explain:

• I have new_supplycontract table.
• This table, has an EntityReference named new_pod
• The new_pod entity, has two fields: new_citypod and new_street
• new_citypod points to another entity named new_city
• new_street points to another entity named new_street

I need to query the new_supplycontract table to retrieve only the records whose new_pod contains a street which name contains a string I pass and a city which name contains another string i pass.

I know this code works for retrieving all new_supplycontract entities whose two text fields "new_city" and "new_address" are like to two strings passed.

QueryExpression query = new QueryExpression(new_supplycontract.EntityLogicalName);
query.ColumnSet = new ColumnSet(true);
query.Criteria.AddCondition("new_city", ConditionOperator.NotNull);
query.Criteria.AddCondition("new_address", ConditionOperator.NotNull);

query.LinkEntities.Add(new LinkEntity(new_supplycontract.EntityLogicalName, "new_comune", "new_city", "new_comuneid", JoinOperator.Inner)); 
query.LinkEntities[0].Columns.AddColumns("new_name");
query.LinkEntities[0].EntityAlias = "comuneTemp";
query.LinkEntities[0].LinkCriteria.AddCondition("new_name", ConditionOperator.Like, "%" + comune + "%");

query.LinkEntities.Add(new LinkEntity(new_supplycontract.EntityLogicalName, new_via.EntityLogicalName, "new_address", "new_viaid", JoinOperator.Inner));
query.LinkEntities[1].Columns.AddColumns("new_name");
query.LinkEntities[1].EntityAlias = "viaTemp";
query.LinkEntities[1].LinkCriteria.AddCondition("new_name", ConditionOperator.Like, "%" + via + "%");


DataCollection<Entity> entities = service.RetrieveMultiple(query).Entities;          

But I don't really know how to use this code for my goal. I don't know how to filter an entityreference's entityreference.

Any help will be appreciated

Answer 1

LinkedEntities can have nested LinkedEntities in CRM 2011. This was changed since 2013 where an EntityName attribute was introduced and there wasn't a need for having nested entities.

But basically, you could start from the supplycontract entity, then add a linked entity against your new_pod entity. From that Linked Entity, you'll need to add 2 linked entities, one to the new_city entity and another one to the new_street entity. Each of these 2 linked entities will need a contains condition expression where you pass the 2 strings you want to use for filtering.

Ex:

QueryExpression query = new QueryExpression(new_supplycontract.EntityLogicalName);
query.ColumnSet = new ColumnSet(true);
var le = query.LinkEntities.Add(new LinkEntity(new_pod.EntityLogicalName, "new_pod", "new_pod", "new_podid", JoinOperator.Inner)); 

var lePod = le.LinkEntities.Add(new LinkEntity(new_pod.EntityLogicalName, "new_city", "new_citypod", "new_cityid", JoinOperator.Inner));
var leCity = le.LinkEntities.Add(new LinkEntity(new_pod.EntityLogicalName, "new_street", "new_street", "new_streetid", JoinOperator.Inner));

//Add conditions to each nested linked entity now as above.

Now, I personally prefer LINQ cause the query is much more easier to read than using LinkedEntities.

Hope this helps.

Answer 2

I've ran into countless issues with LINQ to CRM, mostly due to my misunderstanding of how it works. Unfortunately, Query Expressions are extremely verbose and hard to read. I created and use the DLaB.Xrm library to make my life simpler (and more typed, I love me some EarlyBound Dev)

Here is your query using DLaB.Xrm:

var qe = QueryExpressionFactory.Create<new_supplycontract>();
var podLink = qe.AddLink<new_pod>(new_supplycontract.Fields.new_Pod, new_pod.Fields.Id);

podLink.AddLink<new_city>(new_pod.Fields.new_citypod,              // This is the attribute of the "from" entity to join on
                          new_city.Fields.Id,                      // This is the attribute of the "to" entity to join on.  If name is identical, this parameter can be removed
                          new ColumnSet(new_city.Fields.new_name)) // AliasedValue to add to the result
    .LinkCriteria.AddCondition("new_name", ConditionOperator.Like, "%" + comune + "%");

podLink.AddLink<new_street>(new_pod.Fields.new_street, 
                            new_street.Fields.Id,
                            ColumnSet(new_street.Fields.new_name))
    .LinkCriteria.AddCondition("new_name", ConditionOperator.Like,  "%" + via + "%");

var leads = service.GetEntities(qe);

Answer 3

You need to chain the link entities. Here's an example:

        ConditionExpression condition3 = new ConditionExpression();
        ...
        LinkEntity link3 = new LinkEntity();
        ...
        link3.LinkCriteria.Conditions.Add(condition3);

        ConditionExpression condition2 = new ConditionExpression();
        ...
        LinkEntity link2 = new LinkEntity();
        ...
        link2.LinkCriteria.Conditions.Add(condition2);

        LinkEntity link1 = new LinkEntity();
        ...
        link1.LinkEntities.Add(link2);
        link1.LinkEntities.Add(link3);

        QueryExpression query = new QueryExpression("");
        ...
        query.LinkEntities.Add(link1);

        DataCollection<Entity> entities = service.RetrieveMultiple(query).Entities;

It helps to write them separately and use .Criteria.Conditions.Add() instead of .Criteria.AddCondition()