Trim Values existing in Mongo Database

Question

The data in my collection may have white spaces in the front and at the back what I want to do is trim all white spaces and make a == comparison to get the appropriate record my code below:

var test = await _dataStore.FindMostRecentAsync(x => x.Barcodes.PrimaryBarcode.Trim() == barcode.Trim());

When I run this code it gives me an error .Trim() not supported (it works only when I trim the barcode string variable that I pass in.

What is the best way to trim the data in my collection so I can have an exact comparison.

Stack Trace

at MongoDB.Driver.Linq.Translators.PredicateTranslator.GetFieldExpression(Expression expression) at MongoDB.Driver.Linq.Translators.PredicateTranslator.TranslateComparison(Expression variableExpression, ExpressionType operatorType, ConstantExpression constantExpression) at MongoDB.Driver.Linq.Translators.PredicateTranslator.Translate(Expression node) at MongoDB.Driver.Linq.Translators.PredicateTranslator.Translate(Expression node, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.MongoCollectionImpl1.CreateFindOperation[TProjection](FilterDefinition1 filter, FindOptions2 options) at MongoDB.Driver.MongoCollectionImpl1.FindAsync[TProjection](IClientSessionHandle session, FilterDefinition1 filter, FindOptions2 options, CancellationToken cancellationToken) at MongoDB.Driver.MongoCollectionImpl1.<>c__DisplayClass37_01.b__0(IClientSessionHandle session) at MongoDB.Driver.MongoCollectionImpl1.UsingImplicitSessionAsync[TResult](Func2 funcAsync, CancellationToken cancellationToken)

Answer 1

You'll have to use the aggregate function to be able to call the trim operator.

Sadly there is no direct way of calling through the C# driver however you can build one using some BsonDocuments like so:

var barcode = "     1512356      ";


//This exclude the trimmedField from the result.
var projectionDefinition = Builders<BsonDocument>.Projection.Exclude("trimmedField");  
//Call the trim operator and put it in the temporary trimmedField property (this trims the barcode on the database)
var expression = new BsonDocument(new List<BsonElement>
    {
        new BsonElement("trimmedField", new BsonDocument(new BsonDocument("$trim", new BsonDocument("input", "$Barcodes.PrimaryBarcode"))))
    });

//Add the trimmedField to the document
var addFieldsStage = new BsonDocument(new BsonElement("$addFields", expression));

//Build a filter on the trimmedField and trim the local variable
var trimFilter = Builders<BsonDocument>.Filter.Eq(x => x["trimmedField"], barcode.Trim());

//Put it all together
var result = collection.Aggregate().AppendStage<BsonDocument>(addFieldsStage).Match(trimFilter).Project(projectionDefinition).As<YourType>().ToList();

Make sure to put the correct Type in the .As<T> to be able to cast the entity.

And if you add [BsonIgnoreExtraElements] above your class you'll be able to drop the projection stage.