Entity Framework Code First Date field creation

Question

I am using Entity Framework Code First method to create my database table. The following code creates a DATETIME column in the database, but I want to create a DATE column.

[DataType(DataType.Date)]
[DisplayFormatAttribute(ApplyFormatInEditMode = true, DataFormatString = "{0:d}")]
public DateTime ReportDate { get; set; }

How can I create a column of type DATE, during table creation?

Topic

Answer 1

The EF6 version of David Roth's answer is as follows:

public class DataTypePropertyAttributeConvention 
    : PrimitivePropertyAttributeConfigurationConvention<DataTypeAttribute>
{
    public override void Apply(ConventionPrimitivePropertyConfiguration configuration, 
        DataTypeAttribute attribute)
    {
        if (attribute.DataType == DataType.Date)
        {
            configuration.HasColumnType("Date");
        }
    }
}

Register this as before:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     base.OnModelCreating(modelBuilder);

     modelBuilder.Conventions.Add(new DataTypePropertyAttributeConvention());
}

This has the same outcome as Tyler Durden's approach, except that it's using an EF base class for the job.

Dunghill

Answer 2

Try to use ColumnAttribute from System.ComponentModel.DataAnnotations (defined in EntityFramework.dll):

[Column(TypeName="Date")]
public DateTime ReportDate { get; set; }

Responsible

You may get error, Sequence contains no matching element, if you are pointing at a DB that does not support date. SQL Server 2014 does. – Saucy

In EF6 you need using System.ComponentModel.DataAnnotations.Schema; – Womanhater

Answer 3

I use following

[DataType(DataType.Time)]
public TimeSpan StartTime { get; set; }

[DataType(DataType.Time)]
public TimeSpan EndTime { get; set; }
    
[DataType(DataType.Date)]
[Column(TypeName = "Date")]
public DateTime StartDate { get; set; }

[DataType(DataType.Date)]
[Column(TypeName = "Date")]
public DateTime EndDate { get; set; }

With Entity Framework 6 & SQL Server Express 2012 - 11.0.2100.60 (X64).
It works perfectly and generates time/date column types in SQL server

Counterproof

Your solution worked. @YakoobHammouri's did not work. I had to use both annotations [DataType(DataType.Date)] and [Column(TypeName = "Date")] as you suggested. However this solution created date format as yyyy-mm-dd as opposed to MM-dd-yyyy – Kacikacie

Answer 4

If you prefer not to decorate your classes with attributes, you can set this up in the DbContext's OnModelCreating like this:

public class DatabaseContext: DbContext
{
    // DbSet's

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // magic starts
        modelBuilder.Entity<YourEntity>()
                    .Property(e => e.ReportDate)
                    .HasColumnType("date");
        // magic ends

        // ... other bindings
    }
}

Ingroup

Answer 5

I found this works in EF6 nicely.

I created a convention for specifying my data types. This convention changes the default DateTime data type in the database creation from datetime to datetime2. It then applies a more specific rule to any properties that I have decorated with the DataType(DataType.Date) attribute.

public class DateConvention : Convention
{
    public DateConvention()
    {
        this.Properties<DateTime>()
            .Configure(c => c.HasColumnType("datetime2").HasPrecision(3));

        this.Properties<DateTime>()
            .Where(x => x.GetCustomAttributes(false).OfType<DataTypeAttribute>()
            .Any(a => a.DataType == DataType.Date))
            .Configure(c => c.HasColumnType("date"));
    }
}

Then register then convention in your context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    modelBuilder.Conventions.Add(new DateConvention());
    // Additional configuration....
}

Add the attribute to any DateTime properties that you wish to be date only:

public class Participant : EntityBase
{
    public int ID { get; set; }

    [Required]
    [Display(Name = "Given Name")]
    public string GivenName { get; set; }

    [Required]
    [Display(Name = "Surname")]
    public string Surname { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "Date of Birth")]
    public DateTime DateOfBirth { get; set; }
}

Vitriol

Really great solution! One thing i want to add to be completely clear, that in order to use this you need to add the attribute on the date property e.g. [DataType(DataType.Date)] public DateTime IssueDate { get; set; } – Authorization

@StephenLautier yes you must add the attribute if you wish to be able to use this for only specific DateTime properties. In the code I have added, I show that you can also apply a general rule to all your DateTime types and then a more specific rule to those with the DataType(DataType.Date) decoration. Hope this clears up any confusion. – Vitriol

Good solution, but EF does provide a base class to do this job for you - see my solution for details. – Dunghill

@Dunghill I up-voted your solution, because you are correct. The reason I created my solution though was because EF defaults DateTime properties to use datetime data type in SQL Server, not the datetime2 data type which is compatible with the .NET DateTime property. Thankfully EF core has fixed this issue now. – Vitriol

Answer 6

Beside using ColumnAttribute you can also create a custom attribute convention for the DataTypeAttribute:

public class DataTypePropertyAttributeConvention : AttributeConfigurationConvention<PropertyInfo, PrimitivePropertyConfiguration, DataTypeAttribute>
{
    public override void Apply(PropertyInfo memberInfo, PrimitivePropertyConfiguration configuration, DataTypeAttribute attribute)
    {
        if (attribute.DataType == DataType.Date)
        {
            configuration.ColumnType = "Date";
        }
    }
}

Just register the convention in your OnModelCreating method:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     base.OnModelCreating(modelBuilder);

     modelBuilder.Conventions.Add(new DataTypePropertyAttributeConvention());
}

Fealty

Unfortunately this has been deprecated as of EF6 and is no longer valid. – Vitriol

EF6 did provide a new way of doing this - see my solution for details. – Dunghill

Answer 7

This is just an enhancement for the most up-voted answer by @LadislavMrnka on this question

if you have a lot of Date columns, then you can create custom attribute and then use it when ever you want, this will produce more clean code in the Entity classes

public class DateColumnAttribute : ColumnAttribute
{
    public DateColumnAttribute()
    {
        TypeName = "date";
    }
}

Usage

[DateColumn]
public DateTime DateProperty { get; set; }

Exemplification

Answer 8

This has worked for EF Core 5 + PostgreSQL:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Birthday
{
    [Key]
    public int Id { get; set; }
    
    [Column(TypeName = "date")]
    public DateTime DateOfBirth { get; set; }
}

See column data types.

Kirby

Answer 9

the Best Way it using The

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

but you must using the EntityFramework v 6.1.1

Mcburney