Advanced Linq sorting C#

Question

I have an IQueryable that has a list of pages.

I want to do: Pages.OrderByDescending(o => CalculateSort(o.page));

the method calculate sort is similar to that here is a plain english version:

public int calculatesort(page p)
{
    int rating = (from r in db.rating select r). sum();
    int comments = //query database for comments;

    float timedecayfactor = math.exp(-page.totalhoursago);

    return sortscore = (rating +comments)* timedecayfactor;
}

when I run a code similar to the one above an error is thrown that the mothode calculatesort cannot be converted to sql.

How can I do a conver the function above to be understood by sql so that I can use it to sort the pages?

Is this not a good approach for large data? Is there another method used to sort sets of results other than dynamically at the database?

I havent slept for days trying to fix this one :(

Answer 1

your code is nowhere near compiling so I'm guessing a lot here but I hope this gives an idea none the less. As several have posted you need to give Linq-2-Sql an expression tree. Using query syntax that's what happens (by compiler magic)

                     from p in pages
                     let rating = (from r in db.rating
                                   where r.PageId == p.PageId
                                   select r.Value).Sum()
                     let comments = (from c in db.Comments
                                     where c.PageId == p.PageId
                                     select 1).Count()
                     let timedecayfactor = Math.Exp(-(p.totalhoursago))
                     orderby (rating + comments)*timedecayfactor descending
                     select p;

I haven't actually tried this against a database, there's simply too many unknown based on your code, so there might still be stuff that can't be translated.