Fix Entity Framework so it does not "assume" the SQL datatype is DateTime2 when using SQL 2008 and greater

[ImGonaRot]

The EF code assumes that all .NET DateTime properties should be cast as DateTime2 SQL data types when the SQL version is 2008 and greater.

Please adjust EF so that it "assumes" DateTime (this will not break current code since DateTime and DateTime2 are interchangeable up until SQL 2016 https://docs.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016?view=sql-server-2017) and then let the caller add an attribute to the .NET DateTime property to tell EF wither to use DateTime or DateTime2 data type.

Something like...

[Column(TypeName ="datetime2")]
public DateTime TestDate { get; set; }

[Column(TypeName ="datetime")] // Not needed by default
public DateTime AnotherTestDate { get; set; }

Here are the places in the EF code that needs "fixed":
All code is in the "EntityFramework.SqlServr" .NET project.

1. class SqlFunctionCallHandler

// <summary>
// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation" /> for exact translation
// Pre Katmai creates datetime.
// On Katmai creates datetime2.
// </summary>
private static ISqlFragment HandleCanonicalFunctionCreateDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{            
    var typeName = (sqlgen.IsPreKatmai) ? "datetime" : "datetime2";
    return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, typeName, e.Arguments, true, false);
}

// <summary>
// TruncateTime(DateTime X)
// PreKatmai:    TRUNCATETIME(X) => CONVERT(DATETIME, CONVERT(VARCHAR(255), expression, 102),  102)
// Katmai:    TRUNCATETIME(X) => CONVERT(DATETIME2, CONVERT(VARCHAR(255), expression, 102),  102)
// TruncateTime(DateTimeOffset X)
// TRUNCATETIME(X) => CONVERT(datetimeoffset, CONVERT(VARCHAR(255), expression,  102)
// + ' 00:00:00 ' +  Right(convert(varchar(255), @arg, 121), 6),  102)
// </summary>
private static ISqlFragment HandleCanonicalFunctionTruncateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
    //The type that we need to return is based on the argument type.
    string typeName = null;
    var isDateTimeOffset = false;

    var typeKind = e.Arguments[0].ResultType.GetPrimitiveTypeKind();

    if (typeKind == PrimitiveTypeKind.DateTime)
    {
        typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2";
    }
    else if (typeKind == PrimitiveTypeKind.DateTimeOffset)
    {
        typeName = "datetimeoffset";
        isDateTimeOffset = true;
    }
    else
    {
        Debug.Assert(true, "Unexpected type to TruncateTime" + typeKind.ToString());
    }

    var result = new SqlBuilder();
    result.Append("convert (");
    result.Append(typeName);
    result.Append(", convert(varchar(255), ");
    result.Append(e.Arguments[0].Accept(sqlgen));
    result.Append(", 102) ");

    if (isDateTimeOffset)
    {
        result.Append("+ ' 00:00:00 ' +  Right(convert(varchar(255), ");
        result.Append(e.Arguments[0].Accept(sqlgen));
        result.Append(", 121), 6)  ");
    }

    result.Append(",  102)");
    return result;
}

2. class SqlGenerator

