MongoDB C# Case Insensitive Sort and Index

Question

So far I've been using this code to find my documents and then sort them:

        var options = new FindOptions
        {
            Modifiers = new BsonDocument("$hint", "PathTypeFilenameIndex")
        };

        return await Collection
            .Find(f => f.Metadata["path"] == path, options)
            .SortBy(f => f.Metadata["type"])
            .ThenBy(f => f.Filename)
            .ToListAsync();

I have a class that has Metadata field with path and type fields, also the class has a Filename field. I want all documents with a given path inside the metadata sorted by type and then by Filename.

An example result would be a list of documents ordered by the Name field like this:

a, Ab, B, c, D

Unfortunately, I get something like this:

Ab, B, D, a, c

And that's because MongoDB sorts the data with a simple binary comparison, where 'A' < 'a' because of their ASCII codes.

So my question is: Is there a way to make a case insensitive sort and keep using the "$hint"?

That options I pass to the Find method should tell MongoDB which index to use. I found this post: MongoDB and C#: Case insensitive search but the method here doesn't work for sorting and I couldn't tell MongoDB which index to use.

Answer 1

I think you can use aggregation pipeline with $addFields, $toLower (to convert filename to lowercase in temporary field), and $sort to sort them irrespective of the case

In mongodb shell you would write something like this :

db.collection.aggregate([{
    $addFields : {
        "lowercaseFileName" : {
            $loLower : "$fileName"
        }
    },{
        $sort : {
            "metadata.type" : 1,
            lowercaseFileName : 1
        }
    }
}])

Please write the similar code in c#, and see if it works. I dont know c#, otherwise i would have given you the exact query, but i cant.

The idea is to transform the filename to lowercase, save it in temporary field, using addFields and sort by that field.

Hope this helps you out.

Read more about $addFields, $toLower here.

Update

For whoever wants a working code in C# , thanks to @kaloyan-manev

You can use this :

return await Collection.Aggregate()
    .Match(f => f.Metadata["path"] == path) 
    .AppendStage<BsonDocument>(new BsonDocument("$addFields", new BsonDocument("lowercaseFileName", new BsonDocument("$toLower", "$filename")))) 
    .AppendStage<GridFSFileInfo>(new BsonDocument("$sort", new BsonDocument { {"metadata.type", 1}, {"lowercaseFileName", 1} }))
    .ToListAsync();

Answer 2

Did you try to set the CollationStrenght = 2?

Your code would be similar all you need is to set the Collation in the FindObject:

 var options = new FindOptions
    {
        Modifiers = new BsonDocument("$hint", "PathTypeFilenameIndex"),
        Collation = new Collation("en", strength: CollationStrength.Secondary)
    };