Note: For enhancements to the code in this post, please look to the comments from Johannes and Ian Yates below!
If you are getting this DbUpdateException from Entity Framework:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
You are probably getting to know the pain of the mismatch between the CTS (Common Type System) DateTime and SqlDateTime ranges. In SQL 2008, this is not a problem because the Entity Framework can use the new SQL Server data type datetime2. (Other databases may have this issue, too.)
In some cases, DateTime.Now serves as an acceptable default value for DateTimes. However, we often want date ranges defaulting to the maximum and minimum possible date values. The only fixes I've seen for this issue involve hacks that either pollute the domain object with SqlDateTime values, or that fix the dates on a Entity-by-Entity (or POCO-by-POCO) basis, one property at a time. Both of these are too easy to forget and cause a lot of clutter. I want an automatic solution that assumes that the min/max values for DateTime correspond to the min/max values for SqlDateTime. The overall strategy is as follows:
- Handle the ObjectContext.SavingChanges event: When saving to the database, change any DateTime values that are less than the SqlDateTime minimum value to be the database-compatible minimum. Same goes for the max values.
- Handle the ObjectContext.ObjectMaterialized event: When materializing POCOs/Entities from the EntityClient, change any DateTime values that are equal to the SqlDateTime minimum value to the CTS DateTime minimum value. Same goes for the max values.
- Make sure that the Framework doesn't think that newly-materialized POCOs are in a Modified state.
Here's my first take at a solution. Note that I'm using EF4 CTP5 code-first, but it should apply to other scenarios.
Step 1, which is easy, is to add your event handlers in your ObjectContext/DbContext constructor:
public ProductsContext(string connectionString) : base(connectionString)
{
ObjectContext objectContext = ((IObjectContextAdapter)this).ObjectContext;
objectContext.SavingChanges += new EventHandler(ObjectContext_FixDatesBeforeSave);
objectContext.ObjectMaterialized += new ObjectMaterializedEventHandler(ObjectContext_FixDatesAfterMaterialized);
}
If you are using ObjectContext, you don't need the ugly cast. Let's start with the SavingChanges event handler:
private void ObjectContext_FixDatesBeforeSave(object sender, EventArgs e)
{
ObjectStateManager osm = ((ObjectContext)sender).ObjectStateManager;
foreach (ObjectStateEntry entry in osm.GetObjectStateEntries(EntityState.Added | EntityState.Modified))
{
CurrentValueRecord currentValues = entry.CurrentValues;
this.FixOutgoingDatesRecursive(currentValues);
}
}
The above code loops through new or modified entities/POCOs (as represented by ObjectStateEntry) and calls a function to find and fix any DateTimes in the object graph. We'll get to the function (and why it's recursive) later. Here's the ObjectMaterialized handler:
private void ObjectContext_FixDatesAfterMaterialized(object sender, ObjectMaterializedEventArgs e)
{
ObjectStateManager osm = ((ObjectContext)sender).ObjectStateManager;
foreach (ObjectStateEntry entry in osm.GetObjectStateEntries(EntityState.Unchanged))
{
// Fixing up the OriginalValues doesn't seem to be strictly necessary, but it feels right?
OriginalValueRecord originalValues = entry.GetUpdatableOriginalValues();
this.FixIncomingDatesRecursive(originalValues);
CurrentValueRecord currentValues = entry.CurrentValues;
this.FixIncomingDatesRecursive(currentValues);
entry.ChangeState(EntityState.Unchanged); }
}
In a similar fashion, the above code loops through and fixes DateTimes for both the OriginalValues and CurrentValues of every entity/POCO. Now for the two recursive functions.
private void FixIncomingDatesRecursive(DbUpdatableDataRecord values)
{
for (int i = 0; i < values.FieldCount; i++)
{
object valueObject = values[i];
if (valueObject is DateTime)
{
DateTime valueDate = (DateTime)valueObject;
if (valueDate == System.Data.SqlTypes.SqlDateTime.MinValue.Value)
{
values.SetDateTime(i, DateTime.MinValue);
}
}
else if (valueObject is DbUpdatableDataRecord)
{
this.FixIncomingDatesRecursive((DbUpdatableDataRecord)valueObject);
}
}
}
private void FixOutgoingDatesRecursive(DbUpdatableDataRecord values)
{
for (int i = 0; i < values.FieldCount; i++)
{
object valueObject = values[i];
if (valueObject is DateTime)
{
DateTime valueDate = (DateTime)valueObject;
if (valueDate < System.Data.SqlTypes.SqlDateTime.MinValue.Value)
{
values.SetDateTime(i, System.Data.SqlTypes.SqlDateTime.MinValue.Value);
}
}
else if (valueObject is DbUpdatableDataRecord)
{
this.FixOutgoingDatesRecursive((DbUpdatableDataRecord)valueObject);
}
}
}
Things to note:
- They take DbUpdatableDataRecord, the base class of both CurrentValues and OriginalValues (in the System.Data.Objects namespace).
- They are recursive because a property might be a DateTime, which we can fix directly, or it might be a complex type, into which we need to recurse.
- Technically, the max value for the CTS DateTime is the same as SqlDateTime, so I've left off the tests for exceeding the max value.
One place where the code can be improved is the direct dependency on SqlDateTime. Making it flexible enough to handle other providers is left as an exercise for the reader =) You might also consider making extension methods on either ObjectStateEntry or DbUpdatableDataRecord.
And that's it! Like I said, this is my first attempt, and I'm new to EF, so please tell me of flaws / improvements. Checking with SQL Profiler and with a couple of breakpoints to inspect newly materialized POCOs, everything looks to be in order! We now have transparent handling of the default DateTime value in a reasonable way that doesn't introduce clutter and database-specific code all over the place.
