C# Linq Condition Inside Orderby

[Murat_Yuceer (Murat Yüceer)]

Hello,
I try to orderby like this

query = query.OrderByDescending(p => (p.LastModificationTime == null) ? p.LastModificationTime : p.CreationTime);

But its throw error “System.NotSupportedException: Only fields are allowed in a $sort”

How can I fix problem?

[James_Kovacs]

Hi, @Murat_Yuceer,

Thank you for asking about using OrderByDescending with an expression. Unfortunately you have encountered a server limitation. MongoDB only allows sorting by field names, not by expressions. Those field names can be synthesized using a projection, but the $sort has to resolve to a field name.

You can work around this by projecting into a field, sorting by that field, and then projecting away the sort field. In C#, this would look like this. (Note you have to use the new LINQ3 provider.)

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;

var settings = new MongoClientSettings { LinqProvider = LinqProvider.V3 };
var client = new MongoClient(settings);
var db = client.GetDatabase("test");
var coll = db.GetCollection<Record>("coll");

var query = coll.AsQueryable()
                .Select(p => new { Root = p, SortBy = p.LastModificationTime == null ? p.LastModificationTime : p.CreationTime })
                .OrderByDescending(p => p.SortBy)
                .Select(p => p.Root);
Console.WriteLine(query);

record Record(ObjectId Id, DateTime CreationTime, DateTime? LastModificationTime);

This produces the following output:

test.coll.Aggregate([{ "$project" : { "Root" : "$$ROOT", "SortBy" : { "$cond" : { "if" : { "$eq" : ["$LastModificationTime", null] }, "then" : "$LastModificationTime", "else" : "$CreationTime" } }, "_id" : 0 } }, { "$sort" : { "SortBy" : -1 } }, { "$project" : { "_v" : "$Root", "_id" : 0 } }])

We are considering implementing this in the LINQ3 provider directly. Please vote and follow CSHARP-3965 for updates on this feature.

Sincerely,
James

[Murat_Yuceer (Murat Yüceer)]

Thank you, your answer was very helpful.
I also voted