CODEWITHSUNDEEP
c#linqlinq-to-sql
guhou
guhou

Reputation: 1732

Aggregation in selectmany subquery using LINQ

I'm implementing some basic ranking functionality in my MVC application, but I'm running across some bizarre errors when executing this query:

The intent of this query is to find the top 10 riders with the longest overall time— ie the sum of all laps completed by each rider.

var topRiders = from rider in _riderDataProvider.GetAll()
                orderby
                    (from session in rider.Sessions
                     from lap in session.Laps
                     select lap.LapTime.TotalSeconds)
                     .Sum()
                descending
                select rider;
var result = topRiders.Take(10).ToArray();

However, executing the query throws:

System.Data.SqlClient.SqlException: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Frankly, I have no idea what this exception means or how to fix it. Is there a subtle bug I am missing, or have I exceeded the power of LINQ to SQL? (I am able to rewrite the query in SQL if that is the case, but would prefer not to.)

Upvotes: 1

Views: 1322

Answers (1)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47068

I would try something like this

var topRiders = from rider in _riderDataProvider.GetAll()
                select new {
                   Rider = rider,
                   TotalTime = (from session in rider.Sessions
                     from lap in session.Laps
                     select lap.LapTime.TotalSeconds)
                     .Sum(),
                }

var result = topRiders.OrderByDescending(r=>r.TotalTime)
                      .Select(r=>r.Rider)
                      .Take(10).ToArray();

Upvotes: 1

Related Questions