Create a MongoDB filter that looks for a fraction of a value

Question

I have a list of price data I would like to iterate through to find when currentprice of a given item is less than half of its historical expectedprice.

I have a method that should return a list of items stored in MongoDB that are priced as such:

    public List<Sale> GetDeals()
    {
        var collection = _database.GetCollection<Sale>("listOfSales");

        var filter = Builders<Sale>.Filter.Where(sale => sale.currentprice < sale.expectedprice * 0.5);

        return collection.Find(filter).ToList();
    }

Problem is, I get the following error:

MongoDB.Driver.Linq.ExpressionNotSupportedException: 'Expression not supported: 0.5 in (Convert(sale.expectedprice, Double) * 0.5) because it was not possible to determine how to serialize the constant.'

My Sale class looks like this:

using MongoDB.Bson;
using System.Text.Json.Serialization;

public class Sale
{
    [JsonPropertyName("_id")]
    public ObjectId id { get; set; }
    
    [JsonPropertyName("currentprice")]
    public long currentprice { get; set; }

    [JsonPropertyName("quantity")]
    public long quantity { get; set; }

    [JsonPropertyName("expectedprice")]
    public long expectedprice { get; set; }
}

Is there a better way I can use filters/aggregates/pipelines in MongoDB to do this?

Answer 1

It looks like this query is not supported by LINQ provider. You can do it via plain/raw MQL query.

    db.collection.find(
    {
      $expr: 
      {
        $lt: 
        [
            "$currentprice",
            { $multiply: [ "$expectedprice", NumberDecimal("0.5")] }
        ]
      }
    })

See here how it works. In c#, you can specify it in this way:

    FilterDefinition<Sale> filter = @"
    {
        $expr: 
        {
            $lt: 
            [
                ""$currentprice"",
                { $multiply: [ ""$expectedprice"", NumberDecimal(""0.5"")] }
            ]
        }
    }";

The actual filter that will be sent to the server will be the same:

    { "$expr" : { "$lt" : ["$currentprice", { "$multiply" : ["$expectedprice", NumberDecimal("0.5")] }] } }

Pay attention that currentprice and expectedprice must have the same name as in database, bson configuration like BsonElement won't affect filters in a raw MQL form. Btw attributes like JsonPropertyName also have no power here