Entity Framework: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

In my application I import files, process them and save them to the database. This leads to large amounts of inserts and updates on the database. It can be very frustrating to have Entity Framework reject all update with a message: “The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. “.
This message usually means a DateTime.MinValue is being inserted into a SQL DateTime column. The problem is to figure out which entities are causing the problem.

To help with this I wrote some code to inspect the pending changes. Luckily me and my team have been consistent when it comes to using “Date” in a property name wherever applicable.

#if DEBUG

// first get all the changes (added or modified)
var changes = MyEntities.ChangeTracker.Entries().Where( x => ( x.State == System.Data.Entity.EntityState.Modified ||
																x.State == System.Data.Entity.EntityState.Added )).ToList();

// look for all changes involving date field
var entitiesWithDate = ( from c in changes
							where c.CurrentValues.PropertyNames.Where( pn => pn.Contains( "Date" ) ).Any() == true //
							select c ).ToList();

// select all entities where datetime value is before 1/1/1000 which will be rejected by SQL Server
// I use 1/1/1000 because DateTime.MinValue did not seem to work
var entitiesWithMinValueDate = ( from e in entitiesWithDate
									from p in e.CurrentValues.PropertyNames.Where( pn => pn.Contains( "Date" ) )
									where e.CurrentValues[p] != null &amp;&amp; (DateTime) e.CurrentValues[p] < new DateTime( 1000, 1, 1 )
									select e ).ToList();

#endif

MyEntities.SaveChanges();

Notice I put #if DEBUG around the code, we don’t need this unless we’re debugging.