// <summary>
// Generate tsql for a constant. Avoid the explicit cast (if possible) when
// the isCastOptional parameter is set
// </summary>
// <param name="e"> the constant expression </param>
// <param name="isCastOptional"> can we avoid the CAST </param>
// <returns> the tsql fragment </returns>
private ISqlFragment VisitConstant(DbConstantExpression e, bool isCastOptional)
{
    // Constants will be sent to the store as part of the generated TSQL, not as parameters
    var result = new SqlBuilder();

    var resultType = e.ResultType;
    // Model Types can be (at the time of this implementation):
    //      Binary, Boolean, Byte, Date, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, Single, String, Time
    if (resultType.IsPrimitiveType())
    {
        var typeKind = resultType.GetPrimitiveTypeKind();
        switch (typeKind)
        {
            case PrimitiveTypeKind.Int32:
                // default sql server type for integral values.
                result.Append(e.Value.ToString());
                break;

            case PrimitiveTypeKind.Binary:
                result.Append(" 0x");
                result.Append(ByteArrayToBinaryString((Byte[])e.Value));
                result.Append(" ");
                break;

            case PrimitiveTypeKind.Boolean:
                // Bugs 450277, 430294: Need to preserve the boolean type-ness of
                // this value for round-trippability
                WrapWithCastIfNeeded(!isCastOptional, (bool)e.Value ? "1" : "0", "bit", result);
                break;

            case PrimitiveTypeKind.Byte:
                WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "tinyint", result);
                break;

            case PrimitiveTypeKind.DateTime:
                result.Append("convert(");
                result.Append(IsPreKatmai ? "datetime" : "datetime2");
                result.Append(", ");
                result.Append(
                    EscapeSingleQuote(
                        ((DateTime)e.Value).ToString(
                            IsPreKatmai ? "yyyy-MM-dd HH:mm:ss.fff" : "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture),
                        false /* IsUnicode */));
                result.Append(", 121)");
                break;

            case PrimitiveTypeKind.Time:
                AssertKatmaiOrNewer(typeKind);
                result.Append("convert(");
                result.Append(e.ResultType.EdmType.Name);
                result.Append(", ");
                result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
                result.Append(", 121)");
                break;

            case PrimitiveTypeKind.DateTimeOffset:
                AssertKatmaiOrNewer(typeKind);
                result.Append("convert(");
                result.Append(e.ResultType.EdmType.Name);
                result.Append(", ");
                result.Append(
                    EscapeSingleQuote(
                        ((DateTimeOffset)e.Value).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture), false
                    /* IsUnicode */));
                result.Append(", 121)");
                break;

            case PrimitiveTypeKind.Decimal:
                var strDecimal = ((Decimal)e.Value).ToString(CultureInfo.InvariantCulture);
                // if the decimal value has no decimal part, cast as decimal to preserve type
                // if the number has precision > int64 max precision, it will be handled as decimal by sql server
                // and does not need cast. if precision is lest then 20, then cast using Max(literal precision, sql default precision)
                var needsCast = -1 == strDecimal.IndexOf('.') && (strDecimal.TrimStart(new[] { '-' }).Length < 20);

                var precision = Math.Max((Byte)strDecimal.Length, DefaultDecimalPrecision);
                Debug.Assert(precision > 0, "Precision must be greater than zero");

                var decimalType = "decimal(" + precision.ToString(CultureInfo.InvariantCulture) + ")";

                WrapWithCastIfNeeded(needsCast, strDecimal, decimalType, result);
                break;

            case PrimitiveTypeKind.Double:
                {
                    var doubleValue = (Double)e.Value;
                    AssertValidDouble(doubleValue);
                    WrapWithCastIfNeeded(true, doubleValue.ToString("R", CultureInfo.InvariantCulture), "float(53)", result);
                }
                break;

            case PrimitiveTypeKind.Geography:
                AppendSpatialConstant(result, ((DbGeography)e.Value).AsSpatialValue());
                break;

            case PrimitiveTypeKind.Geometry:
                AppendSpatialConstant(result, ((DbGeometry)e.Value).AsSpatialValue());
                break;

            case PrimitiveTypeKind.Guid:
                WrapWithCastIfNeeded(true, EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */), "uniqueidentifier", result);
                break;

            case PrimitiveTypeKind.Int16:
                WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "smallint", result);
                break;

            case PrimitiveTypeKind.Int64:
                WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "bigint", result);
                break;

            case PrimitiveTypeKind.Single:
                {
                    var singleValue = (float)e.Value;
                    AssertValidSingle(singleValue);
                    WrapWithCastIfNeeded(true, singleValue.ToString("R", CultureInfo.InvariantCulture), "real", result);
                }
                break;

            case PrimitiveTypeKind.String:
                bool isUnicode;

                if (!e.ResultType.TryGetIsUnicode(out isUnicode))
                {
                    // If the unicode facet is not specified, if needed force non-unicode, otherwise default to unicode.
                    isUnicode = !_forceNonUnicode;
                }
                result.Append(EscapeSingleQuote(e.Value as string, isUnicode));
                break;

            default:
                // all known scalar types should been handled already.
                throw new NotSupportedException(
                    Strings.NoStoreTypeForEdmType(resultType.EdmType.Name, ((PrimitiveType)(resultType.EdmType)).PrimitiveTypeKind));
        }
    }
    else
    {
        throw new NotSupportedException();
        //if/when Enum types are supported, then handle appropriately, for now is not a valid type for constants.
        //result.Append(e.Value.ToString());
    }

    return result;
}

