IQueryable complex order Only fields are allowed in a $sort

Question

I want to sort a list. When it was IEnumerable it was working fine, but I changed it to IQueryable to query MongoDB, it does not work. Giving me this error

System.NotSupportedException: 'Only fields are allowed in a $sort.'

//Query 1
var query = from d in list
        orderby
             d.Item.Value1 + d.Item.Value2 descending
        select d;

//Query 2
var query = from d in list
       orderby
            RecommendationFormula(d) descending
       select d;

private double RecommendationFormula(IItem d)
{
    var quality = ((int)(d.ValueX) / 18.0) * 50.0;
    var recent = ((DateTime.MinValue - (d.Item.Released ?? DateTime.MinValue)).TotalDays / (DateTime.MinValue - new DateTime(1990, 1, 1)).TotalDays) * 30;
    var rating = ((d.Item.XRating + d.Item.YRating) / 20) * 15;
    var quantity = (Quantity(d) / 1000.0) * 5;
    return quality + rating + recent + quantity;
}

I also understand that it does not support functions(as shown in Query 2),but Query 1 also gives the error, when I try ToList() the list. How can I write this kind of complex sort for IQueryable list?

Answer 1

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
            }
        }
    ]
}

Answer 2

I believe the result you are looking for can be achieved by using LINQ lambdas

var query = list.OrderByDescending(d => RecommendationFormula(d));

I believe you can also write it as the following to convert the lambda into a method group but please correct me if I'm wrong:

var query = list.OrderByDescending(RecommendationFormula);

Answer 3

It's about the way MongoDB driver works. Can you please cast the IQueryable to a list with ToList() just before using it? It will add an overhead since it needs to load all the elements in the query to memory first.

If the data you are using will create a headache due to the volume, I would suggest either solving it in the database or process the data in memory in chunks, e.g. using Skip() & Take().

P.S: The method RecommendationFormula(IItem d) can be a static method.

Answer 4

I was able to sort the issue, in a different way, but avoided bringing the objects to memory for filter and sorting. But it does take 2 queries to MongoDB.

1. Filter the List, _list is IQueryable

2. Sort the list using projection bringing in only Ids

private IQueryable<string> Sort(IQueryable<IItem> list)
    query = from d in list
            select new
            {
                Ranking =
                    ((int)(d.ValueX) / 18.0) * 50.0 +
                    (((d.XRating + d.YRating) / 20) * 15) * 20 +
                    (d.Quantity/ 1000.0) * 5,
                ReleaseYear = d.Item.Released.HasValue ? d.Item.Released.Value.Year : 0,
                d.Id
            }
        into q
            orderby
                q.ReleaseYear descending,
                q.Ranking descending
            select q.Id;

}

3. Use Skip and Take the sorted ids to a list.

var itemIds = this.Sort(_list).Skip(_pageSize * _page).Take(_pageSize).ToList();

4. Find only the objects in the ids list from MongoDB

var list = _list.Where(q => itemIds.Contains(q.Id)).ToList();

5. Order the real objects like the ids.

var list = list.OrderBy(q => itemIds.IndexOf(q.Id));