C# - Combining matching results from SQL result set

Question

Let's say I have a table in SQL that looks like this:

   record_id|user_id|Name |Meals
   ---------|-------|-----|-----
        1   |   1   |  Bob|1
        2   |   2   |Sally|2
        3   |   3   |Roger|2
        4   |   1   |  Bob|3
        5   |   4   | Joan|1
        6   |   2   |Sally|1

I want to create an object in C# that sums the meal count for any name that appears more than once:

   user_id| Name|Meals
   -------|-----|-----
      1   |  Bob|4
      2   |Sally|3
      3   |Roger|2
      4   | Joan|1

Complicating matters is that due to the nature of the program, the whole table has to be retrieved in one go, rather than using SQL syntax that would sum Bob and Sally's meals, and then iterated over in a C# data reader, and finally added to an IEnumerable array. So I need something along the lines of this:

List<MyIEnumerable> MyList = new List<MyIEnumberable>();
while(dr.Read()) {
    if(MyList.Contains(Convert.ToInt32(dr[1]))) {
        // Needs to combine the dr record with the existing record in MyList
    }
    else {
        MyIEnumerable PersonEntry = new MyIEnumerable();
        PersonEntry.user_id = Convert.ToInt32(dr[1]);
        PersonEntry.Name = dr[2].ToString();
        PersonEntry.Meals = Convert.ToInt32(dr[3]);
        MyList.Add(PersonEntry);
    }
}

So you can see the logic I'm trying to get. .Contains doesn't actually work in this scenario, but I need something like it.

Why is it a requirement of your project that you design your program poorly and do work in the application layer that should be done in the db? — Nick Bailey, Mar 30 '16 at 21:26
Because I'm not actually in charge of the data set I'm working with. I have a query that I need to be able to run, and it returns a data set like my example. I'm well aware that it's a dreadful PITA, but I have to work with what I've got. — Damon Kaswell, Mar 30 '16 at 21:29
That's rough. Well, tell your DBA and or Data Admin that they are bad at their jobs for me — Nick Bailey, Mar 30 '16 at 21:30

Olivier De Meulder · Accepted Answer · 2016-03-30T21:32:55.293

You could use a Dictionary to achieve this.

Dictionary<int, MyIEnumerable> MyDictionary = new Dictionary<int, MyIEnumerable>();

while (dr.Read()) {
    int myKey = Convert.ToInt32(dr[1]));
    if (MyDictionary.ContainsKey(myKey)) 
    {
        MyDictionary[myKey].Meals += Convert.ToInt32(dr[3]);
    }
    else 
    {
        MyIEnumerable PersonEntry = new MyIEnumerable();
        PersonEntry.user_id = Convert.ToInt32(dr[1]);
        PersonEntry.Name = dr[2].ToString();
        PersonEntry.Meals = Convert.ToInt32(dr[3]);
        MyDictionary.Add(myKey), PersonEntry);
    }
}

by doing MyDictionary[id] = object it will update the entry in the dictionary if it already exists or add a new entry.

And if you really want to return a list, then you can finish with:

List<MyIEnumerable> MyList = MyDictionary.Select(kvp => kvp.Value).ToList();

...and it works! This is a lovely and elegant solution to an annoying problem. Thank you very much! — Damon Kaswell, Mar 30 '16 at 21:36

score 0 · Answer 2 · edited May 23 '17 at 11:45

You can't sum the meals 'cause you will get a single number, but you can group by name column and get a table where meals are summarized:

select user_id, name, sum(meals) 
from datatable 
group by user_id, name, meals 
order by user_id

But if it doesn't work for you specific problem you should explain more about it.

If you really want to use .Contain() in this context then you just have to implement IEquatable<Part> in your MyEnumerable class as said here, also you can see here.

C# - Combining matching results from SQL result set

2 Answers2