OrderBy() a List<> property

Question

I have a problem to order a list after a certain condition. The situation is that i build a Competition application for Range shooting and when i am about to order the list of results there is on condition i dont know how to handle with OrderBy().

This is the classes i work with, simplified to only include properties important for this question, and the Shot class is no point in showing here. I have a List of ShooterSignup that i work with and try to order:

ShooterSignup

public class ShooterSignup
{
    public List<Serie> Series { get; set;}
}

Serie

public class Serie
{
    public List<Shot> Shots { get; set;}
}

The Conditions i have to order the results by are:

1. Total score of all series
2. Number of Center hits

These two OrderBy are straight forward and no problem. But the next OrderBy step is where i cant get it to work. The next step to order by is:

1. Highest score on last Serie
2. Highest score on next to last serie
3. Highest score on the one before that one
4. ......

and so on for the number of series shot this competition. How can i achive this orderby? Is it even possible?

Edit: This is done in a query to entity framework, so i cant use any methods created on the classes themselves since entity framework will not know how to translate that into a stored procedure

Answer 1

Let the classes are defined as follows:

public class ShooterSignup
{
    public List<Serie> Series { get; set; }

    public int GetTotalScore()
    {
        return Series.Sum(serie => serie.GetScore());
    }

    public int GetCenterHits()
    {
        return Series.Sum(serie => serie.GetCenterHits());
    }
}

public class Serie
{
    public List<Shot> Shots { get; set; }

    public int GetScore()
    {
        return Shots.Sum(shot => shot.Score);
    }

    public int GetCenterHits()
    {
        // assume that center hit is a hit with score = 10
        return Shots.Count(shot => shot.Score == 10);
    }
}

public class Shot
{
    public Shot(int score)
    {
        Score = score;
    }
    public int Score { get; set; }
}

You can define series-wise comparer like this:

Comparer<ShooterSignup> seriesComparer = Comparer<ShooterSignup>.Create((a, b) =>
{
    using (var enumeratorA = Enumerable.Reverse(a.Series).GetEnumerator())
    using (var enumeratorB = Enumerable.Reverse(b.Series).GetEnumerator())
    {
        bool moveNextA = enumeratorA.MoveNext();
        bool moveNextB = enumeratorB.MoveNext();

        while (moveNextA && moveNextB)
        {
            int scoreA = enumeratorA.Current.GetScore();
            int scoreB = enumeratorB.Current.GetScore();
            if (scoreA != scoreB)
            {
                return scoreB - scoreA;
            }

            moveNextA = enumeratorA.MoveNext();
            moveNextB = enumeratorB.MoveNext();
        }

        if (!moveNextA && !moveNextB)
        {
            return 0;
        }

        return moveNextA ? 1 : -1;
    }
});

Now your can sort list of ShooterSignup:

var orderedSignups = signups
    .OrderBy(s => s.GetTotalScore())
    .ThenBy(s => s.GetCenterHits())
    .ThenBy(s => s, seriesComparer)
    .ToList();

Update. I'm not sure if it is possible to form LINQ to SQL query that returns ShooterSignup list in such an order. The easier way would be to get accumulated totals for each Serie from the database and do ordering on the client side.

You can make server-side query like this:

var seriesTotals = context.Shots
    .GroupBy(shot => shot.SerieId)
    .Join(context.Series,
        g => g.Key,
        serie => serie.Id, (grp, serie) => new { grp, serie })
    .Join(context.ShooterSignup,
        t => t.serie.SignupId, signup => signup.Id,
        (t, signup) => new { t, signup })
    .OrderByDescending(t => t.t.serie.Id)
    .Select(total => new
    {
        SerieId = total.t.grp.Key,
        SignupId = total.signup.Id,
        SignupName = total.signup.Name,
        SerieScore = total.t.grp.Sum(s => s.Score),
        CenterHits = total.t.grp.Count(s => s.Score == 10)
    });

The result will be, for example, as follows:

SerieId	SignupId	SignupName	SerieScore	CenterHits
1	1	A	99	8
2	2	B	95	6
3	1	A	90	3
4	2	B	94	5

Now you can group and order series by shooters on the client:

var series = seriesTotals.AsEnumerable()
    .GroupBy(s => s.SignupId)
    .Select(gr => new
    {
        gr.FirstOrDefault().SignupName,
        TotalScore = gr.Sum(s => s.SerieScore),
        CenterHits = gr.Sum(s => s.CenterHits),
        Scores = string.Join(",", gr.Select(s => s.SerieScore.ToString().PadLeft(3, '0'))) // aggregate all scores in a single string for each ShooterSignup
    })  
    .OrderByDescending(g => g.TotalScore)
    .ThenByDescending(g => g.CenterHits)
    .ThenByDescending(g => g.Scores)
    .ToList();

This query will produce something like:

SignupName	TotalScore	CenterHits	Scores
B	189	11	094,095
A	189	11	090,099