Get how many times the array appears in the list <>

Question

I am developing a section with some statistics for my site, and I am having problems figuring out how to get “popular stuff” (explanation below):

I have the following table:

ID | Spell1ID | Spell2ID
1 | 4 | 12
2 | 4 | 12
3 | 12 | 4
4 | 1 | 8
5 | 3 | 12
6 | 8 | 1

To get this data, I do the following:

List<short[]> spellsList = new List<short[]>();

..

            using (MySqlDataReader dbReader = Conex.Command.ExecuteReader())
            {
                while (dbReader.Read())
                {
                    short[] tempArray = new short[2];

                    tempArray[0] = dbReader.GetInt16("spell1ID");
                    tempArray[1] = dbReader.GetInt16("spell2ID");

                    spellsList.Add(tempArray);
                }
            }

Now I need to calculate which of these values ​​are the most common (the descent from the most common to the less common), and the order of the values ​​of each array in the list should not be important ([4,12] and [12,4] are the same, because what I really need is SpellID and how often it is used), so for this example it would be:

1- 4, 12 (3 times)
2- 1,8 (2 times)
3- 3,12

It would be great if this can be done using LINQ pref with lambda expressions.

, , .

Answer 1

    static void Main(string[] args)
    {
        var spellsList = new List<short[]>();

        spellsList.Add(new short[] {4, 12 });
        spellsList.Add(new short[] {4, 12 });
        spellsList.Add(new short[] {12, 4 });
        spellsList.Add(new short[] { 1, 8});
        spellsList.Add(new short[] {3, 12});
        spellsList.Add(new short[] {8, 1 });
        spellsList.Add(new short[] {8, 1 });
        spellsList.Add(new short[] {8, 1 });
        spellsList.Add(new short[] {8, 1 });


        var result = spellsList.Select(s => s[0] > s[1] ? String.Format("{0},{1}", s[0], s[1]) : String.Format("{0},{1}", s[1], s[0]))
                               .GroupBy(s => s)
                               .OrderByDescending(g => g.Count())
                               .ToList();

        result.ForEach(g => Console.WriteLine($"{g.Key}: {g.Count()} times"));

        Console.Read();
    }

Answer 2

// fake data
var data = @"1 | 4 | 12
            2 | 4 | 12
            3 | 12 | 4
            4 | 1 | 8
            5 | 3 | 12
            6 | 8 | 1"
.Split('\n')
.Select(x => x.Split(new[] { " | " }, StringSplitOptions.None));

var spellsList = data.Select(x => new[]
{
    int.Parse(x[1]), int.Parse(x[2])
});

// query
var combos = spellsList
    .GroupBy(spells => string.Join(", ", spells.OrderBy(x => x)), (k, g) => new
    {
        SpellCombo = k,
        CastCount = g.Count(),
    })
    .OrderBy(x => x.CastCount)
    .ToList();

foreach(var combo in combos)
{
    Console.WriteLine($"Combo {combo.SpellCombo} is casted {combo.CastCount} times. ");
}