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

Question

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.

Did you set the clock of your computer to something like year 1750 or earlier? — Slauma, Nov 07 '11 at 23:35

score 52 · Accepted Answer · answered Nov 07 '11 at 23:49

52

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.

answered Nov 07 '11 at 23:49

Slauma

175,098
59
401
420

Is not the `DateAdded` property properly initialized in my code example? – Chad Levy Nov 08 '11 at 18:19
1

@Paperjam: It is, but is this *exactly* the code you have tested or is there perhaps another `DateTime` property in your test model which is not initialized? If not, then it's a weird thing. I'm using `datetime` columns quite often and `DateTime` properties are never a problem when initialized with `Now`. I had tested your example yesterday to convince myself again, and it didn't throw an exception. – Slauma Nov 08 '11 at 18:27
1

Doh! Yeah, I've got an uninitialized property. I think I'll start on my repository pattern model so as to avoid silly mistakes like this. Thank you for your help! I'm going to definitely check out more of the Fluent API. – Chad Levy Nov 08 '11 at 18:55
Be careful about the EF version 4.3.1 adds some fixes. See this post for details http://stackoverflow.com/questions/9466677/entity-framework-4-3-code-first-cannot-create-datetime2 – Designpattern Apr 07 '12 at 09:18
1

The annotations do work ! No need to use the modelbuilder – user1029883 Aug 16 '17 at 20:22

score 32 · Answer 2 · answered Feb 26 '12 at 14:14

32

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.

answered Feb 26 '12 at 14:14

Houman

64,245
87
278
460

In my sample code, when I initialize a new `Book` class, I am setting `DateAdded` to `DateTime.Now`. Is not the value effectively populated, or am I missing something? – Chad Levy Feb 27 '12 at 09:31
On second thought, it is. As I recall the problem I was having was with my repository code where the `DateAdded` was not, in fact, being set. And using a nullable field, while useful for preventing oversights from mine from firing off errors, would have not met the requirements of my application. +1 for a good suggestion though. – Chad Levy Feb 27 '12 at 09:35
2

Use a nullable type if that's what you want in your model, but if you don't, you shouldn't use it just so your seed or other initializer works. – RickAndMSFT Apr 08 '13 at 19:55
If i don't want to make it the nullable than i mean requirement of the project to not consider this field nullable. – Ashish-BeJovial Jan 25 '18 at 11:29

score 31 · Answer 3 · edited Sep 04 '15 at 20:49

31

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

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

edited Sep 04 '15 at 20:49

JasCav

34,458
20
113
170

answered Mar 18 '15 at 08:47

strangeoptics

753
8
17

smalldatetime can also be used – Arun Prasad E S Dec 15 '19 at 15:54

score 2 · Answer 4 · answered Feb 09 '16 at 10:06

2

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

answered Feb 09 '16 at 10:06

cmxl

663
12
24

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

4 Answers4

Linked