internal static string GenerateSqlForStoreType(SqlVersion sqlVersion, TypeUsage storeTypeUsage)
{
    Debug.Assert(BuiltInTypeKind.PrimitiveType == storeTypeUsage.EdmType.BuiltInTypeKind, "Type must be primitive type");

    var typeName = storeTypeUsage.EdmType.Name;
    var hasFacet = false;
    var maxLength = 0;
    byte decimalPrecision = 0;
    byte decimalScale = 0;

    var primitiveTypeKind = ((PrimitiveType)storeTypeUsage.EdmType).PrimitiveTypeKind;

    switch (primitiveTypeKind)
    {
        case PrimitiveTypeKind.Binary:
            if (!storeTypeUsage.MustFacetBeConstant(DbProviderManifest.MaxLengthFacetName))
            {
                hasFacet = storeTypeUsage.TryGetMaxLength(out maxLength);
                Debug.Assert(hasFacet, "Binary type did not have MaxLength facet");
                typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
            }
            break;

        case PrimitiveTypeKind.String:
            if (!storeTypeUsage.MustFacetBeConstant(DbProviderManifest.MaxLengthFacetName))
            {
                hasFacet = storeTypeUsage.TryGetMaxLength(out maxLength);
                Debug.Assert(hasFacet, "String type did not have MaxLength facet");
                typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
            }
            break;

        case PrimitiveTypeKind.DateTime:
            typeName = SqlVersionUtils.IsPreKatmai(sqlVersion) ? "datetime" : "datetime2";
            break;
        case PrimitiveTypeKind.Time:
            AssertKatmaiOrNewer(sqlVersion, primitiveTypeKind);
            typeName = "time";
            break;
        case PrimitiveTypeKind.DateTimeOffset:
            AssertKatmaiOrNewer(sqlVersion, primitiveTypeKind);
            typeName = "datetimeoffset";
            break;

        case PrimitiveTypeKind.Decimal:
            if (!storeTypeUsage.MustFacetBeConstant(DbProviderManifest.PrecisionFacetName))
            {
                hasFacet = storeTypeUsage.TryGetPrecision(out decimalPrecision);
                Debug.Assert(hasFacet, "decimal must have precision facet");
                Debug.Assert(decimalPrecision > 0, "decimal precision must be greater than zero");
                hasFacet = storeTypeUsage.TryGetScale(out decimalScale);
                Debug.Assert(hasFacet, "decimal must have scale facet");
                Debug.Assert(decimalPrecision >= decimalScale, "decimalPrecision must be greater or equal to decimalScale");
                typeName = typeName + "(" + decimalPrecision + "," + decimalScale + ")";
            }
            break;

        default:
            break;
    }

    return typeName;
}

3. class SqlProviderServices

