Entity Framework Code First: which DataType attribute for DateTime2?

Question

Sometime when using Entity Framework Code First, the default conventions do not create the database type you want. For instance by default a property of type System.DateTime creates a database column of type DateTime. What to do if you want it to have a type of datetime2 (the DateTime type that has no problems with time zones and daylight saving time)?

It is possible to specify the required database type with Data Annotations using a DataTypeAtrribute. One of the constructors of DataTypeAttribute accepts a parameter DataType Enumeration. So one could specify something like:

[DataType(DataType.DateTime)]
public DateTime DateOfBirth {get; set;}

The DataType enumeration type contains a lot of types, however it is missing a value for DateTime2.

Another approach would be using Fluent API. Create a DateTime2 in method DBContext.OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>().Property(p => p.BirthDate)
        .HasColumnType("datetime2");
}

The DataTypeAttribute has a second constructor that accepts a string. This string is defined as

The name of the custom field template to associate with the data field.

So one would assume that the following would be enough to create a datetime2:

[DataType("datetime2")]
public DateTime DateOfBirth {get; set;}

Alas, this doesn't work. The created column still has the DateTime format.

Question: which string to use in the constructor to create a datetime2?

Answer 1

The DataType attribute is not used for column type mapping for Code First:

The Column annotation is a more adept in specifying the attributes of a mapped column. You can stipulate a name, data type or even the order in which a column appears in the table. [...] Don’t confuse Column’s TypeName attribute with the DataType DataAnnotation. DataType is an annotation used for the UI and is ignored by code first.

So:

[Column(TypeName="datetime2")] 

Answer 2

For those still interested in how to define column types for properties. From EF version 6.0 onwards you can define that every value of some type should have some database type.

This is done In DbContext.OnModelCreating using DbModelBuilder.Properties.

If you do this, you don't have to write attributes nor fluent API for every DateTime. It is easier to be consistent and let all DateTime have the same column type. Similarly you can give all decimals the same precision, even if decimals are added in future.

Suppose you want to define that every System.DateTime should have column type DateTime2; every System.Decimal should have a column type with a specified precision. In DbContext you would write:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // every property of type DateTime should have a column type of "datetime2":
    modelBuilder.Properties<DateTime>()
      .Configure(property => property.HasColumnType("datetime2"));
    // every property of type decimal should have a precision of 19
    // and a scale of 8:
    modelBuilder.Properties<decimal>()
        .Configure(property => property.HasPrecision(19, 8));
}