0

Using Code First technique with EF6

When I declare my DateTime properties like this:

Public Property StartDate As DateTime

I get this error:

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.

When I change my declaration to this:

Public Property StartDate As Nullable(Of DateTime)

Everything is fine.

What I don't understand is why. I have seen in other SO posts that we need to use DateTime2 with SQL, but I don't know what that means. It's not a type in code. Is it something else I should be setting up?

Am I taking a shortcut using the nullable declaration or is that automatically changing to datetime2 during the EF processes?

Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
Alan
  • 1,587
  • 3
  • 23
  • 43
  • 1
    Nullable type just remedy the problem when `DateTime` property has not been set (it defaults to null instead of `DateTime.MinValue` which the problem originates), if you really want to use non-nullable `DateTime` property in Code First then you can configure `HasColumnType("datetime2")` explicitly with `DbModelBuilder`. Note that EF handles `DateTime` .NET properties as `datetime2` data type by default. – Tetsuya Yamamoto Dec 22 '17 at 00:38
  • Thx for explaining why my problem was solved w/o any other actions. Regarding DbModelBuilder, I'm not familiar with that and the links I found don't explain it to me. Do you have some favorite links with examples? It also seems like you're saying EF `is` already using datetime2 and I don't need to take anymore action, or did I misunderstand? – Alan Dec 22 '17 at 04:33
  • 2
    The `DateTime` structure in .NET is equivalent to `datetime2` in SQL Server (using same minimum value, i.e. `0001-01-01 00:00:00`). On the other hand, `datetime` in SQL Server uses different minimum value, hence 'out-of-range' value occurs when non-nullable `DateTime` with its default value inserted to `datetime` column (nullable `DateTime` can be mapped to `NULL`, which is default value to nullable column in SQL). – Tetsuya Yamamoto Dec 22 '17 at 05:49
  • 1
    @Alan: Check out https://stackoverflow.com/questions/8043816/using-datetime-properties-in-code-first-entity-framework-and-sql-server/8044310#8044310 and/or https://www.mikesdotnetting.com/article/229/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-resulted-in-an-out-of-range-value – dropoutcoder Dec 22 '17 at 22:19
  • @cloudikka So if we use the `Nullable` option, is it the case that SQL Server allows a null date, but if there's a date, it just has to be greater than Jan 1st 1753? So there's `gap` of storable values when not using datetime2? – Alan Dec 26 '17 at 22:17

1 Answers1

1

You might want to create custom EF convention and plug it to instruct Entity Framework how to deal with every single property of DateTime.

Create following convention class.

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

Then add custom convention to convention collection overriding OnModelBuilder method in class inheriting DbContext.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // existing code, if any

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

You might want to take a look at Entity Framework Custom Code First Conventions (EF6 onwards) for more info.

dropoutcoder
  • 2,627
  • 2
  • 14
  • 32
  • Didn't understand the 1st comment above. But I believe this is my answer. For the current project I will continue with the `Nullable` option, but appreciate the pointer (and links in your comment above) for how to change the convention so SQL automatically uses datetime2. I don't understand why this was down voted. Your answer seems right on target and there isn't a comment here to explain the down vote. – Alan Dec 26 '17 at 22:21
  • Down vote was correct. I edited my answer and replied to the comment. That was the comment you don't understand to. Happy coding! – dropoutcoder Dec 27 '17 at 08:21