IQueryable complex order Only fields are allowed in a $sort-CodePudding

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?

CodePudding user response：

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);

CodePudding user response：

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.