Ndtyjky

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?

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();

}

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.

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);