Proper way to Categorize by a Property

Question

I have made a C# LINQ query that categorizes (or groups) by a property and I am almost sure there is a better way. My project is full of these queries so I am really interested on how to achieve this the proper way.

This is how my query look like:

var g = _repository.GetEmployees();
var result = 
    g.GroupBy(x => x.City, (key, group) => group.First())
    .Select(x => new { 
        city = x.City,
        employees = g
            .Where(y=>y.EmployeeID == x.EmployeeID)
            .Select(y=> new {
                fullname = y.FirstName + " " + y.LastName,
                title = y.Title
            })
            .OrderBy(y=>y.fullname)
    })
    .OrderBy(x => x.city);

Sample of JSON output:

[
    {
        "city":"Barcelona",
        "employees":[
            {
                "fullname":"Foo Bar",
                "title":"Help Desk Technician"
            },
            {
                "fullname":"Lorem Ipsum",
                "title":"Information Technology Director"
            }
        ]
    },
    {
        "city":"London",
        "employees":[
            {
                "fullname":"Le Query",
                "title":"Information Technology Manager"
            },
            {
                "fullname":"Please Help",
                "title":"Management Information Systems Director"
            }
        ]
    }
]

The result is fine. What is the best way to achieve it?

Answer 1

It sounds like you just want:

var result = g.GroupBy(x => x.City, (key, group) => new {
                           city = key,
                           employees = group.Select(emp => new {
                               fullname = emp.FirstName + " " + emp.LastName,
                               title = emp.Title
                           })
                       });

In other words, you're just providing a projection for each group, which is "an anonymous type with the city, and all the employees for that city".