Linq SQL query has more precision for doubles

Question

I've been banging my head against the wall all day trying to figure why this is happening and how to fix it. I am building a C# .NET MVC web application. I call a query using Linq from the Database like this:

calibHistory = db.CalibrationHistory
                 .Where(d => d.ID == EquipmentId && d.ChangeNum == maxChangeNum).First();

And get the C# double type (equivalent to a float in SQL) with the following precision:

2.3980000019073486

I take this object and bind it to a form in a partial view. This works and is fine. However, something happens in the Razor rendering that causes the following value to be rounded off to this:

2.39800000190735

Obviously, Razor is rounding off the last two digits (the last two digits up the 4 to a 5). Debugging shows that Razor knows the correct value - in the model where the double is held, you see the double with full precision. Here's where things get interesting, however: a call in SQL Server Management Server shows that the Razor value has the correct precision, not the double returned by Linq.

So somehow, Linq is getting more precision than what is being provided by SQL Server. I want to be able to compare the posted values from the form against the Linq query above to see if there are changes. I haven't been able to figure this out so any help would be greatly appreciated.

TL;DR: Linq is returning more precision for doubles and messing up equality measures. Would like to either fix the Linq query or make Razor show the full precision.

Answer 1

OK thanks to everyone who answered - surprised how many people were monitoring these tags!

It seems like I had the answer all along but didn't fully execute it. I had already worked with the R format specifier, but had not deployed the right combination of variables in my data annotation.

Due to the nature of EntityFramework, I needed to override certain aspects of the generated POCO object. This led me to this solution:

[MetadataType(typeof(CalibrationHistory.MetaData))]
    public partial class CalibrationHistory
    {
        internal class MetaData {
            [Display(Name = "New Sensor Calibration value")]
            [DisplayFormat(DataFormatString ="{0:R}", ApplyFormatInEditMode = true)]
            public double Sensor_Calib;
            [Display(Name = "New Cylinder to Wall Distance value")]
            [DisplayFormat(DataFormatString = "{0:R}", ApplyFormatInEditMode = true)]
            public double Cylinder_to_Wall_Distance;
        }

        public override bool Equals(object obj)
        {
            CalibrationHistory castedObject = (CalibrationHistory)obj;
            if (this.ID == castedObject.ID && this.Cylinder_to_Wall_Distance == castedObject.Cylinder_to_Wall_Distance
                && this.Sensor_Calib == castedObject.Sensor_Calib)
            {
                return true;
            }
            else {
                return false;
            }
        }

In the data annotations, I had only specified a DataFormatString without the ApplyFormatInEditMode. Thus, the changes were not reflected in the textbox and therefore not posting with the needed precision.

Thank you everyone who answered.

Answer 2

Razor itself does not manipulate numbers. If it's rendering a number, it's using the default .ToString() method unless you specify otherwise. You might try adding an explicit .ToString() in your partial view, with a format string, wherever this value is rendering out.

As far as comparing the value when it posts again, you're going to need some code that's a bit more sophisticated than "a=b" because the double data type gets wonky when comparing very small numbers. See here.

It is very likely that your number, 2.3980000019073486, isn't the exact value stored in the database because double is only precise to about 15-16 digits. Consider changing the database's type to decimal, and accordingly, your code, if you need more precision. Decimal is accurate to 28-29 digits.