CODEWITHSUNDEEP
c#linq-to-sqlsorting
Marwan Roushdy
Marwan Roushdy

Reputation: 1230

Advanced Linq sorting C#

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 :(

Upvotes: 3

Views: 2424

Answers (5)

Luis Filipe
Luis Filipe

Reputation: 8708

Linq is expecting Calculatesort to return a "queryable" expression in order to generate its own SQL.

In can embed your 'calculatesort' method in this lambda expression. (I replaced your variables with constants in order to compile in my environment)

public static void ComplexSort(IQueryable<string> Pages)
{
    Pages.OrderByDescending(p =>
        {
            int rating = 99;//(from r in db.rating select r). sum();
            int comments = 33;//query database for comments;
            double timedecayfactor = Math.Exp(88);
            return (rating + comments) * timedecayfactor;
        });
}

Also, you can even try to run that in parallel (since .net 4.0) replacing the first line with 

    Pages.AsParallel().OrderByDescending(p =>

Upvotes: 0

Zasz
Zasz

Reputation: 12528

The error occurs because LINQ cannot convert custom code/methods into SQL. It can convert only Expression<Func<>> objects into SQL.

In your case, you have a complex logic to do while sorting, so it might make sense to do it using a Stored Procedure, if you want to do it in the DB Layer.

Or load all the objects into main memory, and run the calculate sort method on the objects in memory

EDIT :

I don't have the code, so Describing in english is the best I can do :

  • Have table with structure capable of temporarily storing all the current users data.
  • Have a calculated field in the Pages table that holds the value calculated from all the non-user specific fields
  • Write a stored procedure that uses values from these two sources (temp table and calc field) to actually do the sort.
  • Delete the temp table as the last part in the stored proc
  • You can read about stored procs here and here

Upvotes: 2

Eriawan Kusumawardhono
Eriawan Kusumawardhono

Reputation: 4896

Yes, counting previous answers: the LINQ to SQL doesn't know how to translate CalculateSort method. You should convert LINQ to SQL to ordinary LINQ to Object before using custom method.

Try to use this in the way you call the CalculateSort by adding AsEnumerable:

Pages.AsEnumerable().OrderByDescending(o => CalculateSort(o.page));

Then you're fine to use the OrderByDescending extension method.

UPDATE: LINQ to SQL will always translate the query in the code into Expression tree. It's quite almost the same concept as AST of any programming language. These expression trees are further translated into SQL expression specific to SQL Server's SQL, because currently LINQ to SQL only supports SQL Server 2005 and 2008.

Upvotes: -1

Rune FS
Rune FS

Reputation: 21742

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.

Upvotes: 2

Adrian Iftode
Adrian Iftode

Reputation: 15663

var comments = db.comments.Where(...);
Pages.OrderByDescending(p=>(db.rating.Sum(r=>r.rate) + comments.Count()) * Math.Exp(-p.totalhoursago))

Upvotes: 0

Related Questions