Pivoting an IEnumerable List

Question

I am trying to apply a bit of groupby/crosstabbing logic to an IEnumerable list of a user defined object and was wondering if anyone could help me out. I'm stuck with an existing (rather annoying) object model to work with but anyway here goes...

consider the following class which I will condense to only relevant properties so you get the jist...

public class Holding
{
   private void Institution;
   private string Designation;
   private Owner Owner;
   private Event Event;
   private Shares Shares;
}

I want to convert this into a list that satifys the following...

• The object is grouped by Institution.
• This parent list of institutions contains a list of a new object with a unique combination of Designation and Owner.
• Now for each of this combination of Designation and Owner we get another child list of unique Events.

So it basically 3 lists deep.

I'm not sure if this is possible with an IEnumerable List or not, I have toyed around quite a bit with the GroupBy extension method to no avail thus far. I'd like most to do it this way, but I'm using linq-to-sql to get the initial list of holdings which is as follows and might be the better place to do the business...

public static List<Holding> GetHoldingsByEvents(
    int eventId1,
    int eventId2)
{
    DataClassesDataContext db = new DataClassesDataContext();

    var q = from h in db.Holdings
             where
               h.EventId == eventId1 ||
               h.EventId == eventId2
             select h;

    return q.Distinct().ToList();
}

Any help/guidance would be much appreciated...

Thanks in advance.

Answer 1

I'm using ToLookup method, which is kind of a grouping, it takes two parameters, first one a function used for defining the group keys and the next one is a function used as a selector (what to take from the match).

items.ToLookup(c=>c.Institution.InstitutionId, c => new {c.Designation, c.Owner, c.Event})
    .Select(c => new {
        // find the institution using the original Holding list
        Institution = items.First(i=>i.Institution.InstitutionId == c.Key).Institution,
        // create a new property which will hold the groupings by Designation and Onwner
        DesignationOwner = 
                // group (Designation, Owner, Event) of each Institution by Designation and Owner; Select Event as the grouping result
                c.ToLookup(_do => new {_do.Designation, _do.Owner.OwnerId}, _do => _do.Event)
                            .Select(e => new {
                                // create a new Property Designation, from e.Key
                                Designation = e.Key.Designation,
                                // find the Owner from the upper group ( you can use items as well, just be carreful this is about object and will get the first match in list)
                                Owner = c.First(o => o.Owner.OwnerId == e.Key.OwnerId).Owner,
                                // select the grouped events // want Distinct? call Distinct
                                Events = e.Select(ev=>ev)//.Distinct()
                            })
    })

I assumed your classes look like these

public class Holding
{
   public Institution Institution {get; set;}
   public string Designation {get; set;}
   public Owner Owner {get; set;}
   public Event Event {get; set;}   
}
public class Owner 
{
  public int OwnerId {get; set;}
}

public class Event 
{
  public int EventId {get; set;}
}

public class Institution 
{
  public int InstitutionId {get; set;}
}