linq refactoring (possibly using nested group by clause)

Question

I have written some linq but I am not happy with it. I would like to refactor the two linq statements (possibly with a nested group by clause?) into just one clause.

var queryDB = from res in Results
        where res.SeasonYear == 2013
            group res by res.resultDate into g
            orderby g.Key
            select new 
            {
                ResultDate = g.Key
                , ResultList = g.OrderByDescending(r => r.Value)
            };

List<result> finalList = new List<result>();

foreach (var list in queryDB.Select(k=>k.ResultList).ToList())
{
    var query5 = from item in list
        where item.member.classifications.First(c => c.ClassificationYear == 2013).Value != "U"
        group item by item.member.classifications.First(c => c.ClassificationYear == ClassificationYear).Value into g
        orderby g.Key
        select g.ToList().OrderByDescending(r => r.Value).Take(3);

        query5.ToList().ForEach(c => c.ToList().ForEach(r => finalList.Add(r)));

}

The entities involved are:

public class Member
{    
    public string Surname { get; set; }
    public virtual ICollection<Result> Results { get; set; }
    public virtual ICollection<Classification> Classifications { get; set; }
}
public class Result
{
    public int Value { get; set; }
    public int SeasonYear { get; set; }
    public Member member { get; set; }
}
public class Classification
{
    public string Value { get; set; }
    public int ClassificationYear { get; set; }
    public Member member { get; set; }
}

A member has many results and many classifications (but only one classification per ClassificationYear).

I need to produce a list of results for a particular year (say 2013), sorted by date. The results need to have been grouped by the Value property of the classification class and the results are then ordered by the Value property of the result class.

As a tree, the result set/hierarchy might look like this:

13/04/2013
    classification.Value = A
        Result (Result.Value = 44)
        Result (Result.Value = 41)
        Result (Result.Value = 40)
    classification.Value = B
        Result (Result.Value = 42)
        Result (Result.Value = 39)
        Result (Result.Value = 36)
    classification.Value = C
        Result (Result.Value = 37)
        Result (Result.Value = 35)
        Result (Result.Value = 34)      

11/05/2013
    classification.Value = A
        Result (Result.Value = 40)
        Result (Result.Value = 39)
        Result (Result.Value = 38)
    classification.Value = B
        Result (Result.Value = 39)
        Result (Result.Value = 38)
        Result (Result.Value = 37)
    classification.Value = C
        Result (Result.Value = 38)
        Result (Result.Value = 36)
        Result (Result.Value = 35)  

But ultimately, all I actually want is a flat list of the result objects:

Result (.Value= 44,.Date= 13/04/2013,.Member.Classification.Value for 2013= A)
Result (.Value= 41,.Date= 13/04/2013,.Member.Classification.Value for 2013= A)
Result (.Value= 40,.Date= 13/04/2013,.Member.Classification.Value for 2013= A)
Result (.Value= 42,.Date= 13/04/2013,.Member.Classification.Value for 2013= B)
Result (.Value= 39,.Date= 13/04/2013,.Member.Classification.Value for 2013= B)
Result (.Value= 36,.Date= 13/04/2013,.Member.Classification.Value for 2013= B)
Result (.Value= 37,.Date= 13/04/2013,.Member.Classification.Value for 2013= C)
Result (.Value= 35,.Date= 13/04/2013,.Member.Classification.Value for 2013= C)
Result (.Value= 34,.Date= 13/04/2013,.Member.Classification.Value for 2013= C)
Result (.Value= 40,.Date= 11/05/2013,.Member.Classification.Value for 2013= A)
Result (.Value= 39,.Date= 11/05/2013,.Member.Classification.Value for 2013= A)
Result (.Value= 38,.Date= 11/05/2013,.Member.Classification.Value for 2013= A)
Result (.Value= 39,.Date= 11/05/2013,.Member.Classification.Value for 2013= B)
Result (.Value= 38,.Date= 11/05/2013,.Member.Classification.Value for 2013= B)
Result (.Value= 37,.Date= 11/05/2013,.Member.Classification.Value for 2013= B)
Result (.Value= 38,.Date= 11/05/2013,.Member.Classification.Value for 2013= C)
Result (.Value= 36,.Date= 11/05/2013,.Member.Classification.Value for 2013= C)
Result (.Value= 35,.Date= 11/05/2013,.Member.Classification.Value for 2013= C)

The first linq statement groups the data initially by date and then ResultList contains all the results for that date.

The 2nd linq statement in the loop then processes each ResultList, groups the data by classification (ignoring some classifications) and sorts those results, finally taking the top 3 results.

The loop also has (what I consider to be) a horendous .ToList(ForEach(.ToList(.ForEach()))) lambda expression, to flatten out the hierarchy into a list of result objects.

So, whilst this actually does work, it is pretty horrible. How can I merge the two linq statements and replace the lambda expression to flatten the results?

Answer 1

ok, so the final linq that I came up with was this, including SelectMany at the end to flatten into a list:

var filteredResults = 
   from res in Results
   where res.SeasonYear == 2013
   orderby res.resultDate
   group res by res.resultDate into dateGroup
   select new
   {
      ResultDate = dateGroup.Key,
      ResultList =
         from r in dateGroup
         where r.member.classifications.Single(c => c.year == 2013).Value != "U"
         group r by r.member.classifications.Single(c => c.year == 2013).Value into classifications
         orderby classifications
         select new
         {
            ClassificationGroup = classifications.Key,
            ClassificationList =                                     
               from r2 in classifications.OrderByDescending(r => r.Value).Take(3)
               select r2
         }          
   };       

   finalList = filteredResults.SelectMany(item => item.ResultList)
                              .SelectMany(inner=>inner.ClassificationList)
                              .ToList();