Update using Expressions

[Charles_Stephen_Vice (Charles Stephen Vicencio)]

Hello and good day!

I am quite new to MongoDb (and MongoDb C# Drivers) and lately, we are trying to implement an update wherein we use the value of a field and a variable (method parameter) to update several fields.

Basically, our doc is something like this

public class Inventory
{
public string _id;
public decimal quantity;
public decimal totalCost;
}

What we want to do is to update the quantity and totalCost based on a passed value (qty).
(1) totalCost -= (qty * (totalCost / quantity))
(2) quantity -= qty

The logic behind this is that we want to retain the average cost of our item.
Take note: the value of quantity field in step (1) should use the original value and not the result of step (2).

We can implement this using 2 queries but in our case, we need to execute this logic in one call only as there are different threads that will update a single item.

I have read the docs about Aggregate and Projection (and using Expressions) but I cannot seem to figure out how to use or combine the result of projection into the aggregate update.

Tried this projection to return the value that should be deducted from totalCost

Builders.Projection.Expression(e => (e.totalCost / e.quantity) * -qty);

Thank you and hope you can point us in the right direction.

[Charles_Stephen_Vice (Charles Stephen Vicencio)]

Here is an equivalent of what we are trying to achieve in mongo shell, provided that qty = 500.

db.inventory.updateOne( { _id: “1” }, [ { “$set”: { “TotalCost”: { “$add”: [“$TotalCost”, { “$multiply”: [-500, { “$divide”: [“$TotalCost”, “$Quantity”] }] }] } } } ] )

[wan (Wan Bachtiar)]

Hi @Charles_Stephen_Vice , and welcome to the forum!

@Charles_Stephen_Vice

I have read the docs about Aggregate and Projection (and using Expressions) but I cannot seem to figure out how to use or combine the result of projection into the aggregate update.

You can either use BsonDocument to construct PipelineDefinition as below example:

var newQuantity = 500;
 var pipeline = new BsonDocumentStagePipelineDefinition<BsonDocument, BsonDocument>(
                new[] { new BsonDocument("$set", 
                  new BsonDocument{{"TotalCost", 
                    new BsonDocument("$add", 
                      new BsonArray{ "$TotalCost", 
                        new BsonDocument("$multiply", 
                          new BsonArray{newQuantity, 
                            new BsonDocument("$divide", 
                              new BsonArray{ "$TotalCost", "$Quantity"}
                              )})})}, 
                  {"Quantity", new BsonDocument("$subtract", 
                    new BsonArray{"$Quantity", newQuantity}
                  )}})}
);
var updateDefinition = new PipelineUpdateDefinition<BsonDocument>(pipeline);
var result = collection.UpdateOne(new BsonDocument{}, updateDefinition);

Alternatively, you could utilise BsonDocument.Parse() to build from a string pipeline, i.e.

var newQuantity = 500;
string patternPipeline = @"{{ '$set': {{ 
                'TotalCost': {{ 
                    '$add': ['$TotalCost', {{ 
                        '$multiply': [ {0} , {{ 
                            '$divide': ['$TotalCost', '$Quantity']
                            }}] 
                }}]}}, 
                'Quantity': {{
                    '$subtract': ['$Quantity', {1}]
                }} }} }}"; 
string updatePipeline = string.Format(patternPipeline, newQuantity, newQuantity); 
var pipeline = new BsonDocumentStagePipelineDefinition<BsonDocument, BsonDocument>(
               new[] { BsonDocument.Parse(updatePipeline)});
var updateDefinition = new PipelineUpdateDefinition<BsonDocument>(pipeline);
var result = collection.UpdateOne(new BsonDocument{}, updateDefinition);

Regards,
Wan.