Linq: OrderBy Sum of multiple fields
I'm trying to create a linq expression whereby I can COUNT the numb开发者_Go百科er of event comments as well as SUM the number of Votes. Here's what I've got, but I'm not getting the appropriate results.
My example is done in VB.Net, but I can deal with examples in C# as well.
Public Function GetHotEvents(ByVal skip As Integer) As List(Of Domain.Event) Implements IEventService.GetHotEvents
''# Our order by sequence just takes the number of votes and multiplies
''# it by two. Then takes the number of comments and adds it to the
''# vote count. This way up-voted events will climb to the top while
''# down-voted events will fall to the bottom. Comments also add to
''# the "hotness" of the event.
Return _EventRepository.Read() _
.Where(Function(e) e.EventDate >= Date.Today) _
.OrderBy(Function(e) (((e.EventVotes.Sum(Function(s) s.Vote)) * 2) + (e.Comments.Count))) _
.Skip(skip) _
.Take(5) _
.ToList()
End Function
What I've done to test this is have ZERO comments on all events, and upvote ONE event. That event "should" float to the top, but it's not there.
Any help would be greatly appreciated.
edit
I've tried building the expression in LinqPad but unfortunately it threw an error (note: this error doesn't get thrown in my code)
Overload resolution failed because no accessible 'OrderBy' can be called with these arguments: Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Linq.Expressions.Expression(Of System.Func(Of LINQPad.User.Events, TKey))) As System.Linq.IOrderedQueryable(Of LINQPad.User.Events)' defined in 'System.Linq.Queryable': 'Comments' is not a member of 'LINQPad.User.Events'. Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Linq.Expressions.Expression(Of System.Func(Of LINQPad.User.Events, TKey))) As System.Linq.IOrderedQueryable(Of LINQPad.User.Events)' defined in 'System.Linq.Queryable': Data type(s) of the type parameter(s) cannot be inferred from these arguments. Specifying the data type(s) explicitly might correct this error. Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Func(Of LINQPad.User.Events, TKey)) As System.Linq.IOrderedEnumerable(Of LINQPad.User.Events)' defined in 'System.Linq.Enumerable': 'Comments' is not a member of 'LINQPad.User.Events'. Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Func(Of LINQPad.User.Events, TKey)) As System.Linq.IOrderedEnumerable(Of LINQPad.User.Events)' defined in 'System.Linq.Enumerable': Data type(s) of the type parameter(s) cannot be inferred from these arguments. Specifying the data type(s) explicitly might correct this error.
edit 2
Here's the generated SQL
DECLARE @p0 Int = 2
SELECT *
FROM [dbo].[Events] AS [t0]
ORDER BY (((
SELECT SUM([t1].[Vote])
FROM [dbo].[EventVotes] AS [t1]
WHERE [t1].[EventID] = [t0].[ID]
)) * @p0) + ((
SELECT COUNT(*)
FROM [dbo].[Comments] AS [t2]
WHERE [t2].[EventID] = [t0].[ID]
))
Did you mean OrderByDescending
? ;)
精彩评论