// <summary>
// Determines SqlDbType for the given primitive type. Extracts facet
// information as well.
// </summary>
private static SqlDbType GetSqlDbType(
    TypeUsage type, bool isOutParam, SqlVersion version, out int? size, out byte? precision, out byte? scale, out string udtName)
{
    // only supported for primitive type
    var primitiveTypeKind = ((PrimitiveType)type.EdmType).PrimitiveTypeKind;

    size = default(int?);
    precision = default(byte?);
    scale = default(byte?);
    udtName = default(string);

    // CONSIDER(CMeek):: add logic for Xml here
    switch (primitiveTypeKind)
    {
        case PrimitiveTypeKind.Binary:
            // for output parameters, ensure there is space...
            size = GetParameterSize(type, isOutParam);
            return GetBinaryDbType(type);

        case PrimitiveTypeKind.Boolean:
            return SqlDbType.Bit;

        case PrimitiveTypeKind.Byte:
            return SqlDbType.TinyInt;

        case PrimitiveTypeKind.Time:
            if (!SqlVersionUtils.IsPreKatmai(version))
            {
                precision = GetKatmaiDateTimePrecision(type, isOutParam);
            }
            return SqlDbType.Time;

        case PrimitiveTypeKind.DateTimeOffset:
            if (!SqlVersionUtils.IsPreKatmai(version))
            {
                precision = GetKatmaiDateTimePrecision(type, isOutParam);
            }
            return SqlDbType.DateTimeOffset;

        case PrimitiveTypeKind.DateTime:
            //For katmai pick the type with max precision which is datetime2
            if (!SqlVersionUtils.IsPreKatmai(version))
            {
                precision = GetKatmaiDateTimePrecision(type, isOutParam);
                return SqlDbType.DateTime2;
            }
            else
            {
                return SqlDbType.DateTime;
            }

        case PrimitiveTypeKind.Decimal:
            precision = GetParameterPrecision(type, null);
            scale = GetScale(type);
            return SqlDbType.Decimal;

        case PrimitiveTypeKind.Double:
            return SqlDbType.Float;

        case PrimitiveTypeKind.Geography:
            {
                udtName = "geography";
                return SqlDbType.Udt;
            }

        case PrimitiveTypeKind.Geometry:
            {
                udtName = "geometry";
                return SqlDbType.Udt;
            }

        case PrimitiveTypeKind.Guid:
            return SqlDbType.UniqueIdentifier;

        case PrimitiveTypeKind.Int16:
            return SqlDbType.SmallInt;

        case PrimitiveTypeKind.Int32:
            return SqlDbType.Int;

        case PrimitiveTypeKind.Int64:
            return SqlDbType.BigInt;

        case PrimitiveTypeKind.SByte:
            return SqlDbType.SmallInt;

        case PrimitiveTypeKind.Single:
            return SqlDbType.Real;

        case PrimitiveTypeKind.String:
            size = GetParameterSize(type, isOutParam);
            return GetStringDbType(type);

        default:
            Debug.Fail("unknown PrimitiveTypeKind " + primitiveTypeKind);
            return SqlDbType.Variant;
    }
}

[ImGonaRot]

Similar posts here but it is marked as "closed / won't fix".
#49
#325

Can this be looked at again since this is a "breaking" change in SQL 2016?

There is also another problem with code generated from EF since it creates "DateTime" columns for tables yet compares as "DateTime2" data types.

[SocSoftSpb]

Also, need supports for Date datatype (without time).

[ImGonaRot]

@Gluck1312 Are you asking the EF team to support Date datatype without time? If so, what .NET data type would it bind too?
I don't think there is a .NET data type for just "Date".

[SocSoftSpb]

Selecting a parameter type (for example, For the Insert command) should refer to ColumnType specified in the mapping (or by attribute):

modelBuilder.Entity<MyEntity>()
                    .Property(p => p.MyDate)
                    .HasColumnType("date");

If ColumnType is not specified, use the most common type (datetime or datetime2).

[ajcvickers]

@ImGonaRot Changing the default mapping would be a breaking change for many applications, so we can't do that. If we could come up with a way to workaround the break in SQL Server without breaking existing applications that use EF, then we would be open to doing that. As of now I am not aware of any way we can do it.

[ImGonaRot]

@ajcvickers I thought I showed you a way of fixing this by using "TypeName" attributes like EF Core uses.
I even highlighted all the code that needs changed.

[ajcvickers]

@ImGonaRot Pretty sure none of that will fix this in a non-breaking way.

[ImGonaRot]

@ajcvickers Sure it could.
Any place where it switches to DateTime2, check the .NET property attribute and (If and Only If) the user added "TypeName=DateTime" then let EF use DateTime rather than DateTime2.

Ex.

if (typeKind == PrimitiveTypeKind.DateTime)
    {
        // Check the "TypeName" attribute for DateTime
        // property.GetCustomAttributes(typeof(Column))... more code here
        if (typeNameAttribute == "DateTime")
        {
            typeName = "datetime";
        }
        else
        {
            typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2";
        }
    }

I would suggest caching the attributes so that we don't have to use reflection every time since it is slow.

[ajcvickers]

@ImGonaRot I'll take another look.