How can I organize this data into the objects I want with LINQ?

Question

I have an the following class I'm trying to create a list of with a bunch of data I have queried:

public class Agency
{
    public string AgencyName { get; set; }
    public int AgencyID { get; set; }
    public IEnumerable<AuditRule> rules { get; set; } 
}

Where "AuditRule" is:

public class AuditRule
{
    public int AuditRuleID { get; set; }
    public string AuditRuleName { get; set }
    public int AvgDaysWorked { get; set; }
}

Right now, after a fairly complicated query I have been able to get my data into an anonymous object that looks like:

{ DateImported, AgencyID, AgencyName, AuditRuleName, AuditRuleID, DaysWorked }

I have thousands of records in that format, and I'm trying to construct a IEnumerable<Agency> which will itself contain a list of rules for each agency.

So, take the following data as an example:

{ AgencyID = 1, AgencyName = "CCR", AuditRuleName = "Call", AuditRuleID = 1, DaysWorked = 3 }
{ AgencyID = 1, AgencyName = "CCR", AuditRuleName = "Call", AuditRuleID = 1, DaysWorked = 5 }
{ AgencyID = 1, AgencyName = "CCR", AuditRuleName = "Time", AuditRuleID = 2, DaysWorked = 2 }
{ AgencyID = 2, AgencyName = "YRX", AuditRuleName = "Call", AuditRuleID = 1, DaysWorked = 3 }
{ AgencyID = 2, AgencyName = "YRX", AuditRuleName = "Acct", AuditRuleID = 3, DaysWorked = 2 }

So, out of this data, I'm trying to construct first a list of agencies (the class at the top), which would obviously be "CCR" and "YRX" for this example. Then, for each agency I want an entry for each of its rules in its IEnumerable<AuditRule> that there is a record for.

So, Agency "CCR" would have have 2 Rule entries, "Call" and "Time". The "Call" AuditRule entry will have an AvgDaysWorked of 4 since we are averaging the entries for "Call" under Agency "CCR" which is 3 days and 5 days worked. The Time AuditRule entry would have an AvgDaysWorked of 2, since there is only one entry.

Can someone with some LINQ ninja skills please help me wrangle this data into a list of Agencies and Rules for each agency (with the avg of days worked for that rule/agency combo)?

It was complicated enough for me to even be able to get together that anonymous object and I'm really struggling writing this query to create this list of Agencies/AuditRules.

Is this a case where I would use SelectMany() or something similar? I'm kind of lost here.

Answer 1

I hope this does the job.

// GetAnonymousCollection() is hard to define... ;)
var anonymous = GetAnonymousCollection();

IEnumerable<Agency> result = anonymous
    .GroupBy(a => a.AgencyID)
    .Select(ag => new Agency()
    {
        AgencyID = ag.Key,
        AgencyName = ag.First().AgencyName,
        Rules = ag
            .GroupBy(agr => agr.AudiRuleID)
            .Select(agrg => new AuditRule()
            {
                AuditRuleID = agrg.Key,
                AuditRuleName = agrg.First().AuditRuleName,
                AvgDaysWorked = (Int32)agrg.Average(agrgr => agrgr.DaysWorked)
            })
      });

By the way, think about using a decimal or float for the average time.

Answer 2

EDIT: I think I personally prefer Daniel's solution in terms of simplicity, although mine is slightly more efficient (as it doesn't need to evaluate the query multiple times through calling First() to get the agency/audit rule name). I'm not entirely sure what to do with my answer here: it shows something different (grouping by a composite key) which is nice, but at the same time it's a bulky and unwieldy bit of code. Then again, it's a complete example, which makes it easier to play with...

Thoughts, anyone? Should I leave this here, or delete it?

---

Okay, I think this does what you want. It's pretty evil though. I don't have time to explain it immediately, but the GroupBy method is the crucial one.

I've formatted it a bit, but it's still not ideal...

using System;
using System.Collections.Generic;
using System.Linq;

public class Agency
{
    public string AgencyName { get; set; }
    public int AgencyID { get; set; }
    public IEnumerable<AuditRule> Rules { get; set; } 

    public override string ToString()
    {
        return string.Format("Agency {0}/{1}:\r\n{2}",
            AgencyID, AgencyName,
            string.Concat(Rules.Select(x => x.ToString() + "\r\n")
                               .ToArray()));
    }
}

public class AuditRule
{
    public int AuditRuleID { get; set; }
    public string AuditRuleName { get; set; }
    public int AvgDaysWorked { get; set; }

    public override string ToString()
    {
        return string.Format("Audit rule {0}/{1}: {2}", AuditRuleID,
                             AuditRuleName, AvgDaysWorked);
    }
}

class Test
{    
    static void Main()
    {
        var previousQuery = new[]
        {
            new { AgencyID = 1, AgencyName = "CCR",
                  AuditRuleName = "Call",  AuditRuleID = 1, DaysWorked = 3 },
            new { AgencyID = 1, AgencyName = "CCR",
                  AuditRuleName = "Call", AuditRuleID = 1, DaysWorked = 5 },
            new { AgencyID = 1, AgencyName = "CCR",
                  AuditRuleName = "Time", AuditRuleID = 2, DaysWorked = 2 },
            new { AgencyID = 2, AgencyName = "YRX",
                  AuditRuleName = "Call", AuditRuleID = 1, DaysWorked = 3 },
            new { AgencyID = 2, AgencyName = "YRX",
                  AuditRuleName = "Acct", AuditRuleID = 3, DaysWorked = 2 },
        };

        var itemsGroupedByAgency = previousQuery.GroupBy
            (item => new { item.AgencyID, item.AgencyName });

        // Want to do the query for each group
        var query = itemsGroupedByAgency.Select
        // Outdented to avoid scrolling
        (group => new Agency 
         { 
             AgencyName = group.Key.AgencyName,
             AgencyID = group.Key.AgencyID,
             Rules = group.GroupBy(item => new { item.AuditRuleName, 
                                                 item.AuditRuleID },
                                                 (key, items) => new AuditRule
                    // Outdented to avoid scrolling :)
                    {
                       AuditRuleID = key.AuditRuleID,
                       AuditRuleName = key.AuditRuleName,
                       AvgDaysWorked = (int) items.Average(x => x.DaysWorked)
                    })
         });

        foreach (var item in query)
        {
            Console.WriteLine(item);
        }
    }
}