groupby clause

group by statement defined as

groupby-clause ::= group selExpr by keyExpr

That is powerful tool for grouping by single or compund property(s), also function can be applied to key property. In simplest case group statement uses itemName (from itemName) as selExp.

MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var results = from e in emp.DeptEmps
                group e by e.Department.DeptName;

foreach (var a in results)
{
    Console.WriteLine(a.Key.ToString());
    foreach (var b in a.Take(10) )
        Console.WriteLine("\t"+b.Employee.LastName);
}

Listing 10.1

If you misstype variable name var results = from e in emp.DeptEmps group i by e.Department.DeptName; appears strange error message: Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer<>' because it is not a delegate type. Check carefully selExp. It's possible here to construct new anonymous type as selExp almost with the same result set.

MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var results = from e in emp.DeptEmps
                group new { e.Department.DeptName, e.Employee.LastName } by e.Department.DeptName;

foreach (var a in results)
{
    Console.WriteLine(a.Key.ToString());
    foreach (var b in a.Take(10) )
        Console.WriteLine("\t"+b.LastName);
}

Listing 10.2

The output from both listings will be the same let's find in next example all full namesake in employee set, it should much first name and last name. We should use compound key and extended group by into.

         MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
         var emp = new EmployeeDataContext(conn);

         var results = from e in emp.Employees
                       group e by new { e.LastName, e.FirstName } into g
                       where g.Count()>1
                       select g.Key;

         foreach (var a in results)
             Console.WriteLine(a);
     }

Listing 10.3