Grouping over a list of dictionaries

Question

I am consuming data from an API that reads from a SharePoint list. The API is basically this:

IList<Dictionary<string,string>> GetListData(string listName);

So the data is a list of dictionaries - each dictionary containing value-pairs (column name, column value) for a "row" in the list.

I need to group over the results, doing a sum over one column.

I have made code that does exactly that - but I am not entirely happy with it.

The code is this:

[Test]
public void TestOfSharePointResult()
{
    //Arrange
    IList<Dictionary<string, string>> sharePointResult = new List<Dictionary<string, string>>
    {
        new Dictionary<string, string> { {"TO", "101"}, { "PA", "123" } },
        new Dictionary<string, string> { {"TO", "202"}, { "PA", "456" } },
        new Dictionary<string, string> { {"TO", "404"}, { "PA", "123" } }
    };

    //Act
    var valuePairs = sharePointResult
        .Select(dict => new KeyValuePair<string, int>(dict["PA"], dict["TO"].FromProjectValueToInt()))
        .GroupBy(d => d.Key)
        .Select(g => new { PA = g.Key, Sum = g.Sum(f => f.Value) })
        ;

    //Assert

    // Result must be:
    //  PA  Sum
    //  --------
    //  123 505
    //  456 202
    var TOValueFor123 = valuePairs.First(i => i.PA == "123").Sum;
    var TOValueFor456 = valuePairs.First(i => i.PA == "456").Sum;
    Assert.AreEqual(505, TOValueFor123);
    Assert.AreEqual(202, TOValueFor456);
}

The "FromProjectValueToInt()" extension basically converts a string to an int.

The reason I am not entirely happy with this code is line 14, where I am doing an explicit extraction of values from the dictionary entries to a KeyValuePair struct.

Is there a more implicit way of looping over the list of dictionaries that I am missing?

Answer 1

You don't need neither Select and can get the same result by using only GroupBy:

var valuePairs = sharePointResult
    .GroupBy(
        dict => dict["PA"], 
        dict => int.Parse(dict["TO"]), 
        (key, items) => new
        {
            PA = key,
            Sum = items.Sum(f => f)
        });

where the parameters are respectively:

.GroupBy(keySelector, elementSelector, resultSelector);

I used int.Parse for testing - it's not a part of my review ;-)

Answer 2

If you're using SharePoint 2013 or higher just use CSOM and the CamlQuery interface. You should be able to trim down your request to just the field you care about or at the very least order it in a way that makes it easier for you to do your calculations.

Answer 3

You want .ToLookup(), which will offload the work of grouping and comparing keys to the framework. Then you can use ApplyResultSelector to project to a key/sum pair (anonymous object, tuple, custom object, whatever).

I'd also take it a step farther, though.

I would entirely eliminate downstream usages of Dictionary<string, string> and map your list of dictionaries to either an ILookup or a Dictionary<string, IEnumerable<string>> using the appropriate ToLookup or ToDictionary method. Do that as early as possible when you get the data and it will save you a lot of headaches.