Sorting IQueryable by Aggregate in VB.net

Question

Hello all, been searching for a quick example of sorting a IQueryable (Using Linq To SQL) using a Aggregate value.

I basically need to calculate a few derived values (Percentage difference between two values etc) and sort the results by this.

i.e.

return rows.OrderBy(Function(s) CalcValue(s.Visitors, s.Clicks))

I want to call an external function to calculate the Aggregate. Should this implement IComparer? or IComparable?

thanks

[EDIT] Have tried to use:

Public Class SortByCPC : Implements IComparer(Of Statistic)

Public Function Compare(ByVal x As Statistic, ByVal y As Statistic) As Integer Implements System.Collections.Generic.IComparer(Of Statistic).Compare
    Dim xCPC = x.Earnings / x.Clicks
    Dim yCPC = y.Earnings / y.Clicks
    Return yCPC - xCPC
End Function

End Class

LINQ to SQL doesn't like me using IComparer

Answer 1

My VB is pretty bad, but I think this is what it should look like. This assumes that CalcValues returns a double and the type of rows is RowClass. This example does not use the IComparer version of the OrderBy extension but relies on the fact the doubles are comparable already and returns the CalcValue (assumed as double) as the key.

Dim keySelector As Func(Of Double, RowClass) = _
     Func( s As RowClass) CalcValue( s.Visitors, s.Clicks )

return rows.OrderBy( keySelector )

Here are some links you might find useful.

IQueryable.OrderBy extension method

Lambda expressions for Visual Basic

Answer 2

LINQ to SQL is never going to like you using your own methods within a query - it can't see inside them and work out what you want the SQL to look like. It can only see inside expression trees, built up from lambda expressions in the query.

What you want is something like:

Dim stats = From x in db.Statistics
            Where (something, if you want filtering)
            Order By x.Earnings / x.Clicks;

If you really want to fetch all of the results and then order them, you need to indicate to LINQ that you're "done" with the IQueryable side of things - call AsEnumerable() and then you can do any remaining processing on the client. It's better to get the server to do as much as possible though.

Answer 3

My solution:

Dim stats = rows.OrderBy(Function(s) If(s.Visitors > 0, s.Clicks / s.Visitors, 0))

This also catches any divide by zero exceptions