Group By

Results can be grouped by using the GroupBy method while composing a query. dbExpression supports grouping by any number of expression elements.

An example that selects the count of records grouped by last name:

IEnumerable<dynamic> counts = db.SelectMany(
        dbo.Person.LastName,
        db.fx.Count(dbo.Person.LastName).As("LastNameCount")
    )
    .From(dbo.Person)
    .GroupBy(dbo.Person.LastName)
    .Execute();

An example that selects the count of records grouped by first name and last name:

IEnumerable<dynamic> persons = db.SelectMany(
        dbo.Person.FirstName,
        dbo.Person.LastName,
        db.fx.Count(dbo.PersonAddress.Id).As("Count")
    )
    .From(dbo.Person)
    .InnerJoin(dbo.PersonAddress).On(dbo.Person.Id == dbo.PersonAddress.PersonId)
    .GroupBy(
        dbo.Person.FirstName,
        dbo.Person.LastName
    ).OrderBy(
        dbo.Person.LastName,
        dbo.Person.FirstName.Desc()
    )
    .Execute();

Group by is typically used with aggregation functions, but also works as a means to select distinct items.

//select unique last names ordered ascending
IEnumerable<string> uniqueLastNames = db.SelectMany(dbo.Person.LastName)
    .From(dbo.Person)
    .GroupBy(dbo.Person.LastName)
    .OrderBy(dbo.Person.LastName.Asc())
    .Execute();