Retrieve N:N relationship Dynamics CRM

Question

I have a relationship between Opportunities and my custom Contract entity in Dynamics 2016 on premise. I am trying to retrieve all of the related contracts from a particular opportunity in a C# plugin. When I try to retrieve the relationships, I receive the error:

No system many-to-many relationship exists between opportunity and ccseq_contract. If attempting to link through a custom many-to-many relationship ensure that you provide the from and to attributes.

It appears that the relationship does exist based on this screenshot:

Here's my Query Expression:

EntityCollection contracts = service.RetrieveMultiple(new QueryExpression()
{
    EntityName = Opportunity.LogicalName,
    ColumnSet = new ColumnSet(new String[]
    {
        Opportunity.Properties.OpportunityId
    }),
    LinkEntities =
    {
        new LinkEntity
        {
            LinkFromEntityName = Opportunity.LogicalName,
            LinkToEntityName = Contract.LogicalName,
            LinkCriteria = new FilterExpression
            {
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                    new ConditionExpression
                    {
                        AttributeName = Opportunity.Properties.OpportunityId,
                        Operator = ConditionOperator.Equal,
                        Values = {wonOpportunity.Id}
                    }
                }
            }
        }
    }
});

Why am I receiving this error and how can I resolve the error?

Answer 1

The LinkedEntity in a query expression is exactly like a SQL inner or outer join (you specify the join type). it's great for fetching a N:1 relationship, it doesn't really work for a N:N.

For the N:N, you need to go via the 'relationship entity'.

If you want all contracts linked to an opportunity, you must retrieve all contacts that has a row linking them to that opportunity, in the 'relationship entity' table, 'ccseq_opportunity_ccseq_contract' (I'm using string constants below, because I don't quite know how you're building your entity classes).

var q = new QueryExpression("ccseq_contract") {
    ColumnSet = new ColumnSet(true), //or specify what fields you want from ccseq_contract
    LinkEntities =  {
        new LinkEntity() {              
            LinkFromEntityName = "ccseq_contract",
            LinkToEntityName = "ccseq_opportunity_ccseq_contract",
            ColumnSet = new ColumnSet(false), //don't fetch any fields from the link table
            LinkCriteria = new FilterExpression() {
                FilterOperator = LogicalOperator.And,
                Conditions = {
                    new ConditionExpression("opportunityid", ConditionOperator.Equal, wonOpportunity.Id)                    
                }
            }
        }
    }       
};

As an aside, when you're not using the 'in' query operator, I would really prefer using LINQ queries instead of query expressions, if you have generated strongly typed entity classes. The LINQ query would look like

using(var ctx = new OrganizationServiceContext(service)) {
    var contracts = (
        from c in ctx.CreateQuery<ccseq_contract>()
        join lnk in ctx.CreateQuery<ccseq_opportunity_ccseq_contract>() on c.ccseq_contractId equals link.ccseq_contractId
        where lnk.opportunityid = wonOpportunity.Id
        select c
        // Or, to fetch only some fields, do 
        // select new { c.ccseq_contractId, c.ccseq_name }
        ).ToList();
}

Answer 2

Here is where I ended up. This was based partially on gnud's answer.

QueryExpression query = new QueryExpression("ccseq_opportunity_ccseq_contract");
query.ColumnSet.AddColumns(Contract.Properties.ContractId, Opportunity.Properties.OpportunityId);
query.Criteria = new FilterExpression();
query.Criteria.AddCondition(Opportunity.Properties.OpportunityId, ConditionOperator.Equal, wonOpportunity.Id);

EntityCollection contracts = service.RetrieveMultiple(query);

Answer 3

Another answer that can make the logic of the query more understandable visually. Think like in sql, first switch to intermediate table, then switch to other table

        QueryExpression query = new QueryExpression("ccseq_contract")
        {
            ColumnSet = new ColumnSet(true),
            LinkEntities =
            {
                new LinkEntity
                {
                    LinkFromEntityName = "ccseq_contract",
                    LinkToEntityName = "ccseq_opportunity_ccseq_contract",
                    LinkFromAttributeName = "ccseq_contractId",
                    LinkToAttributeName = "ccseq_contractId",
                    LinkEntities =
                    {
                        new LinkEntity
                        {
                            LinkFromEntityName = "ccseq_opportunity_ccseq_contract",
                            LinkToEntityName = "opportunity",
                            LinkFromAttributeName = "opportunityid",
                            LinkToAttributeName = "opportunityid",
                            LinkCriteria = new FilterExpression
                            {
                                Conditions =
                                {
                                    new ConditionExpression("opportunityid", ConditionOperator.Equal, wonOpportunity.Id)
                                }
                            }
                        }
                    }
                }
            }
        };

Answer 4

Please try to retrieve list of contracts using the below XML query. The query is done on the N:N relationship.

<fetch  mapping='logical'>
    <entity name='ccseq_opportunity_ccseq_contract'>
        <attribute name='opportunityid'/>
        <attribute name='ccseq_contractid'/>
        <link-entity name='opportunity' to='opportunityid' from='opportunityid' alias='opportunity'>
            <attribute name='opportunityid'/>
            <filter type='and'>
                <condition attribute='opportunityid' operator='eq' value=$'{wonOpportunity.Id}'/>
            </filter>
        </link-entity>
    </entity>
</fetch>

Hope it helps.