Usage of C# (C Sharp) Query Keywords – group, by, into

When you are framing queries using LINQ Query Expression there may be situations when you want to group records based on a specific criteria or a specific field. This is much alike GROUP BY clause of SQL.

You can perform grouping in C# query expressions using the keywords group and by. You will understand more about these two keywords in this article. In addition, you will know how to store the result of grouping in a temporary variable to process it further in rest of the query. This is achieved using into keyword.

Assume that you have a class to record employee details including his supervisor id. What if you want to list the employees working with each supervisor? You can easily do it using group and by keywords as shown in the example below:

class sampleClass {
public class employee {
public int empId { get; set; }
public string empName { get; set; }
public int supervisorId {get; set; }
}

public static List<employee> accessEmployeeRecords() {
List< employee> emp = new List<employee> {
new employee {empId=123, empName="Alphonse", supervisorId = 110},
new employee {empId=121, empName="Michael", supervisorId = 101},
new employee {empId=122, empName="Rose", supervisorId = 110},
new employee {empId=124, empName="Mary", supervisorId = 110},
};
return emp;
}
static void Main(string[] args) {
List<employee> emp = accessEmployeeRecords();
var groupQuery =from empRec in emp
group empRec by empRec.supervisorId;
foreach (supervisor sup in groupQuery) {
Console.WriteLine("Supervisor with ID " + sup.Key.ToString() + " supervises " +
sup.Count().ToString() + " employee(s) whose details are as below:");
foreach (var emp in supervisor) {
Console.WriteLine(" Employee ID: " + emp.empId.ToString() + " Name: " +
emp.empName);
}
}
}
}

Output of this code will be:

Supervisor with ID 110 supervises 3 employee(s) whose details are as below:
Employee ID: 123 Name: Alphonse
Employee ID: 122 Name: Rose
Employee ID: 124 Name: Mary
Supervisor with ID 101 supervises 1 employee(s) whose details are as below:
Employee ID: 121 Name: Michael

In this example you have displayed the count pertaining to number of employees working with each supervisor and you have also provided details about each employee working with each supervisor. If you look at the query, you might be surprised to see that there is no select clause in the query. Then how are the details fetched and displayed? The result is fetched after the group keyword and before by keyword. After by keyword you specify the criteria or the field based on which grouping has to happen.

In the query highlighted above, you have specified empRec after group keyword which means that you are returning the whole employee record as the result. After by keyword you have specified empRec.supervisorId which means that you are grouping the records based on supervisorId field of each employee instance.

Hence the key for grouping is the supervisorId and for each supervisor you will have one or more employee records associated. This information is retrieved in the foreach iteration statement. Since for every supervisor the corresponding employee records might be more than one, inside foreach statement iterating each supervisor you have another foreach statement iterating employee records for that supervisor.

You have returned the entire employee record in this query. What if you want to display only the empId field of each employee and empName field is not required? In that case you can rephrase the query as:

var groupQuery =from empRec in emp
group empRec.empId by empRec.supervisorId;

As already discussed, the examples you have seen so far doesn’t have a select clause because the group clause in itself fetches the result. Hence select clause is not required. However if you want to perform further processing based on the result fetched from the group clause, then you have to use select clause. For example if you want to sort the results of earlier query based on supervisorId, then your query will be:

var groupQuery =from empRec in emp
group empRec by empRec.supervisorId into grpResult
orderby grpResult.Key
select grpResult;

To sort the result, you need to store the grouping result into a temporary variable. That is done using the query keyword into. Using this keyword you have stored the result of grouping inside a variable called grpResult. You use this variable for further processing. To sort the result based on supervisorId you have used orderby clause and you have fetched the supervisorId using grpResult.Key where Key refers to the key field based on which grouping is done.

Sorted records are then fetched using the select clause. Here again, instead of fetching entire result of grouping (grpResult) in select clause you can fetch specific information alone. For example if you want to retrieve only the supervisorId and number of employees working with the supervisor and you don’t require details of the employees working with each supervisor, then you can reframe the query as:

var groupQuery =from empRec in emp
group empRec by empRec.supervisorId into grpResult
orderby grpResult.Key
select new (grpResult.Key, grpResult.Count() ) ;

Ensure the following guidelines in your query:

• No other clauses other than select clause and group clause can be at the end of your query i.e. the last clause in your query

• If select clause and group clause is available in your query then either of them can be in the last position of your query.

• Your query should have at least one among select clause or group clause. If both these clauses are not present in your query then it will lead to compilation error. The example given below will result in compilation error:
var query =from empRec in emp
where empRec.empId > 123;

So far you have grouped the records based on a single field. You can group records using more than one field as well. In such case, those fields are together termed as composite keys. Assume that the class employee includes one more field called supervisorName. You can group the records using both supervisorId as well as supervisorName. For that, rephrase the query as shown below:

var groupQuery =from empRec in emp
group empRec by new (empRec.supervisorId, empRec.supervisorName);

In all the examples discussed above, after by keyword you have specified a field. You can also specify a condition based on which grouping has to happen. Here is an example:
var groupQuery =from empRec in emp
group empRec by empRec.empId > 122;

Here the grouping condition is empRec.empId > 122 which means that the output will contain two groups, one group containing employees with empId less than or equal to 122 and the other group containing employees with empId greater than 122.

|How to Define Custom Error Pages for Error Handling In ASP.Net Application| Role of Web gardens and web farms in ASP.NET | Understanding Purpose and Usage of "event" Class Member Modifier in C# (C Sharp) | Understanding the Structure and Content of Web.Config File | Usage of C# (C Sharp) Query Keywords – group, by, into | Usage of C# (C Sharp) Query Keywords – join, on, equals, let |Usage of C# (C Sharp) Query Keywords – orderby, ascending, descending | Usage of C# (C Sharp) Query Keywords – select, from, where, in | Usage of Lambda Operator (=>) in C# (C Sharp) |Using C# (C Sharp) as a tool for object oriented programming | Using Dictionaries in .Net – An Overview | Using WCF for providing web service |