CODEWITHSUNDEEP
c#mongodbmongodb-queryaggregation-frameworkmongodb-.net-driver
Kelsey Abreu
Kelsey Abreu

Reputation: 1124

How to sort by condition using the mongo driver in c#

I've been following the mongo driver (2.4) documentation and am currently stuck trying to put a conditional on a sort.

Following the documentation, I got a AsQueryable instance and used the where function with an expression passed in. That works fine until the moment I want to order by a conditional instead of a field.

Here's what I'd like to order by:

db.child.AsQueryable().Where(expression).OrderBy(x=> x.parentId == someParentId);

which should be something along the lines of in linq

SELECT * FROM child
ORDER BY
  CASE parentId
  WHEN someParentId THEN 1
  ELSE 2 END,
  parentId;

Currently, however, after running the OrderBy condition I believe it's evaluating the expression because from the debugger I can see the expression change as shown below:

enter image description here

So the two main questions are:

  1. How do I sort by an boolean expression using the .net mongo driver.
  2. Why is the expression being converted as seen in the screenshot?

Mongo Driver Documentation (2.4)

Upvotes: 2

Views: 3783

Answers (1)

mickl
mickl

Reputation: 49945

As MongoDB C# driver documentation states (from pasted link):

The driver contains an implementation of LINQ that targets the aggregation framework.

So there has to be a corresponding operation in Aggregation Framework to get translated from LINQ. You call .AsQueryable() which means you can build your expression using LINQ syntax but then it fails when needs to be translated to Aggregation Framework. Unfortunately you can't have expressions inside $sort and that's why your code will fail.

To fix that you can use $addFields stage to add one addional field MatchesParent and the sort by this field.

Assuming your model is represented by following class:

public class Model
{
    [BsonId]
    public ObjectId Id { get; set; }
    public string ParentId { get; set; }
    // some other properties
}

you can add following class:

public class ModelResult: Model
{
    public bool MatchesParent { get; set; }
}

And then you can define $addFields as PipelineStageDefinition and nameof operator to keep it strongly typed:

PipelineStageDefinition<Model, ModelResult> addFields = new BsonDocument() {
            { "$addFields", new BsonDocument() {
                    { nameof(ModelResult.MatchesParent), new BsonDocument() {
                        { "$eq", new BsonArray() { "$" + nameof(Model.ParentId), someParentId } }
                    }
                }
            }
        }
    };

var result = Col.Aggregate()
                .Match(expression)                           
                .AppendStage(addFields)
                .SortByDescending(x => x.MatchesParent)
                .ToList();

Upvotes: 4

Related Questions