How to map `time(0)` to `DateTime` with Entity Framework 6

Question

Is there any way a custom mapping rule could be injected using Entity Framework 6's new dependency pattern?

The database I am working with has times of day stored in a few time(0) columns.

According to MSDN, the time data type defines a particular “time of a day”, as opposed to an arbitrary length of time. This is evidenced by the range of the time type, 00:00:00.0000000 through 23:59:59.9999999.

All is well here. However, Entity Framework refuses to map it to a DateTime property.

The mapping to the ADO.NET default of TimeSpan is far from ideal (as Jon Skeet pointed out) because SQL Server's time is not an arbitrary span of time but has AM/PM context. .NET's TimeSpan formatter has no concept of AM/PM. time and TimeSpan are for separate problem domains. From a purely idealistic point of view, I would rather always map time to DateTime. That also makes sense because of the fixed starting point as a time of day.

Worse, because of these separate problem domains of time and TimeSpan, controls like DevExpress's TimeEdit will only bind to DateTime properties. I am hoping to avoid dirtying all my entities with duplicated properties that simply translate between TimeSpan and DateTime.

(The author here asked the same question but had fewer requirements: SQL 'time' type in Entity Framework Code First)

Answer 1

Your problem looks very like the example used to change the convention for DateTimes on this page: Custom Code First Conventions (EF6 onwards)

Something like this perhaps:

public class TimeSpanConvention : Convention
{
    public TimeSpanConvention()
    {
        this.Properties<TimeSpan>()
            .Configure(c => c.HasColumnType("datetime"));        
    }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new TimeSpanConvention());
}

EDIT Just re-read your question, and I see that you want DateTime in the C# code and time(0) in the database not the other way around so that won't work. Unfortunately, I suspect you will need 2 fields like this solution Convert value when mapping - which does keep the database property private.

What about the original idea of a custom coding convention but based on a naming convention:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Properties<DateTime>()
                .Where(p => p.Name.EndsWith("TimeOfDay"))
                .Configure(p => p.HasColumnType("time(0)"));
}

EDIT 2 And since that is an invalid mapping that leaves us with little choice:

In the Model:

private string DBTimeOfDay { get; set; }

[System.ComponentModel.DataAnnotations.Schema.NotMapped]
public DateTime TimeOfDay
{
    get { return new DateTime(DBTimeOfDay.Ticks); }
    set { DBTimeOfDay= value.TimeOfDay; }
}

And in the Context class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{      modelBuilder.Types().Configure(c =>
   {
      var properties = c.ClrType.GetProperties(BindingFlags.NonPublic 
                                         | BindingFlags.Instance)
                             .Where(p => p.Name == "DBTimeOfDay");
       foreach (var p in properties)
           c.Property(p).HasColumnName("TimeOfDay");
   });
}

Another option is to create a database view that converts time(0) to DateTime in its sql, and map to modified stored procedures for updates. That technique has been suggested for unsupported data types

EDIT 3 ! Having slept on this...here are my thoughts.

time(0) represents a time of day.

Entity Framework has chosen to convert it to a Timespan.

Timespans are supposed to represent durations.

Timespans are easily added to DateTimes with the result being a new DateTime

C# does not have a TimeOfDay data type

So in your business logic either you have to hold time of day' as a TimeSpan that never exceeds 24 hours or as a DateTime where you have set the date part to an arbitrary value that you then ignore.

EF is consistent with C# where DateTime.TimeOfDay returns a Timespan

If you are doing any arithmetic in the business logic, say for recurring appointments or such, then I think there may actually be a marginally stronger argument for TimeSpan. If it's complicated then maybe you should start using Noda time

The problem comes when it moves to the UI. TimeEdit needs a DateTime...We want to display it as a.m./p.m...

So that is where I would map it to a DateTime i.e. in the UI. I'm using MVC. I'm using ViewModels. I'm already mapping from Entities. I'm using AutoMapper. It's easy and it keeps the Entities clean. TimeSpan properties in the Domain converted to DateTime for UI..