Best practise in splitting up query result by a column

Question

I have a query which returns data for a number of dataseries in one lump.

I would like to split this data up into the data series id, by the dataseries id. The query cannot be changed.

Its not safe to assume that the data is ordered by the series id, so what would be the best way to do this? LINQ?

Answer 1

I suggest grouping the result by the DataSeriesId.

var groupedResult = QueryDatabase().GroupBy(item => item.DataSeriesId);

Now you can access the grouped data as follows. The example will just print all groups with all items.

 foreach(var group in groupedResult)
 {
     Console.WriteLine("Group: " + group.Key);

     foreach(var item in group)
     {
         Console.WriteLine("  Item: " + item);
     }
 }

Or you can group the database query result into a list of lists.

IList<IList<DataItem>> = QueryDatabase()
    .GroupBy(item => item.DataSeriesId)
    .Select(group => group.ToList())
    .ToList();

Or you can build a dictionary from DataSeriesId to a list of data items from the database query result.

IDictionary<Int32, IList<DataItem>> = QueryDatabase()
    .GroupBy(item => item.DataSeriesId)
    .ToDictionary(group => group.DataSeriesId, group => group.ToList());

Or use Enumerable.ToLookUp() if you don't want to alter the dictionary later.

UPDATE

Just noticed the "Best practice" in the question an the "DataReader" tag. Well LINQ is easy to write and easy to get right but it is probably not the fastest solution. So depending on your requirements using LINQ might be a good or bad choice. I would prefer LINQ if performance is not (yet) an issue. Else I would consider building a dictionary from DataSeriesId to a list of data items while reading the data.

IDictionary<Int32, IList<DataItem>> result =
    new Dictionary<Int32, IList<DataItem>>();

while (dataSource.DataAvailiable)
{
    DataItem item = dataSource.ReadItem();

    IList<DataItem> items;
    if (!result.TryGetValue(item.DataSeriesId))
    {
        items = new List<DataItem>();

        result.Add(item.DataSeriesId, items);
    }

    items.Add(item);
}