Linq order by decimal field sorts like a string?

Question

I'm totally baffled and cannot find anything on the internet about this, so I must be doing something wrong?

 _positionsRepo.GetAllTrades().OrderByDescending(x => x.TotalPLPercent).ToList();

TotalPLPercent is a decimal field. the result order sorts like this:

Obviously, this is wrong. I tested the sort against another field that was a double, and it worked as expected. What am I missing here about decimals in C#?

I am using the Mongo DB C# driver.

Try putting a `.ToList().` in between `GetAllTrades()` and `OrderByDescending()`. That way, the list will be sorted in memory, rather than in the database (which I suspect is something to do with it). — Blorgbeard, Jul 16 '14 at 04:25
it's absolutely not a string... but sorts like one, hence the confusion. — ijjo, Jul 16 '14 at 04:30
OK, looks like MongoDB does not have a native decimal type. Are you *sure* it's not a string? It might serialize to a C# decimal, but in the database, it may be a string. — Blorgbeard, Jul 16 '14 at 04:31
hey blorgbeard got the answer. i had a suspicion of trying this, but didn't think this could be causing it! works fine now. but now i don't get the performance benefit of the deferred execution because the ToList gets the ENTIRE collection from the DB, THEN sorts. sounds like a bug in he monggoDB C# driver. — ijjo, Jul 16 '14 at 04:32
yes blorgbeard, that sounds right. i bet it's a string in the database. seems like a bug. — ijjo, Jul 16 '14 at 04:35

score 5 · Accepted Answer · edited May 23 '17 at 12:20

5

The C# Mongo DB driver serializes decimal as a string (The driver source-code corroborates this).

It does this because there is no BSON type for decimal - double does not have the same precision. Unfortunately it means you can't compare "decimal" values as numbers.

You could sort the data in memory, like this:

_positionsRepo.GetAllTrades()
   .ToList()
   .OrderByDescending(x => x.TotalPLPercent)
   .ToList();

Another option would be to store the number of cents (or hundredths of cents) as a long integer. Then you can sort them normally, and you just have to divide by 100 (or 10,000) to get your real value.

edited May 23 '17 at 12:20

Community

1
1

answered Jul 16 '14 at 04:35

Blorgbeard

101,031
48
228
272

thanks. works. but seems like not ideal for performance now. – ijjo Jul 16 '14 at 04:36
Well, you are retrieving all rows anyway, I don't think it will make *too* much difference, unless your data-set is really large. – Blorgbeard Jul 16 '14 at 04:38
you have to make it vice versa , if you need order descending make it ascending \ – hosam hemaily Nov 25 '20 at 10:24

score 2 · Answer 2 · answered Jul 16 '14 at 04:35

2

Apparently, this is the problem with MongoDB Driver for C#. Check this open issue here -

the 'System.Decimal' type save as 'String'

As you can see since Decimal is saved as String, it surely will sort like a string. I think you should try double instead of decimal until they fix this issue.

answered Jul 16 '14 at 04:35

brainless coder

6,310
1
20
36

It's dangerous to use `double` for currency values - you get floating point rounding errors. – Blorgbeard Jul 16 '14 at 04:46

score 1 · Answer 3 · answered Jul 16 '14 at 04:30

1

Are you sure the type of x is Decimal? It looks like it's doing a string ordering. (I'd put a breakpoint on "x.TotalPLPercent" and then evaluate x's type to double verify it's what I thought it was.)

answered Jul 16 '14 at 04:30

Harley Pebley

103
5

yeah it's most definitely a decimal. i think it's a bug in the mongodb C# driver with decimals. since maybe mongo looks at it as a string. check the comments on my question, the solution is there. not ideal, but it works. – ijjo Jul 16 '14 at 04:34

Linq order by decimal field sorts like a string?

3 Answers3

Linked

Related