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

Answer 3

You can refer to the link below to define your DateTimeAndDateTimeOffsetWrapper to do the translation between two types.

http://www.odata.org/blog/how-to-use-sql-spatial-data-with-wcf-odata-spatial/

Define two properties on your model, one is DateTime which only exists in the Edm model, the other is DateTimeOffset which only exists in the DB.

---

If the solution above doesn't meet your request, you have to change the data to DateTime before saving it to database and change it back to DateTimeOffset after retrieving it from database in the controller actions.

You can define two almost-same classes to achieve this. The only difference is that one has DateTime property and the other has DateTimeOffset property.

The former one is used for EF and mapping into DB.

The latter one is used for defining OData Edm model and presenting to the users.

As I said above, you have to do the translation between these two classes before saving the data and after retrieving the data.

Answer 4

You can add the AppendDatetimeOffset method to add automatically the methods using the microsoft T4 engine (i.e. updating the template file *.tt). So that when regenerating the code, you don't have to append classes again. Hope this Helps :)

public string Property(EdmProperty edmProperty)
    {
        return string.Format(
            CultureInfo.InvariantCulture,
            (_ef.IsKey(edmProperty) ? "[Key]" : "") +
            "{0} {1} {2} {{ {3}get; {4}set; }}  {5}",           
            Accessibility.ForProperty(edmProperty),
            _typeMapper.GetTypeName(edmProperty.TypeUsage),
            _code.Escape(edmProperty),
            _code.SpaceAfter(Accessibility.ForGetter(edmProperty)),
            _code.SpaceAfter(Accessibility.ForSetter(edmProperty)),
            AppendDateTimeOffset(edmProperty));
    }

    public string AppendDateTimeOffset(EdmProperty edmProperty){

     if(!_typeMapper.GetTypeName(edmProperty.TypeUsage).Contains("DateTime")) return " ";
     //proceed only if date time
     String paramNull = @"public Nullable<System.DateTimeOffset> edm{0} 
                        {{
                            get
                            {{
                                return {0}.HasValue ? new DateTimeOffset({0}.Value, TimeSpan.FromHours(0)) : (DateTimeOffset?)null;
                            }}
                        }}"; 

    String paramNotNull = @"public System.DateTimeOffset edm{0} 
                        {{
                            get
                            {{
                                return new DateTimeOffset({0}, TimeSpan.FromHours(0));
                            }}
                        }}"; 

        String s= String.Empty;
        if(edmProperty.Nullable){
        s = string.Format(paramNull, edmProperty.Name);     
        }else
        {
        s = string.Format(paramNotNull, edmProperty.Name);      
        }
        return s;

    }