How to "downgrade" C# Decimal data type to some arbitrary number of decimal places?

I have a database table with a field defined in a very simplified form like that

CREATE TABLE Product
(
    ProductId INT,
    Price SMALLMONEY
)

I have a LINQ entity framework query that in it's simplified form goes something like:

Iqueryable query = from Product in testDB.Products
        select new
        {
            Price = Product.Price
        }

The problem is that this produces unexpected and unwanted behavior describes in my other post https://stackoverflow.com/questions/32610246/aspxtextbox-how-to-make-it-to-use-displayformatstring-correctly

I have researched this and I have concluded that this unwanted behavior is due to my query result returns field Price as Decimal with 4 decimal points. This is understandable as SMALLMONEY is stored in database with 4 decimal points of precision. If I reduce the number of decimals everything works ok. Something like this in this trivial example

Iqueryable query = from Product in testDB.Products
            select new
                {
                    Price = 1.12m;  // Decimal literal with 2 decimals
 }

So I thought it would be sufficient to round this number to two decimals and I will be ok.

Iqueryable query = from Product in testDB.Products
                select new
                {
                    Price = Decimal.Round(Products.Price,2) // round to two decimals    
                }

WRONG. Somehow the value Price persists the knowledge that it stores 4 decimal points and my web form is messed up.

I took a look at source code for DecimalRound() found at http://referencesource.microsoft.com/#mscorlib/system/decimal.cs

[System.Security.SecuritySafeCritical]  // auto-generated
public static Decimal Round(Decimal d, int decimals)
{
    FCallRound (ref d, decimals);
    return d;
}

This passes argument value by reference, so I guess only the value of argument is changed and not it's inner "meta" data describing precision.

What can I do? How can I downgrade the precision to two decimals.

Jon Skeet
people
quotationmark

I suspect the problem is that you're not actually performing the rounding using decimal.Round. Instead, you're expressing it in the query which is being translated into SQL.

If you want the rounding to be done in .NET instead, you need to make sure you're using LINQ to Objects - and the easiest way to do that is to call AsEnumerable. For example:

var results = testDB.Products
    .Select(p => new { p.Name, p.Price })
    .AsEnumerable()
    .Select(p => new { p.Name, Price = decimal.Round(p.Price, 2) });

Note that you should make sure you do any ordering, filtering etc before AsEnumerable(), to avoid it pulling all the results down and filtering locally.

As a side note, while there isn't a simple property to obtain the scale, sign and significand, you can use decimal.GetBits(decimal) to obtain the internal representation. This is useful for comparing whether two values are identical, and for more details you can consult the documentation as to what each bit means.

people

See more on this question at Stackoverflow