SQL Server to .Net Decimals with EF6 dDatabase first issue

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


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)


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

And when I call

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

I get

Conversion Overflow Error

5/10/2019 7:27:52 AM

Popular Answer

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"))
        var cmd = new SqlCommand("select cast(4210862852.86 as decimal(38,20))  val", con);
        using (SqlDataReader rdr = cmd.ExecuteReader())

            var val = rdr.GetDecimal(0);

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


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

internal decimal Decimal

        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.


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

5/10/2019 6:13:34 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow