How to identify column throwing System.Data.SqlClient.SqlException? (conversion of a datetime2 data type to a datetime data type)

Question

I have overridden my ApplicationDbContext.SaveChanges() method. This helps me by providing immediately visible error messages on the yellow and white error application error screen.

But when there is a validation error with a DateTime column, the catch clause doesn't seem to be activated.

Why is that? And how might I identify the invalid column?

Override method

public partial class ApplicationDbContext 
{
    public override int SaveChanges()
    {
        try
        {
            return base.SaveChanges();  //**error is thrown here**//
        }
        catch (DbEntityValidationException ex)
        {
            var sb = new StringBuilder();

            foreach (var failure in ex.EntityValidationErrors)
            {
                sb.AppendFormat("{0} failed validation\n", failure.Entry.Entity.GetType());
                foreach (var error in failure.ValidationErrors)
                {
                    sb.AppendFormat("- {0} : {1}", error.PropertyName, error.ErrorMessage);
                    sb.AppendLine();
                }
            }

            throw new DbEntityValidationException(
                "Entity Validation Failed - errors follow:\n" +
                sb.ToString(), ex
                ); 
        }
    }
}

However the exception isn't thrown with DateTime columns, it appears that the catch block is not being activated.

enter image description here

The reason why you aren't catching it is because of a caught `DbEntityValidationException` vs a thrown `SqlException`. The solution is almost [certainly one of these causes](http://stackoverflow.com/q/1331779/314291) — StuartLC, Mar 05 '15 at 21:33
Thank you. Is there anything I could add into the code posted that would give me a hook into the SqlException? I really just want access to the property / field name causing the exception. — Martin Hansen Lennox, Mar 05 '15 at 21:39
Annoyingly, Sql Server doesn't actually list the miscreant column name AFAIK. If you are able to run Sql Profiler while you execute the statement (or paste to LinqPad), you can grab the actual Sql. But the likely candidate is a `DateTime` which hasn't been set to > `1753` (e.g. is unitialized as a default DateTime) — StuartLC, Mar 05 '15 at 21:57
That's a shame. Ah well good to know. Your combined comments make up the answer for me. If you want to combine them into I can mark it as such. — Martin Hansen Lennox, Mar 05 '15 at 22:04

score 2 · Accepted Answer · edited May 23 '17 at 11:50

TL;DR

If you use Sql DATETIME columns, add validation to .Net DateTime properties on persisted entities to ensure values are between 1753 and 9999 prior to .SaveChanges()
Otherwise, change your Sql storage to DATE or DATETIME2, depending on your precision requirements.

In Detail
Given that the Sql Server DateTime data type can only store dates in the range 1753 - 9999, whereas the .Net DateTime struct can store a larger dynamic range, and higher precision, i.e. not all .Net DateTimes can be stored in Sql Server DATETIME.

Especially prone to this error is a .Net DateTime property on an EF entity which hasn't explicitly been assigned, as default(DateTime) is 0001/01/01.

Hence the preference to replace DATETIME columns with DATETIME2 or DATE storage as appropriate.

As a result, on a > Sql 2005 RDBMS, Entity Framework will default to a Sql DATETIME2 datatype as this will allow storage beyond the limited range offered by DATETIME and is closer to the .Net data type.

However, if you do have an existing table with a DATETIME column and you attempt to bind an 'out of band' DateTime value, annoyingly, Sql Server doesn't actually list the miscreant column name. If you are able to run Sql Profiler while you execute the statement (or paste to LinqPad), you can grab the actual Sql. But the likely candidate is a DateTime which hasn't been set to > 1753 (e.g. is unitialized as a default DateTime).

The reason why you aren't catching the Exception is because of the disjoint between catch DbEntityValidationException vs a thrown SqlException.

that batch of info is even better than the comments - thank you so much — Martin Hansen Lennox, Mar 05 '15 at 22:41

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

If I'm not mistaken you are using smalldatetime in MSSQL, which in some cases can throw this error. Check this thread for the answer.

Try catching on catch(Exception ex) and there you'll be able to see the exact error message.

edited May 23 '17 at 11:43

Community

1
1

answered Mar 05 '15 at 21:35

Gergo Bogdan

199
1
5

Would I put an additional catch clause in along with the current one? So I'd have one to catch a `DbEntityValidationException` and another to catch an `Exception`. – Martin Hansen Lennox Mar 05 '15 at 21:50

How to identify column throwing System.Data.SqlClient.SqlException? (conversion of a datetime2 data type to a datetime data type)

2 Answers2