Pfthb

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