LINQ to pull back object graph

Question

Is it possible to group by more than one group in LINQ?

For instance, I have this query (obviously incomplete)

from lxr in LOCATION_XREFs
    join l in LOCATIONs on lxr.LOCATION_SKEY equals l.LOCATION_SKEY
    join c in COMPANies on l.COMPANY_SKEY equals c.COMPANY_SKEY
    join prlx in PeopleRoleLocationXrefs on lxr.LOCATION_XREF_SKEY equals prlx.LOCATION_XREF_SKEY
    join p in PEOPLEs on prlx.PEOPLE_SKEY equals p.PEOPLE_SKEY
    join pr in PeopleRoles on prlx.PeopleRoleKey equals pr.PeopleRoleKey

... and I'd like to get back a bunch of companies that have a bunch of locations that, in turn, have a bunch of people.

Answer 1

If you're looking to group by multiple parameters, that is possible:

from lxr in LOCATION_XREFs
    join l in LOCATIONs on lxr.LOCATION_SKEY equals l.LOCATION_SKEY
    join c in COMPANies on l.COMPANY_SKEY equals c.COMPANY_SKEY
    join prlx in PeopleRoleLocationXrefs on lxr.LOCATION_XREF_SKEY equals prlx.LOCATION_XREF_SKEY
    join p in PEOPLEs on prlx.PEOPLE_SKEY equals p.PEOPLE_SKEY
    join pr in PeopleRoles on prlx.PeopleRoleKey equals pr.PeopleRoleKey
group c by new { c.COMPANY_SKEY, l.LOCATION_SKEY} into myGroup
select new
{
    myGroup.Key.COMPANY_SKEY,
    myGroup.Key.LOCATION_SKEY,
    myGroup.Count()
}

Answer 2

I ultimately used the following code to achieve what I wanted. Interesting note: notice that I wanted the Roles flattened out and concatenated with commas, so I did this using a wrapper accessor that uses the Aggregate operator. Apparently the Aggregate operator is not suuported within a LINQ query, as you get "the query operator "Aggregate" is not supported" otherwise I would have done it right there inline like this Roles = new List<string>(g.Select(u => u.pr.RoleName)).Aggregate((a, b) => a + ", " + b)

public class DAOPerson
{
    public string Name { get; set; }
    public int PeopleSkey { get; set; }
    public List<string> RolesCollection { get; set; }
    public string Roles
    {
        get { return RolesCollection.Aggregate((a, b) => a + ", " + b); }
    }
}

IQueryable<DAOLocation> gridData;
gridData = (from lxr in db.LOCATION_XREFs
            join l in db.LOCATIONs on lxr.LOCATION_SKEY equals l.LOCATION_SKEY
            join c in db.COMPANies on l.COMPANY_SKEY equals c.COMPANY_SKEY
            where lxr.DEPARTMENT_NUMBER == Department.BINDING_AUTHORITY_KEY
                  && lxr.BRANCH_NUMBER == Branch.ATLANTAKEY
            orderby c.NAME, l.INTERNAL_NAME ascending
            select new DAOLocation
                       {
                           CompanyName = c.NAME,
                           CompanySkey = c.COMPANY_SKEY,
                           LocationName = l.INTERNAL_NAME,
                           LocationSkey = l.LOCATION_SKEY,
                           Persons = (from prlx in db.PeopleRoleLocationXrefs
                                      join lxr2 in db.LOCATION_XREFs on prlx.LOCATION_XREF_SKEY equals lxr.LOCATION_XREF_SKEY
                                      join p in db.PEOPLEs on prlx.PEOPLE_SKEY equals p.PEOPLE_SKEY
                                      join pr in db.PeopleRoles on prlx.PeopleRoleKey equals pr.PeopleRoleKey
                                      where lxr2.LOCATION_SKEY == l.LOCATION_SKEY
                                      group new { p, pr } by p.PEOPLE_SKEY into g
                                      select new DAOPerson
                                                 {
                                                     Name = g.First().p.LAST_NAME,
                                                     PeopleSkey = g.First().p.PEOPLE_SKEY,
                                                     RolesCollection = new List<string>(g.Select(u => u.pr.RoleName))
                                                 }).ToList()
                       });