Linq-To-Objects group by

Question

Hey,

I'm building a software for timereporting

I have a Dictionary<string, Dictionary<string, double>>. The key in the main dictionary is a users name and their value is a dictionary of .

I have a function GetDepartment(string UserName) which returns a string with the users department.

What I want is to crate a new dictionary, of the same type, that has the department as the main key and in the subdictionary a where hours is the total for that department.

I have been trying to do this with linq but did not succeed. Would be very glad for some help here!

EDIT: This code does exactly what I want. But I want it in LINQ

        Dictionary<string, Dictionary<string, double>> temphours = new Dictionary<string, Dictionary<string, double>>(); ;
        foreach (var user in hours)
        {
            string department = GetDepartment(user.Key);
            if (!temphours.ContainsKey(department))
            {
                temphours.Add(department, new Dictionary<string, double>());
            }
            foreach (var customerReport in user.Value)
            {
                if (!temphours[department].ContainsKey(customerReport.Key))
                {
                    temphours[department].Add(customerReport.Key, 0);
                }
                temphours[department][customerReport.Key] += customerReport.Value;
            }
        }

Answer 1

Why do you want to do this with LINQ? I do not think that it will be any clearer, plus LINQ queries are not as easy to debug.

The following expression will not work in LINQ to Entities, since you cannot call C# function such as GetDepartment there.

Dictionary<string, Dictionary<string, double>> temphours 
    = (from user in hours
       group user by GetDepartment(user.Key) into department
       select new {
          Key = department.Key
          Value = (from userInDepartment in department
                   from report in userInDepartment.Value
                   group report by report.Key into g // To tired to think of a name =)
                   select new {
                       Key = g.Key
                       Value = g.Sum(reportInG => reportInG.Value)
                   }).ToDictonary(ud => ud.Key, ud=> ud.Value);
       }).ToDictonary(u => u.Key, u=> u.Value);

I am not sure that this is bug free. At least it should give you an idea on how to do this.

Answer 2

Here's my take on it.

Dictionary<string, Dictionary<string, double>> temphours =
(
  from user in hours
  let department = GetDepartment(user.Key)
  from customerReport in user.Value
  group customerReport by department
)
.ToDictionary(
  g => g.Key,
  g => g.GroupBy(rep => rep.Key).ToDictionary
  (
    g2 => g2.Key,
    g2 => g2.Sum(rep => rep.Value)
  )
);

That's about a direct as I can make it. If you want a little more descriptiveness, then this might do it for you:

Dictionary<string, Dictionary<string, double>> temphours =
(
  from user in hours
  let department = GetDepartment(user.Key)
  from customerReport in user.Value
  group customerReport by department into reportGroup
  select new
  {
    Department = reportGroup.Key,
    Reports =
    (
       from report in reportGroup
       group report.Value by report.Key
    ).ToDictionary(g => g.Key, g => g.Sum())
  }
)
.ToDictionary{
  x => x.Department,
  x => x.Reports
);