SQL 'time' type in Entity Framework Code First

Question

I'm trying to create a 'time(7)' column in a table with Entity Framework Code First. This is my Entity:

public class ShiftDetail
{
    public long Id { get; set; }

    [Required]
    public int DayOfWeek { get; set; }

    [Required]
    [Column(TypeName="time")]
    public DateTime StartTime { get; set; }

    [Required]
    [Column(TypeName = "time")]
    public DateTime EndTime { get; set; }

    public long ShiftId { get; set; }
    public virtual Shift Shift { get; set; }
}

As you can see I'm trying to set the database type for the columns StartTime and EndTime to "time" but I get this error:

(112,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' of member 'StartTime' in type 'ShiftDetail' is not compatible with 'SqlServer.time[Nullable=False,DefaultValue=,Precision=7]' of member 'StartTime' in type 'CodeFirstDatabaseSchema.ShiftDetail'.

I've tried also with TypeName="time(7)" but I get this other error:

(104,6) : error 0040: The Type time(7) is not qualified with a namespace or alias. Only primitive types can be used without qualification.

How can I create a time column with code first? (preferably without fluent API)

Thanks in advance.

If you use the EDMX designer, what data type does that pick for your time column? You probably shouldn't be using `DateTime`, but I'm not sure what the right type is, and this is an easy way to find out. — , Aug 29 '12 at 20:36

score 57 · Accepted Answer · edited Sep 27 '16 at 13:03

57

If you want to use Time type in database you will have to use TimeSpan with 24 hour cycle in your application. DateTime is not representation of time.

edited Sep 27 '16 at 13:03

Shaul Behr

36,951
69
249
387

answered Aug 29 '12 at 20:42

Ladislav Mrnka

360,892
59
660
670

3

Actually, when you want to store time of day, there are two good reasons to use DateTime in entities. .NET 4's TimeSpan formatter does not support AM/PM at all, and UI controls like DevExpress's do not bind to TimeSpans for the same reason. Meaning I am forced to use DateTime and am now looking a way to reconcile EF6 with the `time(0)` column I want to use. – jnm2 Nov 13 '13 at 14:02
E.g. http://stackoverflow.com/questions/12078603/need-to-format-timespan-ado-net-datacolumn-as-am-pm – jnm2 Nov 13 '13 at 14:30
1

@jnm2 Just make a NotMapped property that merges the Date and the Time back to a DateTime field for the UI, or do that in your ViewModel. No need to mess up your businesslogic because of UI requirements. – Arwin Aug 10 '16 at 08:14
@Arwin Oh yes, I've done it both ways and on larger projects I have a cleaner domain model separate from the data model. It's just a pain, and I'm looking forward to the potential to use DateTime directly in EF Core. – jnm2 Aug 10 '16 at 11:35
Agreed that an option to just say 'split up my DateTime' in Date and Time fields in the database automatically would be nice, perhaps even linked to the Kind property, when Kind = Local or Unspecified, split it up in Date and Time fields in the database, when Kind = UTC, use DateTime in the database, something like that. – Arwin Aug 10 '16 at 11:58
@LadislavMrnka i want to store HH:MM:SS but when i use TimeSpan it come in HH:MM:PM/AM format,use [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:hh\\:mm\\:ss}")] not worked and convert field in view to simple textbox – AminM Feb 14 '17 at 07:11

SQL 'time' type in Entity Framework Code First

1 Answers1

Linked