fbpx

Entity Framework error: “SqlDateTime overflow. Must be between…”

Overlapping colored triangles

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.

About the Author:

Visionfriendly.com

Visionfriendly.com

VisionFriendly.com has been building successful websites for over 20 years and covers all areas of website design, development and marketing with creative custom web designs and a robust in-house team of programmers to get your web marketing to work exactly the way you need it to.

Share On:

Share on facebook
Share on twitter
Share on linkedin

Comments:

Leave a Comment

Your email address will not be published. Required fields are marked *

Copyright © 2021, All Rights Reserved
Tri Colored Triangles
Scroll to Top

Take your business to the

Next Level!

Visionfriendly.com has the right team to make your business stand out from other professional websites.

Ready For Takeoff?

A Completely Customized
Digital Marketing Experience

We'll be in touch within one business day to discuss your goals and create a personalized plan that fits both you and your business.

Let’s start a Conversation