24

Is it possible to globally set Entity Framework DbContext to use datetime2 for all properties that are System.DateTime when using Code-First model?

I can do this for each column by using HasColumnType() method, but for an existing codebase I would like a global solution.

Knaģis
  • 20,827
  • 7
  • 66
  • 80
  • Just curious as to why you want to do this. The reason I ask is it is a very common error when trying to commit an entity that has a default value for a non-nullable DateTime column (DateTime.MinVal). The exception indicates that you should use a DateTime2 column, but this is mostly misleading unless you actually need the extra precision. The fix is to either assign a non-default value for the DateTime field, or to define it as being nullable. Hope this helps. – w.brian Mar 06 '13 at 16:35
  • @w.brian 1) We have a tables that store works of art and authors where both can be made/born before 1753... 2) If this would be possible, it would be an alternative for min date validation for every other field. 3) technical curiosity... – Knaģis Mar 06 '13 at 16:40
  • Gotcha. Just wanted to make sure you weren't barking up the wrong tree as I originally did when I ran into the exception I noted above. – w.brian Mar 06 '13 at 16:44
  • 1
    @w.brian Even when the error is because of `DateTime.MinVal` it's reasonable to force `DateTime2`. Quoting MSDN: `Use the time, date, datetime2 and datetimeoffset data types for new work.` – user247702 May 02 '13 at 13:53
  • 1
    @w.brian There is never a reason to use datetime anymore. datetime2 allows for a larger date range, is more precise, and uses less storage if picked to be less precise. The stated error is not the only reason to switch over. – Kyle Jun 07 '13 at 22:50

2 Answers2

36

Since EF6 has been out for quite a while now and this question still shows up in searches, here is the way to use custom conventions to set the SQL type. Within OnModelCreating method in your DbContext class do:

modelBuilder.Properties<DateTime>()
    .Configure(c => c.HasColumnType("datetime2"));
Knaģis
  • 20,827
  • 7
  • 66
  • 80
Søren Boisen
  • 1,669
  • 22
  • 41
  • Is this for new modal only? because I got this error message on Update-Database: ALTER TABLE ALTER COLUMN Date failed because one or more objects access this column. – Jacob Jul 07 '17 at 03:41
  • 1
    @Jacob Do you mean will it work if you have already created the database for the model? Yes it does, but you have to remember to create a migration after adding the convention to OnModelCreating. This is how I did it, so I know it works if done right :) – Søren Boisen Jul 09 '17 at 10:15
  • 1
    I had the error as a result of the tables constrains. I solved it by deleting them manually according to this answer: https://stackoverflow.com/a/19461205/665783 However, your answer helped us very much. We migrated our whole db by the code you posted. Thank you! – Jacob Jul 09 '17 at 13:49
7

Not in EF5 but EF6 (currently in alpha version) allow this with custom conventions. For EF5 you would need some custom convention based framework based on reflection which would add HasColumnType calls to model builder through reflection - check for example EF Code First Extras (it claims to have support for pluggable conventions).

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670