Handling Dates with OData v4, EF6 and Web API v2.2

Question

I'm in the midst of upgrading from v1-3 to v4, but I've run into a few problems.

My understanding is that DateTime is unsupported, and I have to always use DateTimeOffset. Fine.

But before I was storing Sql date data type in the DateTime, now it seems I get this error:

Member Mapping specified is not valid. The type 'Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]' of member 'CreatedDate' in type 'MyEntity' is not compatible with 'SqlServer.date[Nullable=False,DefaultValue=,Precision=0]'

What is the work around for this? I need to be able to store specifically just dates in the database (time and locality is not important). Would be great if I could get the Edm.Date aswell as a returned data type, but I didn't have that before.

Thanks.

Edit: Example classes

Before:

public class Ticket
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required, MaxLength(50)]
    public string Reference { get; set; }

    [Column(TypeName = "date")]
    public DateTime LoggedDate { get; set; }
}

After:

public class Ticket
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required, MaxLength(50)]
    public string Reference { get; set; }

    [Column(TypeName = "date")]
    public DateTimeOffset LoggedDate { get; set; }
}

This isn't valid in EF.

Answer 1

One option is to define a new property in the entity. Say Title is mapped to EF:

public partial class Title
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Nullable<System.DateTime> CreatedOn { get; set; }
}

then add a new property of DateTimeOffset:

public partial class Title
{
    [NotMapped]
    public DateTimeOffset? EdmCreatedOn
    {
        // Assume the CreateOn property stores UTC time.
        get
        {
            return CreatedOn.HasValue ? new DateTimeOffset(CreatedOn.Value, TimeSpan.FromHours(0)) : (DateTimeOffset?)null;
        }
        set
        {
            CreatedOn = value.HasValue ? value.Value.UtcDateTime : (DateTime?)null;
        }
    }
}

and the code for generate OData Model looks like:

    public static IEdmModel GetModel()
    {
        ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
        EntityTypeConfiguration<Title> titleType= builder.EntityType<Title>();
        titleType.Ignore(t => t.CreatedOn);
        titleType.Property(t => t.EdmCreatedOn).Name = "CreatedOn";

        builder.EntitySet<Title>("Titles");

        builder.Namespace = typeof(Title).Namespace;

        return builder.GetEdmModel();
    }
}

The controller looks like:

public class TitlesController : ODataController
{
    CustomerManagementSystemEntities entities = new CustomerManagementSystemEntities();

    [EnableQuery(PageSize = 10, MaxExpansionDepth = 5)]
    public IHttpActionResult Get()
    {
        IQueryable<Title> titles = entities.Titles;
        return Ok(titles);
    }

    public IHttpActionResult Post(Title title)
    {
        entities.Titles.Add(title);
        return Created(title);
    }
}

Answer 2

For anyone coming to this in the future, the OData v4 team have fixed this issue.

[Column(TypeName = "date")]
public DateTime Birthday { get; set; }

This will now auto-resolve to Edm.Date.

If you are like me and are doing date type by convention, you have to manually declare the properties as dates lest they be auto-resolved as DateTimeOffset. OData currently does not allow you to add your own conventions.

customer.Property(c => c.Birthday).AsDate();

http://odata.github.io/WebApi/#12-01-DateAndTimeOfDayWithEF