IQueryable complex order Only fields are allowed in a $sort – Asp.net

As it’s stated in the exception, the typed way to configure sorting in LINQ supports only fields and not calculated values. It looks like it’s a server restriction based on this. So it can be solved by adding projection:

        var query = from d in list
                    select new { Sum = d.Item.Value1 + d.Item.Value2, Item = d.Item } into projected
                    orderby projected.Sum descending
                    select projected;

that triggers this MQL:

{
    "aggregate": "coll",
    "pipeline": [{
            "$project": {
                "Sum": {
                    "$add": ["$Item.Value1", "$Item.Value2"]
                },
                "Item": "$Item",
                "_id": 0
            }
        }, {
            "$sort": {
                "Sum": -1
            }
        }
    ]
}

you can add more complex logic there, but don’t expect anything too complicated, in your particular case, supporting for Timespan properties will be triggering an unsupported exception, I’m not sure whether server even has equivalent for Timespan logic in .net.

If you still has to look at more complex scenarios that will trigger an unsupported exception with typed LINQ queries, you have 2 options:

1. Look at latest LINQ3 implementation. I know it supports more various cases in LINQ queries comparing it with LINQ2 (default), but I don’t have experience with it, so it requires more investigation.
2. You can look at whether your target can be archived with MQL server query itself (that definitely supports much more cases than can be implemented with LINQ provider). If you can construct query you need with a server syntax and get the result you need in mongo shell, you can pass this raw MQL into c# driver (pay attention that all definitions inside raw MQL should match with how server represents it). See this for details.

UPDATE:

1. It looks like $substract is supported by LINQ2:

        var query = from d in list
                    select new { Sum = (DateTime.Now - d.Item.Released), Item = d.Item } into projected
                    orderby projected.Sum descending
                    select projected;
   

but it cannot extract TotalDays and similar properties. However it looks the server stages support it via 2 operators:

1. Example of how to extract ms from a data via $substact.
2. You may look at dateDiff newly introduced operator also.

Idea is to create a raw MQL request that you can check in the shell similar to:

db.coll.aggregate(
[
// stage 1
{
    $addFields: {
        "DateDiff": {
            $dateDiff: {
                startDate: "$Item.Released",
                endDate: ISODate("2010-01-01"),
                unit: "day"
            }
        }
    }
},
// stage 2
{
    $addFields: {
        "ForSorting": {
            $divide : [ { $toInt : "$DateDiff"} , 9] // 9 is a random value, you can calculate it yourself 
        }
    }
},
// stage 3
{
    $sort : { "ForSorting" : -1 }
}
]
)

and then pass each stage separately via AppendStage method.

NOTE: you can combine typed stages(if it’s supported) and stages with raw MQL similar to:

        var result = coll
            .Aggregate()
            .Match(c => c.Item != null) // just example of supported typed stage
            .AppendStage<BsonDocument>("{ $sort : { Released : 1 } }") // such simple `$sort` is supported in typed way, here is just an example how a raw query can be appended to the pipeline
            .ToList();

The generated MQL for the above LINQ query will be:

{
    "aggregate": "coll",
    "pipeline": [{
            "$match": {
                "Item": {
                    "$ne": null
                }
            }
        }, {
            "$sort": {
                "Released": 1
            }
        }
    ]
}

UPDATE2
if fields in your projected output document match to fields in your input document, you can avoid reprojecting (ie additional server side step) and instead just replace the output serializer on the client side via As:

        var list = coll
            .Aggregate<Original>() // this class has only `Item` field
            .Project(i => new { Sum = i.Item.Value1 + i.Item.Value2, Item = i.Item }) // this expression can be generated dynamically
            .SortByDescending(s=>s.Sum)
            .As<Original>()
            .ToList();

The generated MQL for the above case will be:

{
    "aggregate": "coll",
    "pipeline": 
    [
        {
            "$project": {
                "Sum": {
                    "$add": ["$Item.Value1", "$Item.Value2"]
                },
                "Item": "$Item",
                "_id": 0
            }
        }, 
        {
            "$sort": {
                "Sum": -1
            }
        }
    ]
}