CODEWITHSUNDEEP
phpsoapdynamics-crmdynamics-365dynamics-crm-365
Christoph Burschka
Christoph Burschka

Reputation: 4689

How do I query an n:n relationship from MS Dynamics CRM via SOAP?

I need to add some linked entities to a SOAP query on a Dynamics 365 CRM (the version it reports is Version 1612 (8.2.1.176) (DB 8.2.1.176)). The client implementation is in PHP.

With some trial and error and a lot of examples, I have been able to put together something like the following request body. (I'm not sure if this is the best way to structure a query; there seem to be several others including <fetch>, but this one has worked so far.)

<Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:si="http://www.w3.org/2001/XMLSchema-instance">
  <request xmlns:c="http://schemas.microsoft.com/xrm/2011/Contracts" si:type="c:RetrieveMultipleRequest">
    <c:Parameters xmlns:c2="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
      <c:KeyValuePairOfstringanyType>
        <c2:key>Query</c2:key>
        <c2:value si:type="c:QueryExpression">
          <c:ColumnSet>
            <c:AllColumns>true</c:AllColumns>
            <c:Columns/>
          </c:ColumnSet>
          <c:EntityName>entity1</c:EntityName>
          <c:LinkEntities>
            <c:LinkEntity>
              <c:LinkFromAttributeName>entity2id</c:LinkFromAttributeName>
              <c:LinkFromEntityName>entity1</c:LinkFromEntityName>
              <c:LinkToAttributeName>entity2id</c:LinkToAttributeName>
              <c:LinkToEntityName>entity2</c:LinkToEntityName>
              <c:JoinOperator>Inner</c:JoinOperator>
              <c:LinkEntities>
                <c:LinkEntity>
                  <c:LinkFromAttributeName>entity3id</c:LinkFromAttributeName>
                  <c:LinkFromEntityName>entity2</c:LinkFromEntityName>
                  <c:LinkToAttributeName>entity3id</c:LinkToAttributeName>
                  <c:LinkToEntityName>entity3</c:LinkToEntityName>
                  <c:JoinOperator>Inner</c:JoinOperator>
                </c:LinkEntity>
              </c:LinkEntities>
            </c:LinkEntity>
          </c:LinkEntities>
          <c:Orders>
            <c:OrderExpression>
              <c:AttributeName>name</c:AttributeName>
              <c:OrderType>Ascending</c:OrderType>
            </c:OrderExpression>
          </c:Orders>
          <c:PageInfo>
            <c:Count>1</c:Count>
            <c:PageNumber>1</c:PageNumber>
          </c:PageInfo>
        </c2:value>
      </c:KeyValuePairOfstringanyType>
    </c:Parameters>
    <c:RequestName>RetrieveMultiple</c:RequestName>
  </request>
</Execute>

(In this case, the relationship seems to be 1:n from entity1 to entity2, and n:1 from entity2 to entity3. In effect, entity2 contains only the foreign keys for joining entity1 and entity3, with some extra attributes.)

This query is apparently executed correctly (it runs without error, and it limits the result set according to the inner join), but I only get back columns from the first entity type.

How do I actually get the attributes of the joined entities?

Edit:

I have tried adding each of the following to the <c:LinkEntity> element:

<c:ColumnSet>
  <c:AllColumns>true</c:AllColumns>
</c:ColumnSet>

<c:Columns>
  <c:AllColumns>true</c:AllColumns>
</c:Columns>

<c:Columns>
  <a:string xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    attribute1
  </a:string>
</c:Columns>

<c:ColumnSet>
  <c:Columns>
    <a:string xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
      attribute1
    </a:string>
  </c:Columns>
</c:ColumnSet>

Unfortunately, while none of these have caused an error, they also haven't changed the output.

Upvotes: 2

Views: 256

Answers (1)

Arun Vinoth PrecogTechnologies
Arun Vinoth PrecogTechnologies

Reputation: 22836

Placing the Columns element like below worked.

<Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:si="http://www.w3.org/2001/XMLSchema-instance">
  <request xmlns:c="http://schemas.microsoft.com/xrm/2011/Contracts" si:type="c:RetrieveMultipleRequest">
    <c:Parameters xmlns:c2="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
      <c:KeyValuePairOfstringanyType>
        <c2:key>Query</c2:key>
        <c2:value si:type="c:QueryExpression">
          <c:ColumnSet>
            <c:AllColumns>true</c:AllColumns>
            <c:Columns/>
          </c:ColumnSet>
          <c:EntityName>entity1</c:EntityName>
          <c:LinkEntities>
             <c:LinkEntity>

              <c:Columns>
                <c:AllColumns>true</c:AllColumns>
              </c:Columns>

              <c:LinkFromAttributeName>entity2id</c:LinkFromAttributeName>
              <c:LinkFromEntityName>entity1</c:LinkFromEntityName>
              <c:LinkToAttributeName>entity2id</c:LinkToAttributeName>
              <c:LinkToEntityName>entity2</c:LinkToEntityName>
              <c:JoinOperator>Inner</c:JoinOperator>
              <c:LinkEntities>
                <c:LinkEntity>
                  <c:LinkFromAttributeName>entity3id</c:LinkFromAttributeName>
                  <c:LinkFromEntityName>entity2</c:LinkFromEntityName>
                  <c:LinkToAttributeName>entity3id</c:LinkToAttributeName>
                  <c:LinkToEntityName>entity3</c:LinkToEntityName>
                  <c:JoinOperator>Inner</c:JoinOperator>
                </c:LinkEntity>
              </c:LinkEntities>
            </c:LinkEntity>
          </c:LinkEntities>
          <c:Orders>
            <c:OrderExpression>
              <c:AttributeName>name</c:AttributeName>
              <c:OrderType>Ascending</c:OrderType>
            </c:OrderExpression>
          </c:Orders>
          <c:PageInfo>
            <c:Count>1</c:Count>
            <c:PageNumber>1</c:PageNumber>
          </c:PageInfo>
        </c2:value>
      </c:KeyValuePairOfstringanyType>
    </c:Parameters>
    <c:RequestName>RetrieveMultiple</c:RequestName>
  </request>
</Execute>

Upvotes: 1

Related Questions