Inserting small float value to SQL Server using C# and Entity Framework

Question

My code is like this.

public class MyDbContext : DbContext
{
    public virtual DbSet<CurrentData> CurrentData { get; set; }
}

public class CurrentData
{
    public int ID { get; set; }
    public DateTime Time { get; set; }
    public float Value { get; set; }
}

...

float f = 1.37E-40f;

using (var context = new MyDbContext())
{
    var data = new CurrentData() { ID = 100, Time = DateTime.Now, Value = f };
    context.CurrentData.Add(data);

    var result = context.SaveChanges();
}

It raises exception on SaveChanges() because of the small float value.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 (\"@1\"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.

If I change f to 0, it works properly.

When I checked SQL Server's REAL type range, the minimum positive value is 1.18E-38.

https://msdn.microsoft.com/en-us/library/ms173773.aspx

So if float value in C# is smaller than 1.18E-38, the exception occurs.

I changed f value like this

if (f > 0 && f < 1.18E-38f)
    f = 0;
else if (f < 0 && f > -1.18E-38f)
    f = 0;

But I think it doesn't look good.

What will be better solution?

I tested using SqlDataAdapter instead of Entity Framework, and got same exception.

I used EF v6.1.3 and .NET 4.5.1 with SQL Server 2012.

if you only store the "number" and do not need to calculate on the number in mssql, you could perhaps store it as a string literal? https://msdn.microsoft.com/en-us/library/sd9e25kz(v=vs.110).aspx — DaFi4, Apr 28 '16 at 12:59
@montewhizdoh because there's no need to do so. SQL Server *does* have `numeric` types with well defined scale and precision eg `numeric(19,5)`. — Panagiotis Kanavos, Apr 28 '16 at 13:01
Just *don't* use `float` or `real`. Use a numeric type with the required scale and precision on the table, and `decimal` on the class. On the other hand, *why* do you want to use such a small number? Are you trying to emulate a NULL? — Panagiotis Kanavos, Apr 28 '16 at 13:01
@Panagiotis how would you store numeric(800,799) ? You haven't done many regression calculations, have you? — DaFi4, Apr 28 '16 at 13:05
@montewhizdoh I don't. I ask for the *reason* for such a weird request. Most likely the OP is trying to solve a different problem, and thought that storing a small float is the solution. Eg, the code and/or database aren't using decimal/numeric resulting in scaling errors. The OP may have though to use a small value instead of 0 for comparison. The *real* fix is to define the proper scale for all numeric fields and just compare with 0 if needed — Panagiotis Kanavos, Apr 28 '16 at 13:07
its not a weird request in regression calculations or other fringe cases. You said there was no reason to do so, so you must already know the reason, then. In theoretical math, you cannot know the real fix without knowing the requirements! :) — DaFi4, Apr 28 '16 at 13:08
@montewhizdoh this *is* a weird request when databases are concerned. It's also caused by using an imprecise type like `float` in calculations, and allowing arbitrary rounding. — Panagiotis Kanavos, Apr 28 '16 at 13:10
Well you cannot always use numeric, and regressions are often imprecise guesses, and float is appropriate in the case of certain regression calculations. sometimes you need to store a value closer to 0 than the native db datatype can store. It doesn't mean the problem is unsolvable. In other words sometimes there "is a need to do so". I have at least one solid example where float will beat decimal for utility in mssql (IRR calculation) and based on this example alone I can find infinitely more. — DaFi4, Apr 28 '16 at 13:16
Precise scale is meaningless when dealing with regression calculations that concern "guesses". A precise data type will be no more accurate than an imprecise datatype (because its a guess!) and the regression code that uses a precise datatype in this example will break easier than the imprecise type. Now that data science is becoming a big deal, you will see this type of problem appear more often. — DaFi4, Apr 28 '16 at 13:26
I got that kind of small number from sensor or instrument. And the reason why i used c# float or SQL real type is that it has enough precision for me. Using decimal or double is not my option because I don't need high precision and it will have more memory and space. — Bongho Lee, May 03 '16 at 12:00

score 2 · Accepted Answer · edited May 23 '17 at 12:16

The problem here is that C# supports denormalized floating-point values when running on an x86 architecture, whereas SQL Server does not. As a result, a C# float supports more values than a SQL Server REAL, even though both are nominally single-precision floating point types. (Further confusing the issue is that what SQL Server calls FLOAT is actually a double precision type, which C# calls double, so float != FLOAT -- but that's not the problem here).

There is no way to disable denormalized values in C#, and there is no terribly good way to test for them either. This question has some approaches in the answers, though. An alternative is of course to increase the precision of the column on the SQL Server side to FLOAT; this can handle all values in the range of a C# float, even denormalized. Of course, this just shifts the problem when you're reading a value from the database that doesn't fit in a float. And it leaves no solution for when you need to store a C# double, since there is no corresponding bigger type on the SQL end.

For practical purposes, a simple range check may work well enough, especially if you can tie it to a range your application will actually use.

Do you think that it is right the converting code be in user code? I think most users may treat c# float as sql real type, and it means it has potential problem in this case. I think there should be some option ignoring for this in EF or SqlDataAdapter. — Bongho Lee, May 03 '16 at 12:16
Users can't afford to be ignorant of the differences between C# data types and SQL Server data types to begin with. For example, a C# `DateTime` supports the value `0001-01-01`, an SQL `DATETIME` does not. You can't just "ignore" range issues like this. But I'm not the author of EF and a discussion on that is rather off topic for SO. You can always open an issue with the EF project. — Jeroen Mostert, May 03 '16 at 12:36

Inserting small float value to SQL Server using C# and Entity Framework

1 Answers1