Unable to cast object of type 'System.Guid' to type 'System.Data.SqlTypes.INullable'

[edgeerrol87]

Hi,

I use the version 9.1.0 and when I try to run this query

SELECT DISTINCT c.new_contractid, c.new_name,
CASE
    WHEN cc.fullname = pc.fullname AND ccp.new_name = pcp.new_name THEN '1'
    WHEN cc.fullname = pc.fullname AND cc.birthdate = pc.birthdate AND cc.telephone1 = pc.telephone1 THEN '2'
    WHEN cc.fullname = pc.fullname AND cc.birthdate = pc.birthdate AND cc.gendercode = pc.gendercode THEN '3'
    ELSE '0'
END AS Flag
FROM new_customercontractrelation AS ccr
    INNER JOIN new_contract AS c ON ccr.new_contractid = c.new_contractid
        INNER JOIN new_producercontractrelation AS pcr ON c.new_contractid = pcr.new_contractid
            INNER JOIN new_producer AS p ON pcr.new_producerid = p.new_producerid
                INNER JOIN contact AS pc ON p.new_contactid = pc.contactid
                    LEFT JOIN new_profile AS pcp ON pc.new_profileid = pcp.new_profileid
    INNER JOIN contact AS cc ON ccr.new_customerid = cc.contactid
        LEFT JOIN new_profile AS ccp ON cc.new_profileid = ccp.new_profileid
WHERE   ccr.new_role = 100000000
    AND c.new_contractid IN (SELECT new_contractid FROM new_contract WHERE modifiedon = yesterday() AND statuscode = 100000000)

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='new_customercontractrelation'>
    <attribute name='new_customercontractrelationid' />
    <link-entity name='new_contract' to='new_contractid' from='new_contractid' alias='c' link-type='inner'>
      <attribute name='new_contractid' />
      <attribute name='one_name' />
      <link-entity name='new_producercontractrelation' to='new_contractid' from='new_contractid' alias='pcr' link-type='inner'>
        <attribute name='new_producercontractrelationid' />
        <link-entity name='new_producer' to='new_producerid' from='new_producerid' alias='p' link-type='inner'>
          <attribute name='new_producerid' />
          <link-entity name='contact' to='new_contactid' from='contactid' alias='pc' link-type='inner'>
            <attribute name='fullname' />
            <attribute name='birthdate' />
            <attribute name='telephone1' />
            <attribute name='gendercode' />
            <attribute name='contactid' />
            <link-entity name='new_profile' to='new_profileid' from='new_profileid' alias='pcp' link-type='outer'>
              <attribute name='one_name' />
              <attribute name='new_profileid' />
              <order attribute='new_profileid' />
            </link-entity>
            <order attribute='contactid' />
          </link-entity>
          <order attribute='new_producerid' />
        </link-entity>
        <order attribute='new_producercontractrelationid' />
      </link-entity>
      <link-entity name='new_contract' to='new_contractid' from='new_contractid' alias='Expr1' link-type='inner'>
        <attribute name='new_contractid' />
        <filter>
          <condition attribute='modifiedon' operator='yesterday' />
          <condition attribute='statuscode' operator='eq' value='100000000' />
        </filter>
        <order attribute='new_contractid' />
      </link-entity>
      <order attribute='new_contractid' />
    </link-entity>
    <link-entity name='contact' to='new_customerid' from='contactid' alias='cc' link-type='inner'>
      <attribute name='fullname' />
      <attribute name='birthdate' />
      <attribute name='telephone1' />
      <attribute name='gendercode' />
      <attribute name='contactid' />
      <link-entity name='new_profile' to='new_profileid' from='new_profileid' alias='pcp' link-type='outer'>
        <attribute name='new_name' />
        <attribute name='new_profileid' />
        <order attribute='new_profileid' />
      </link-entity>
      <order attribute='contactid' />
    </link-entity>
    <filter>
      <condition attribute='new_role' operator='eq' value='100000000' />
    </filter>
    <order attribute='new_customercontractrelationid' />
  </entity>
</fetch>

I have the following error:

Msg 10337, Level 16, State 1, Line 1
Unable to cast object of type 'System.Guid' to type 'System.Data.SqlTypes.INullable'.

See the Execution Plan tab for details of where this error occurred

I found a problem in line 747-748 of the Sql4Cds/MarkMpn.Sql4Cds.Engine/ExecutionPlan/FetchXmlScan.cs file with key Expr1.new_contractid

[MarkMpn]

I'm struggling to reproduce this error. Can you simplify your query to use only standard entity types and still get the same error? So far I can't get it to generate the <attribute> and <order> elements within the Expr1 link entity. Can you also share the results of SELECT @@VERSION?

[edgeerrol87]

I'm struggling to reproduce this error. Can you simplify your query to use only standard entity types and still get the same error? So far I can't get it to generate the <attribute> and <order> elements within the Expr1 link entity. Can you also share the results of SELECT @@VERSION?

The result of SELECT @@VERSION is

Microsoft Dataverse - 9.2.24061.186
    SQL 4 CDS - 9.1.0.0
    Jun 11 2024 21:15:27
    Copyright © 2020 - 2024 Mark Carrington

The simplify query is

SELECT DISTINCT 
    c.contactid,
    CASE
        WHEN c.lastname = pc.lastname THEN 1
        WHEN c.mobilephone = pc.mobilephone THEN 2
        ELSE 0 
    END AS flag
FROM lead AS l
INNER JOIN contact AS c ON l.customerid = c.contactid
LEFT JOIN account AS ca ON c.contactid = ca.primarycontactid
INNER JOIN contact AS pc ON l.parentcontactid = pc.contactid
LEFT JOIN account AS pca ON c.contactid = pca.primarycontactid
WHERE c.contactid IN (SELECT contactid FROM contact WHERE modifiedon = today())

[MarkMpn]

Thanks, that’s given me what I need to reproduce it, I should be able to get this fixed for the next release. In the meantime you should be able to avoid the error by rewriting your WHERE clause without the IN:

WHERE   ccr.new_role = 100000000
    AND c.modifiedon = yesterday()
        AND c.statuscode = 100000000