Rounding error in C# when converting currencies during object creation

Question

I'm converting currencies and have encountered a strange rounding issue when using entity framework. Here's the abbreviated code:

var v = select new ValuationsModel
{
    LocalValue = dv.LocalValue,
    CurrencyId = dv.CurrencyId,
    FXRate = fx.ExchangeRate,
    USDValue = dv.LocalValue * (dv.CurrencyId == "USD" ? 1.0m : Convert.ToDecimal(fx.ExchangeRate)),
}).ToList();

decimal usdValue = v.LocalValue.Value * (v.CurrencyId == "USD" ? 1.0m : Convert.ToDecimal(v.FXRate));

Here are the values (all were verified in the quick watch window):

fx.ExchangeRate is type Double

dv.CurrencyId is type String

dv.LocalValue is type nullable Decimal

fx.ExchangeRate = 1.36678
dv.CurrencyId = "GBP"
dv.LocalValue = 15102141.1994

The value in USDValue is: 20689933.443178 = 15102141.1994 * 1.37

The value in usdValue is: 20641304.548515932 = 15102141.1994 * 1.36678

Why would fx.ExchangeRate get rounded to 1.37 in the select new statement but not be rounded in the exact same calculation below? I even copied the currency check into the second assignment to make sure that wasn't affecting things. Do I just have to do the calculation outside of the select new statement if I want accuracy?

Please show the relevant class definitions (`ValuationsModel` and whatever type `fx` is). My money is on using different types for storing the decimal value. — Flater, Apr 09 '21 at 14:40
[this code works in C#](https://dotnetfiddle.net/tvQ8Pa) the only problem can be if `dv.LocalValue * (dv.CurrencyId == "USD" ? 1.0m : Convert.ToDecimal(fx.ExchangeRate))` is translated to SQL (and which database is used) — Selvin, Apr 09 '21 at 14:45
@Flater The types are listed. The issue isn't that a rounding error is happening generally, it's that the same calculation produces two different results from the same values. — Legion, Apr 09 '21 at 14:45
@Legion We'd rather be able to copy-and-paste (minimal) class definitions than have to write them ourselves for the purposes of reproducing the problem. — Andrew Morton, Apr 09 '21 at 14:46
@AndrewMorton I understand, but `dv` and `fx` are entity framework classes so I can't give you a proper class definition for them. — Legion, Apr 09 '21 at 14:48
@Legion Then how can we attempt to reproduce the problem to help you? Surely you could write minimal classes for us to use? Having said that, I think that [Selvin could be on to something](https://stackoverflow.com/questions/67022992/rounding-error-in-c-sharp-when-converting-currencies-during-object-creation#comment118470843_67022992). — Andrew Morton, Apr 09 '21 at 14:51
@Legion: `ExchangeRate` is not a known property in any of EF's library code. These classes might be entities _used_ in context of EF, but the class definition is custom built and not part of the library. My money is still that the type used for `ValuationsModel.FXRate` is different than that for `fx.ExchangeRate` — Flater, Apr 09 '21 at 14:51
again .. it is SQL fault ... he didn't mention this in the question but he is using EF .. now `select ....` is not executed on C# side but it is translated to SQL ... now it's easy to image that `Convert.ToDecimal(...)` is translated to something like `CAST(... AS DECIMAL(X,Y))` then if `Y` is `2` the result is obvious ... it is even worst if he is using SQLite because there is no `DECIMAL` data type there ... **so solution is to materialize result and calculate on C# side** — Selvin, Apr 09 '21 at 14:59
@Selvin I believe you are correct. I'm not using SQLite though using SQL Server. Is it generally considered bad practice to have any calculations in your `SELECT`ing LINQ code? This is my first time running into something like this. — Legion, Apr 09 '21 at 15:02
you may use `select new { LocalValue = dv.LocalValue, CurrencyId = dv.CurrencyId, FXRate = fx.ExchangeRate }.AsEnumerable().Select(v => new ValuationsModel { ... set whatever you need using v - instance of anonymouse ...}).ToList()` with `.AsEnumerable()` you materialize query — Selvin, Apr 09 '21 at 15:08
and bad practice is to store something in double/real when it is connected with currency(fx excange rate) — Selvin, Apr 09 '21 at 15:11

score 2 · Answer 1 · answered Apr 09 '21 at 14:37

2

Decimal and Double have different levels of precision. See this question and this question.This has to do with the limitations of computers in handling floating point arithmetic, and the tradeoffs of speed vs high precision.

answered Apr 09 '21 at 14:37

TheAtomicOption

1,456
1
12
21

It has nothing to do with the relative precisions of decimal and double. The issue is that the same calculation produces two different results from the same values. – Legion Apr 09 '21 at 14:47
@Legion: If the types used are different, then it's not "the same calculation". If you look at the underlying binary, doubles and decimals are represented entirely differently, and thus any calculations done with them are done differently. – Flater Apr 09 '21 at 14:54
@Flater The relative types are the same in both calculations. `USDValue` = `dv.LocalValue` of type decimal being multiplied by `fx.ExhangeRate` of type double that is converted to decimal. `usdValue` = `v.LocalValue.Value` of type decimal being multiplied by `v.FXRate` of type double that is converted to decimal. – Legion Apr 09 '21 at 14:57

Rounding error in C# when converting currencies during object creation

1 Answers1