Using DateTime properties in Code-First Entity Framework and SQL Server

I have an example class book:

public class Book
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DateAdded { get; set; }
}

When I attempt to add a new book to the BookDb context...

using (BookDb db = new BookDb())
{
    Book book = new Book {
        Name = "Some name",
        DateAdded = DateTime.Now
    };

    db.Books.Add(book);
    db.SaveChanges();
}

... an error is thrown:

System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

I've found that the cause of this is the incompatible datetime types between .NET and SQL Server. There is a way to tell EF to use SQL Server's format in traditional Entity Framework, but how do I do it in Code-First Entity Framework?

I am using EF4 on .NET 4 (MVC 3 web app) and SQL Server 2008 Express.

Solution 1:

You can specify the type in Fluent API:

modelBuilder.Entity<Book>()
    .Property(f => f.DateTimeAdded)
    .HasColumnType("datetime2");

This creates a datetime2(7) column in the database. If you want to finetune the precision you can use:

modelBuilder.Entity<Book>()
    .Property(f => f.DateTimeAdded)
    .HasColumnType("datetime2")
    .HasPrecision(0);

... for a datetime2(0) column in the DB.

However, the code you have shown in your question works because the datetime type allows to store dates back to around 1750. The exception occurs only for earlier dates. A common reason for this exception is an uninitialized DateTime property because it represents the year 0001 which can't be stored in a datetime column in SQL Server.

There is no corresponding attribute to define this with data annotations. It's only possible with Fluent API.

Solution 2:

When saving a date it needs to have a value populated. Thats why you get this error.

Just use DateTime?. There is no need for the magic above.

Solution 3:

You can annotate the attribute of your class with the type datetime2.

public class Book
{
    [Column(TypeName = "datetime2")]
    public DateTime DateAdded { get; set; }
}

Solution 4:

If migrations are enabled you can also adjust stuff right there.

public override void Up()
{
    CreateTable(
        "dbo.MyTable",
        c => new
        {
            Date = c.DateTime(false, 7, storeType: "datetime2"),
        });
}