Column 'createdon' does not allow DBNull.Value.

[fowl2]

Somehow, I've got an null createdon, and SQL 4 CDS does not like it.

I can't select that column on any row without receiving "Column 'createdon' does not allow DBNull.Value."

select top 1 
createdon
 from solutioncomponent
 where createdon is null

<fetch xmlns:generator='MarkMpn.SQL4CDS' top='1'>
  <entity name='solutioncomponent'>
    <attribute name='createdon' />
    <filter>
      <condition attribute='createdon' operator='null' />
    </filter>
  </entity>
</fetch>

XrmToolBox 1.2023.12.68
SQL4CDS 9.1.0.0 (happened in 9.0 as well, not sure about previously)

[fowl2]

Can trace it down to here, where it seems that createdon is hardcoded as not nullable:

Sql4Cds/MarkMpn.Sql4Cds.Engine/ExecutionPlan/FetchXmlScan.cs

Line 1281 in d4f1403

var notNull = innerJoin && (attrMetadata.LogicalName == entityMetadata.PrimaryIdAttribute || attrMetadata.LogicalName == "createdon");

Looks like this has recently been a problem with createdby as well: 5c688bc

But really, the question I'm asking myself is why we're performing schema validation on results at all? Is it to ensure the integrity of any optimization of operations/joins/updates etc?

[MarkMpn]

Thanks!

For background:

Internally we can use the nullability of a column to apply different optimizations, and we also expose the schema information from the Sql4CdsDataReader so the consuming application, whether that's the SQL 4 CDS tool itself or other applications, can make use of it as necessary.

In the XrmToolBox tool, the results are loaded into a DataTable that is then bound to the grid for display. The DataTable uses the exposed schema information to apply constraints on its own columns, which then produces this error when the data is loaded.

[MarkMpn]

Fixed in #493