Another LINQ OrderBy query

Question

I have a table (as in a list of numbers) that gets recreated through a series of linq queries. I'm trying to sort the columns in descending order based upon the values within the first row.

My Database looks like this:

Data
{
   ColumnLabel,
   RowLabel,
   Value
}

{"Cat", "All", 9}
{"Dog","All", 17}
{"Fish", "All", 4}

{"Cat", "Girls", 3}
{"Dog","Girls", 2}
{"Fish", "Girls", 2}

{"Cat", "Boys", 6}
{"Dog","Boys", 15}
{"Fish", "Boys", 2}

When I naively join this table together it looks like this:

      Cat | Dog | Fish
All    9     17     4
Girls  3     15     2
Boys   6     2      2

What I want to do is sort the ColumnLabels in descending order based on Value where RowLabel == "All". The way I WANT the table to look is like this:

      Dog | Cat | Fish
All    17    9     4
Girls  15    3     2
Boys   2     6     2

Notice the Dog and Cat row flipped.

I'll need to out put this table using HTML so I iterate through by each row.

I start by sorting the ColumnLabels:

var top = data.Where(x => x.RowLabel == "All") .OrderBy(x => x.Data) .Select(x => x.ColumnLabel).SingleOrDefault();

Now top contains the order I want the Columns to appear; {Dog, Cat, Fish}. I don't really care how the Rows are sorted vertically.

What I then want to do is group each row based upon it's RowLabel value:

var row = data.GroupBy(x => x.RowLabel)
          .OrderBy(y => ??????????????);
          //I want to OrderBy the values in top
          //How do I do this?

I then iterate through each row and create my HTML table. This part is easy, but I don't know how to order subsequent rows based upon the top order. How do I do this in LINQ?

Answer 1

I believe this will do what you want:

var order = data
    .Where(x => x.RowLabel == "All")
    .OrderByDescending(x => x.Value)
    .Select((x, i) => new { x, i })
    .ToDictionary(x => x.x.ColumnLabel, x => x.i);
var result = data
    .GroupBy(x => x.RowLabel, (x, y) => y.OrderBy(z => order[z.ColumnLabel]));

foreach (var x in result)
{
    foreach (var y in x)
    {
        Console.WriteLine("ColumnLabel: {0}, RowLabel: {1}, Value: {2}", y.ColumnLabel, y.RowLabel, y.Value);                    
    }
}

Result:

ColumnLabel: Dog, RowLabel: All, Value: 17 
ColumnLabel: Cat, RowLabel: All, Value: 9 
ColumnLabel: Fish, RowLabel: All, Value: 4
ColumnLabel: Dog, RowLabel: Girls, Value: 2
ColumnLabel: Cat, RowLabel: Girls, Value: 3
ColumnLabel: Fish, RowLabel: Girls, Value: 2
ColumnLabel: Dog, RowLabel: Boys, Value: 15
ColumnLabel: Cat, RowLabel: Boys, Value: 6
ColumnLabel: Fish, RowLabel: Boys, Value: 2

Let me know if I misinterpreted your question, but the idea here is to create a dictionary order that provides the ordering for the subsequent group-by query. The other key idea is to use the resultSelector of group-by to order the elements within (since those represent the columns).

Answer 2

After thinking about this, this is what I came up with. It's more or less the same as Kirk's but doesn't use a dictionary. I was aiming for a single query to do this, (select ... into) really saved me there.

var query = from item in data
            where item.RowLabel == "All"
            orderby item.Value descending
            select item.ColumnLabel into columnOrder
            join item in data on columnOrder equals item.ColumnLabel
            group item by item.RowLabel;

var lquery = data.Where(item => item.RowLabel == "All")
                 .OrderByDescending(item => item.Value)
                 .Select(item => item.ColumnLabel)
                 .Join(data, columnOrder => columnOrder,
                             item => item.ColumnLabel,
                             (columnOrder, item) => item)
                 .GroupBy(item => item.RowLabel);