[CSHARP-2034] FilterDefinition decimal scaling factor Created: 29/Aug/17  Updated: 09/Feb/22  Resolved: 09/Feb/22

Status: Closed
Project: C# Driver
Component/s: Linq
Affects Version/s: 2.3
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: espray Assignee: Unassigned
Resolution: Done Votes: 0
Labels: triaged
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Epic Link: CSHARP-3615

 Description   

I am having an issue when creating a FilterDefinition with a nullable decimal. The decimal scaling factor is not being honored.

– Example –
class Foo
{
public decimal? Amount

{ get; set; }

}

In MongoChef the Document looks like this

{ "_id" : ObjectId("5994596fcb337f716c4c827c"), "invoiceTotal" : "0.00" }

I want to get all documents where Amount != null and Amount != 0.00

IMongoCollection<Foo> _collection;
_collection.FindAsync(x => x.Amount != null && x.Amount != 0.00m)
Creates a FilterDefinition: "{ "amount" :

{ "$ne" : null }

, "amount" :

{ "$ne" : "0" }

}"

Document ObjectId("5994596fcb337f716c4c827c") is returned in the result set. But its Amount is 0.00

You can see the issue in the FilterDefinition the decimal scaling factor was not honored when the FilterDefinition was created.



 Comments   
Comment by James Kovacs [ 09/Feb/22 ]

Using .NET/C# driver 2.14.1:

var query = coll.Find(x => x.Amount != null && x.Amount != 0.00m);
Console.WriteLine(query);

produces the following output:

find({ "$and" : [{ "Amount" : { "$ne" : null } }, { "Amount" : { "$ne" : "0.00" } }] })

So appending .Value is no longer required.

As you discovered, precision (e.g. number of decimal places) matters when decimals are persisted as strings. "0" is different than "0.0" is different than "0.00".

Starting in MongoDB 3.4, the database can store decimals exactly using BsonType.Decimal. For backwards compatibility, the driver's default is still to store them as strings, but you can easily change this default while bootstrapping your application using:

BsonSerializer.RegisterSerializer<decimal>(new DecimalSerializer(BsonType.Decimal128));

The above query now produces:

find({ "$and" : [{ "Amount" : { "$ne" : null } }, { "Amount" : { "$ne" : NumberDecimal("0.00") } }] })

Note that the type is now NumberDecimal. This has the advantage that the database type is a numeric type and numeric comparisons can be performed. That means that 0 is the same as 0.0 is the same as 0.00 etc. You no longer have to take precision into account when writing your queries.

Comment by espray [ 08/Sep/17 ]

Verified the workaround works

Another odd thing the nullable decimal value in the document was "0" | "0.0" | "0.00". I would assume this was because when I created the document the nullable decimal field may have been zero filled.

My finale expression

_collection.FindAsync(x => x.Amount != null && (x.Amount.Value != 0m || x.Amount.Value != 0.0m || x.Amount.Value != 0.00m) )

Thanks!

Comment by Robert Stam [ 07/Sep/17 ]

I am able to reproduce this. It seems to be related to the fact that your Amount property is nullable.

As a workaround you can add .Value to the predicate:

_collection.FindAsync(x => x.Amount != null && x.Amount.Value != 0.00m)

Generated at Wed Feb 07 21:41:26 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.