c#entity-frameworkentity-framework-4.1code-firstsqldatatypes

Reputation: 19099

Entity Framework Code First Date field creation

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?

Upvotes: 97

Answers (9)

Irfan Ashraf

Reputation: 2460

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

Upvotes: 19

mialkin

Reputation: 2781

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.

Upvotes: 1

Hakan Fıstık

Reputation: 19421

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

Upvotes: 4

Yakoob Hammouri

Reputation: 125

the Best Way it using The

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

but you must using the EntityFramework v 6.1.1

Upvotes: -3

Stoyan Dimov

Reputation: 5539

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

Upvotes: 12

Richard

Reputation: 30618

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

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.

Upvotes: 24

Tyler Durden

Reputation: 1026

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

Upvotes: 10

David Roth

Reputation: 697

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

Upvotes: 4

Ladislav Mrnka

Reputation: 364259

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

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

Upvotes: 181

Entity Framework Code First Date field creation

Answers (9)

Related Questions