Retrieve three columns and group by one

Viewed 54


I have an SQL table composed with different columns corresponding to character data (in a game).
In these columns I would like to take the Level, the Date (it is when the data have been put on the table), and then a string corresponding to the character Script.

In brief, I need to have a Dictionary, or an alternative (in c# for sure) with script as key and in value a list of KeyValuePair (maybe?) with .

For instance, something like this: (it is not json, I'm just representing what I want)

"testScript" : 4 ; 09/03/2020 11:00:00,
               5 ; 09/03/2020 12:00:00,
               6 ; 10/03/2020 13:00:00,
               9 ; 10/03/2020 15:00:00,

"script7" : 7 ; 10/03/2020 14:00:00,
            8 ; 10/03/2020 14:30:00,

.....

It is in order to calculate the level average, and convert this list into Json to draw charts on google charts.

Here an example of what I tried, but it is not working at all lol...

Dictionary<string, Tuple<int, DateTime>> lvlList = archivedCharacterList.Select(i => new { i.Level, i.Updated_at, i.Script_Name })
                                                                                                   .GroupBy(c => c.Script_Name)
                                                                                                   .OrderBy(g => g.Key.Updated_at)
                                                                                                   .ToList();

Thank you for your time !


Update (solution):
Thanks to @StriplingWarrior

 ILookup<string, (int, DateTime)> lvlList = archivedCharacterList
   .Select(i => new { i.Level, i.Updated_at, i.Script_Name })
   .OrderBy(g => g.Updated_at)
   .ToLookup(c => c.Script_Name, c => (c.Level, c.Updated_at)); 

    foreach (var line in lvlList)
     {
         Console.WriteLine(line.Key); // Script 
         foreach ((byte, DateTime) items in line)
         Console.WriteLine("{0} {1}", items.Item1, items.Item2);
     }
// Then I just have to add this to what I want :)

this is working well

1 Answers

You'll want an ILookup (if you need to be able to do lookups by index).

ILookup<string, (int, DateTime)> lvlList = archivedCharacterList
    .Select(i => new { i.Level, i.Updated_at, i.Script_Name })
    .OrderBy(g => g.Updated_at)
    .ToLookup(c => c.Script_Name, c => (c.Level, c.Updated_at));
Related