SqlServer: Literals with real type is always considered float type

[smitpatel]

In SSMS

SELECT SQL_VARIANT_PROPERTY(0E0, 'BaseType')
//Result: float

We generate literal for real (C# single) & float (C# double) as 0E0 format. By default SqlServer assumes it to be float type. This forces upcasting when used inside SQL functions which causes runtime InvalidCastException.

Example:

public class Blog
{
    public int Id { get; set; }
    public float? Value { get; set; }
}

var query = db.Blogs.Where(b => b.Id == 1).Select(b => b.Value ?? 0).SingleOrDefault();

Throws

 An exception occurred in the database while iterating the results of a query for context type 'EFSampleApp.MyContext'.
      System.InvalidCastException: Unable to cast object of type 'System.Double' to type 'System.Single'.
         at lambda_method(Closure , QueryContext , ValueBuffer )
         at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer valuUnhandled Exception: eBuffer)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(Boolean buffer)
         at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
         at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
         at lambda_method(Closure , QueryContext )
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass17_0`1.<CompileQueryCore>b__0(QueryContext qc)

Generated SQL

SELECT TOP(2) COALESCE([b].[Value], 0E0)
FROM [Blogs] AS [b]
WHERE [b].[Id] = 1

Value column is mapped to real data type in server. But 0E0 is taken as float literal. COALESCE function uses the highest precedence data type for final result type. Which makes result of type float. But C# expects single instead of double hence fails.

There can be few more functions/cases like this.

[smitpatel]

Same goes for long type literals (whole numbers which do not fit in int range) they are converted to numeric type.

[smitpatel]

The second issue was handled for case of bitwise & for Enum.HasFlag in #8851
We should consider generating the literal value with explicit casting to avoid such issues.

[divega]

@smitpatel [closed-fixed] but not closed?

[smitpatel]

Solution: Hard casting to real type for SqlServer.
Filing separate issue for bigint/numeric.