CODEWITHSUNDEEP
c#linqlinq-to-sqlmathrounding
Overhed
Overhed

Reputation: 1314

LINQ to SQL Math.Round Problem

I'm having an issue with a query I wrote where for some reason the variable that I'm using to store a decimal value in returns 6 values after the decimal place (they're mostly 0).

I have tried the following (and different combinations using Math.Round) with no luck.

Sales =
          (from invhed in INVHEAD 
           ... // Joins here
           orderby cust.State ascending
           select new Sale
           {
                InvoiceLine = inv.InvoiceLine,
                InvoiceNum = inv.InvoiceNum,
                ...
                NetPrice = Math.Round((inv.ExtPrice - inv.Discount) * (Decimal) (qsales.RepSplit / 100.0), 2, MidpointRounding.ToEven),
           }).ToList<Sale>();

The NetPrice member has values like 300.000000, 5000.500000, 3245.250000, etc.

Any clues? I can't seem to find anything on this issue online.

EDIT:

Decimal.Round did the trick (I forgot to mention that the NetPrice member was a Decimal type). See my answer below.

Upvotes: 3

Views: 13511

Answers (3)

AnthonyWJones
AnthonyWJones

Reputation: 189457

Trailing zeros can appear in the output of .ToString on the decimal type. You need to specify the number of digits after the decimal point you want display using the correct format string. For example:-

 var s = dec.ToString("#.00");

display 2 decimal places.

Internally the decimal type uses an integer and a decimal scaling factor. Its the scaling factor which gives rise to the trailing 0. If you start off with a decimal type with a scaling factor of 2, you will get 2 digits after the decimal point even if they are 0.

Adding and substracting decimals will result in a decimal which has a scaling factor of the is the maximum of the decimals involved. Hence subtracting one decimal with a scaling factor of 2 from another with the same the resulting decimal also has a factor of 2.

Multiplication and division of decimals will result in a decimal that has a scaling factor that is the sum of the scaling factors of the two decimal operands. Multiplying decimals with a scaling factor of 2 results in a new decimal that has a scaling factor of 4.

Try this:-

var x = new decimal(1.01) - (decimal)0.01;
var y = new decimal(2.02) - (decimal)0.02;
Console.WriteLine(x * y * x * x);

You get 2.00000000.

Upvotes: 2

Overhed
Overhed

Reputation: 1314

I got it to work using Decimal.Round() with the same arguments as before. :)

Looks like the issue is somewhat on the trail of what Pavel was saying, where Decimal types behave differently and it would seem Math.Round doesn't quite work with them as one would expect it to...

Thanks for all the input.

Upvotes: 0

Pavel Minaev
Pavel Minaev

Reputation: 101565

System.Decimal preserves trailing zeroes by design. In other words, 1m and 1.00m are two different decimals (though they will compare as equal), and can be interpreted as being rounded to different number of decimal places - e.g. Math.Round(1.001m) will give 1m, and Math.Round(1.001m, 2) will give 1.00m. Arithmetic operators treat them differently - + and - will produce a result that has the the same number of places as the operand which has most of them (so 1.50m + 0.5m == 1.10m), and * and / will have the sum of number of places for their operands (so 1.00m * 1.000m == 1.00000m).

Upvotes: 2

Related Questions