Force DateTime with Entity Framework Database Firs

Question

I am connecting to a database using EF database first. Therefore my models are autogenerated.

When I update a record my DateTimes are getting written as SQL type DateTime2. The production server is SQL Server 2005 (no comments please), so the updates fail as DateTime2 is not supported.

I've tried creating a metadata class for my model as per http://www.asp.net/mvc/overview/getting-started/database-first-development/enhancing-data-validation and decorating the member with [Column(DbType = "datetime")], but either I'm not following the instructions properly or I'm on the wrong track.

How can I get EF database first to use DateTime when writing to the database?

Thanks

Answer 1

I have had this problem in the past as Entity Framework standard for DateTime is the DateTime2 field, the obviously workaround is to update all columns to datetime2 in your SQL Server database, however datetime2 data-type was only introduced in SQL Server 2008, so here are a few things to try out:

First: ensure that if the DateTime field in your SQL Database is nullable, your model is using "DateTime?" (nullable datetime) instead of "DateTime".

Second: open your EDMX file with any XML editor (Visual Studio should work) and change the value of your ProviderManifestToken to ProviderManifestToken="2005", this should ensure SQL Server 2005 compatibility.

That works with .edmx files, but with Code First changing will be a little bit more challenging and will depend on your Entity Framework Version, so Microsoft recommends trying to specify your column type on your OnModelCreating method, like the following example:

modelBuilder.Entity<Blog>().Property(t => t.CreatedOn).HasColumnName("CreatedOn").HasColumnType("date");

Try experimenting with values for ColumnType until you reach your objective.

If you still want to change your ProviderManifestToken value:

E.F. 6: you can create a configuration for your DbContext, basically a class like this:

/// <summary>
/// A configuration class for SQL Server that specifies SQL 2005 compatability.
/// </summary>
internal sealed class EntityFrameworkDbConfiguration : DbConfiguration
{
    /// <summary>
    /// The provider manifest token to use for SQL Server.
    /// </summary>
    private const string SqlServerManifestToken = @"2005";

    /// <summary>
    /// Initializes a new instance of the <see cref="EntityFrameworkDbConfiguration"/> class.
    /// </summary>
    public EntityFrameworkDbConfiguration()
    {
        this.AddDependencyResolver(new SingletonDependencyResolver<IManifestTokenResolver>(new ManifestTokenService()));
    }

    /// <inheritdoc />
    private sealed class ManifestTokenService : IManifestTokenResolver
    {
        /// <summary>
        /// The default token resolver.
        /// </summary>
        private static readonly IManifestTokenResolver DefaultManifestTokenResolver = new DefaultManifestTokenResolver();

        /// <inheritdoc />
        public string ResolveManifestToken(DbConnection connection)
        {
            if (connection is SqlConnection)
            {
                return SqlServerManifestToken;
            }

            return DefaultManifestTokenResolver.ResolveManifestToken(connection);
        }
    }
}

Usage:

DbConfigurationType(typeof(EntityFrameworkDbConfiguration))]
public class MyContextContext : DbContext
{
}

(source: How to configure ProviderManifestToken for EF Code First)

E.F. 5 and older: Read this post that easily clarifies it: http://blog.oneunicorn.com/2012/04/21/code-first-building-blocks/

Hope that helps.

Answer 2

Annoyingly Felippe's suggestion didn't work, although I can't see any reason why it shouldn't.

I managed to hack out my own solution. Not completely happy with it, but we're trying to get the client to upgrade to SQL Server 2014 so it isn't permanent. Comments welcome...

First I created this class:

internal sealed class MyCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        ChangeDateTime2ToDateTimeForSqlServer2005Compatibility(command);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        ChangeDateTime2ToDateTimeForSqlServer2005Compatibility(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        ChangeDateTime2ToDateTimeForSqlServer2005Compatibility(command);
    }

    /// <summary>
    /// Changes parameters of type datetime2 to datetime for SQL server2005 compatibility.
    /// </summary>
    /// <param name="command">The command.</param>
    private void ChangeDateTime2ToDateTimeForSqlServer2005Compatibility(DbCommand command)
    {
        foreach (DbParameter param in command.Parameters)
        {
            if (param.DbType == System.Data.DbType.DateTime2)
            {
                param.DbType = System.Data.DbType.DateTime;
            }
        }
    }
}

Then I activate it using a static constructor in my DbContext derived class:

    static MyDbContext()
    {
        // The command interceptor changes DateTime2 data types to DateTime for SQL Server 2005 compatibility.
        DbInterception.Add(new MyCommandInterceptor());
    }

I'm going to keep a good watch on this to make sure that it behaves however.

Answer 3

I solve it by deleting the database and run the update-database command again in package manager console. Not suitable for production environment though.