Is that possible to make cast field in fetchxml?

Question

Is that possible to make cast field from identifier to string in fetchxml? I have this query:

select *
from table1 t1 left outer join table2 t2
on t1.stringId=CAST( t2.id as varchar(40))
where t2.id is null and t1.childid is not null

and this row t1.stringId=CAST( t2.id as varchar(40)) can't make in fetchxml while field "stringId" is string but it's value is GUID and I want to have that equality.

In fetchxml:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
  <entity name="table1 " >
    <attribute name="stringid" />
    <attribute name="te_name" />
    <link-entity name="table1" from="id" to="stringid" link-type="outer" alias="s" />
    <filter type="and" >
      <condition entityname="s" attribute="stringid" operator="null" />
    </filter>
  </entity>
</fetch>

Answer 1

I don't know if I am missing something, but Dynamics CRM/365 is quite forgiving when it comes to joining types. It is quite possible to join a string field to a guid field. See this sample, where I have placed a guid of an account in the fax attribute of a contact:

<fetch version='1.0' output-format='xml-platform' mapping='logical' >
  <entity name='contact' >
    <attribute name='fullname' />
    <attribute name='fax' />
    <attribute name='parentcustomerid' />
    <filter type='and' >
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <attribute name='contactid' />
    <link-entity name='account' from='accountid' to='fax' link-type='outer' >
      <attribute name='name' />
    </link-entity>
  </entity>
</fetch>

So yes, you are correct that casting is not possible, but the query in your example still executes nicely, which I guess was the goal of it all.

Answer 2

Casting is not possible in fetchxml. (Am unable to test this right away.)

Link entity join the 2 entities on Key GUIDs (Primary & Foreign Keys), it's better to create the relationship to maintain Database design & query them.

Interestingly, MSDN is saying Id as string in schema:

<!--

link-entity element - used for joining one entity to its "parent"

-->
  <!-- [XDR-XSD] "link-entity" element  -->
  <xs:complexType name="FetchLinkEntityType">
    <xs:choice minOccurs="0"
               maxOccurs="unbounded">
      <!-- -->
      <xs:element ref="all-attributes"
                  minOccurs="0" />
      <xs:element name="attribute"
                  type="FetchAttributeType"
                  minOccurs="0"
                  maxOccurs="unbounded" />
      <xs:element name="order"
                  type="FetchOrderType"
                  minOccurs="0"
                  maxOccurs="1" />
      <xs:element ref="filter"
                  minOccurs="0" />
      <xs:element name="link-entity"
                  type="FetchLinkEntityType" />
    </xs:choice>
    <!-- -->
    <xs:attribute name="name"
                  use="required"
                  type="xs:string"></xs:attribute>
    <xs:attribute name="to"
                  type="xs:string"></xs:attribute>
    <xs:attribute name="from"
                  type="xs:string"></xs:attribute>
    <xs:attribute name="alias"
                  type="xs:string"></xs:attribute>
    <xs:attribute name="link-type"
                  type="xs:string"></xs:attribute>
    <xs:attribute name="visible"
                  type="xs:boolean"></xs:attribute>
    <xs:attribute name="intersect"
                  type="xs:boolean"></xs:attribute>
  </xs:complexType>

Update:

Natively, fetchxml uses the guid keys to join (ex. From Advanced find). But it seems we can override that from and to with any field. It’s working as of today, Probably undocumented henceforth unsupported.