The binary operator LessThan is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.Nullable`1[System.DateTimeOffset]'

Question

I'm trying to create and Odata endpoint but I'm getting this error whenever I try and perform any queries involving dates.

I've re-created it in a very simple example below.

Database Table

EDMX (Snippet)

<edmx:ConceptualModels>
  <Schema Namespace="DataWarehouseModel" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
    <EntityContainer Name="DataWarehouseEntities1" annotation:LazyLoadingEnabled="true">
      <EntitySet Name="People" EntityType="DataWarehouseModel.Person" />
    </EntityContainer>
    <EntityType Name="Person">
      <Key>
        <PropertyRef Name="ID" />
      </Key>
      <Property Name="ID" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
      <Property Name="FirstName" Type="String" Nullable="false" MaxLength="50" FixedLength="false" Unicode="true" />
      <Property Name="LastName" Type="String" Nullable="false" MaxLength="50" FixedLength="false" Unicode="true" />
      <Property Name="DOB" Type="DateTime" Nullable="false" Precision="3" />
    </EntityType>
  </Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
  <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
    <EntityContainerMapping StorageEntityContainer="DataWarehouseModelStoreContainer" CdmEntityContainer="DataWarehouseEntities1">
      <EntitySetMapping Name="People">
        <EntityTypeMapping TypeName="DataWarehouseModel.Person">
          <MappingFragment StoreEntitySet="Person">
            <ScalarProperty Name="DOB" ColumnName="DOB" />
            <ScalarProperty Name="LastName" ColumnName="LastName" />
            <ScalarProperty Name="FirstName" ColumnName="FirstName" />
            <ScalarProperty Name="ID" ColumnName="ID" />
          </MappingFragment>
        </EntityTypeMapping>
      </EntitySetMapping>
    </EntityContainerMapping>
  </Mapping>
</edmx:Mappings>

Model

public partial class Person
{
    [Key]public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public System.DateTime DOB { get; set; }
}

Controller

public class PersonController : ODataController
{
    private DataWarehouseServiceContext db = new DataWarehouseServiceContext();

    private bool PersonExists(int key)
    {
        return db.Persons.Any(p => p.ID == key);
    }

    [EnableQuery]
    public IQueryable<Person> Get()
    {
        return db.Persons;
    }

    protected override void Dispose(bool disposing)
    {
        db.Dispose();
        base.Dispose(disposing);
    }
}

Queries

http://localhost:53205/OData/Person?$top=20&$filter=DOB eq 1972-11-20T00:00:00.000Z
http://localhost:53205/OData/Person?$top=20&$filter=DOB lt 1972-11-20T00:00:00.000Z

etc.

All fail with messages along the lines of :-

The query specified in the URI is not valid. The binary operator LessThan is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.Nullable`1[System.DateTimeOffset]'.

Can anyone help?

Answer 1

The issue here is that while Microsoft has partly added DateTime support (see comments), OData V4 doesn’t include DateTime as primitive type. This means that your filter still needs to use DateTimeOffset.

While not ideal, casting the DateTime to a DateTimeOffset seems to solve the problem.

/OData/Person?$top=20&$filter=cast(DOB,'Edm.DateTimeOffset') eq 1972-11-20T00:00:00.000Z