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?

Answer 1

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

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

Answer 2

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.

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

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
    }
}

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; }
}

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());
}

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; }

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.

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