SQL Server to .Net Decimals with EF6 dDatabase first issue

Question

SQL Server to .Net Decimals with EF6 dDatabase first issue

c# entity-framework entity-framework-6 sql-server

Question

I'm currently reading data from a SQL Server stored procedure and it is returned as Decimal(38, 20).

I know that the .Net decimal is (28, 10), but the model classes generated for this by EF are showing Decimal?. When I query the table, the code throws a conversion overflow error, because it is trying to place the SQL Server decimal in the .Net decimal field.

Is there an easy way to convert it to .Net decimal through the data context? Basically I don't have access to modify the SQL Server datatype.

SQL Server datatype:

 Amount Decimal(38, 20)

Entity

public class EntityClass
{
     public decimal? Amount { get; set; }
}

And when I call

 var eee =  _context.EntityClass
                    .Select(x => x.Amount)
                    .FirstOrDefaultAsync();

I get

Conversion Overflow Error

Joe Ricklefs

Related Questions

View more on Stack Overflow

David Browne - Microsoft · Accepted Answer · 5/10/2019 6:13:34 PM

This is a bug or limitation with .NET's SqlClient. Here's a repro:

    using (var con = new SqlConnection("server=.;database=tempdb;Integrated Security=true"))
    {
        con.Open();
        var cmd = new SqlCommand("select cast(4210862852.86 as decimal(38,20))  val", con);
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            rdr.Read();

            var val = rdr.GetDecimal(0);
            Console.WriteLine(val);
        }
    }

The issue is that this number

select cast(cast(4210862852.86 as decimal(38,20)) as varbinary(20))

is stored, and transmitted over the wire as

0x261400010000D877FB4DEE8B51699A5005000000

And SqlClient will refuse to convert any decimal with a non-zero in that last 4 bytes:

internal decimal Decimal
{
    get
    {
        ThrowIfNull();

        if (StorageType.Decimal == _type)
        {
            if (_value._numericInfo.data4 != 0 || _value._numericInfo.scale > 28)
            {
                throw new OverflowException(SQLResource.ConversionOverflowMessage);
            }
            return new decimal(_value._numericInfo.data1, _value._numericInfo.data2, _value._numericInfo.data3, !_value._numericInfo.positive, _value._numericInfo.scale);
        }
        if (StorageType.Money == _type)
        {
            long l = _value._int64;
            bool isNegative = false;
            if (l < 0)
            {
                isNegative = true;
                l = -l;
            }
            return new decimal((int)(l & 0xffffffff), (int)(l >> 32), 0, isNegative, 4);
        }
        return (decimal)this.Value; // anything else we haven't thought of goes through boxing.
    }
}

https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlBuffer.cs

I opened an issue against .NET Core here: https://github.com/dotnet/corefx/issues/37592