Querying a Data Table Using Select Method and Lambda Expressions in C#

In this article, we are explaining how we can perform several queries on a DataTable object using the select method and Lambda expression.

If you have your data in a DataTable object and we want to retrieve specific data from a data table on some certain conditions, so it becomes quite easy to query a data table using its select method and Lamda Expression. A sample code is also attached with this article to explain the concept.

Suppose we have a DataTable object having four fields: SSN, NAME, ADDRESS and AGE. We could create a data table and add columns in the following way:

DataTable dt = new DataTable();  
dt.Columns.Add("SSN", typeof(string));  
dt.Columns.Add("NAME", typeof(string));  
dt.Columns.Add("ADDR", typeof(string));  
dt.Columns.Add("AGE", typeof(int));  
// Showing how to set Primary Key(s) in a Data table (Although it's not compulsory to have one)  
DataColumn[] keys = new DataColumn[1];  
keys[0] = dt.Columns[0];  
dt.PrimaryKey = keys;

Now we store some data in our data table "dt" to show how we can perform several queries on the DataTable object like filtering some data, finding a person's record on certain conditions, sorting the person's records into ascending or descending order etc. These types of operations can easily be performed using a "Lambda Expression" and the select method.

dt.Rows.Add("203456876", "John", "12 Main Street, Newyork, NY", 15);  
dt.Rows.Add("203456877", "SAM", "13 Main Ct, Newyork, NY", 25);  
dt.Rows.Add("203456878", "Elan", "14 Main Street, Newyork, NY", 35);  
dt.Rows.Add("203456879", "Smith", "12 Main Street, Newyork, NY", 45);  
dt.Rows.Add("203456880", "SAM", "345 Main Ave, Dayton, OH", 55);  
dt.Rows.Add("203456881", "Sue", "32 Cranbrook Rd, Newyork, NY", 65);  
dt.Rows.Add("203456882", "Winston", "1208 Alex St, Newyork, NY", 65);  
dt.Rows.Add("203456883", "Mac", "126 Province Ave, Baltimore, NY", 85);  
dt.Rows.Add("203456884", "SAM", "126 Province Ave, Baltimore, NY", 95); 

Now we see how we can perform various queries against our data table on the list using a one-line query using a simple Lambda expression. A DataTable object has a built-in select method that has the following signature:

DataRow[] DataTable.Select(string filterExpression, string sort)  

Where the input parameters are:

• filterExpression: criteria to use to filter the rows.
• sort: string specifying the column and sort direction.

Return Value

An array of DataRow objects matching the filter expression. The following code retrieves all the person's records except persons having the name "SAM". Here the filter expression is "NAME <> 'SAM'" where the "<>" is a NOT operator. See:

Console.WriteLine(" Retrieving all the person except having name 'SAM'\n");  
foreach (DataRow o in dt.Select("NAME <> 'SAM'"))  
{  
    Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}

The following code retrieves the two oldest people older than 60 years. Here the filter expression is "AGE > 60" and then we are selecting two persons from the top.

Console.WriteLine(" Retrieving Top 2 aged persons from the list who are older than 60 years\n");  
foreach (DataRow o in dt.Select("AGE > 60").Take(2))  
{  
    Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}

The following code gets the average of all the person's age. Here we keep the filter criteria empty and then use a Lambda expression in the Average() method:

Console.WriteLine("\n Getting Average of all the person's age...");  
double avgAge = dt.Select("").Average(e => (int)e.ItemArray[3]);  
Console.WriteLine(" The average of all the person's age is: " + avgAge);

The following code checks whether a person having the name "SAM" exists or not. Here we keep the filter criteria empty and then use a Lambda expression in the Any() method:

Console.WriteLine("\n Checking whether a person having name 'SAM' exists or not...");  
if(dt.Select().Any(e => e.ItemArray[1].ToString() == "SAM"))  
{  
    Console.WriteLine("\tYes, A person having name  'SAM' exists in our list");  
}

The following code checks whether any person is a teenager or not. Here the filter expression in the Select method is "AGE >= 13 AND AGE <= 19" and then check for the existence of such a person with Any(). See:

Console.WriteLine("\n Checking whether any person is teen-ager or not...");  
if(dt.Select("AGE >= 13 AND AGE <= 19").Any())  
{  
    Console.WriteLine("\t Yes, we have some teen-agers in the list");  
}

The following code gets a sorted list of persons in descending order by their age. Here we keep the filter criteria empty and the sorting criteria as AGE DESC:

Console.WriteLine("\nSorting data table in Descening order by AGE ");  
foreach (DataRow o in dt.Select("","AGE DESC"))  
{  
    Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}

The following code gets a sorted list of persons in ascending order by their name. Here we keep the filter criteria empty and the sorting criteria is NAME ASC:

Console.WriteLine("\nSorting data table in Aescening order by NAME ");  
foreach (DataRow o in dt.Select("", "NAME ASC"))  
{  
    Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}

Following code gets the name of the most aged person in the list. Here we keep the filter criteria empty and sort the list by age. Then use the Last() method to determine the oldest person.

Console.WriteLine("\n Getting the name of the most aged person in the list ...");  
DataRow mostAgedPerson = dt.Select("", "AGE ASC").Last();  
Console.WriteLine("\t"+mostAgedPerson["SSN"] + "\t" + mostAgedPerson["NAME"] + "\t" + mostAgedPerson["ADDR"]  + "\t" + mostAgedPerson["AGE"]);

The following code gets the sum of everyone's ages. Here we keep the filter criteria empty and then use a Lambda expression in the Sum() method, whereas ItemArray[3] indicates the Age column. See:

Console.WriteLine("\n Getting Sum of all the person's age...");  
int sumOfAges =dt.Select().Sum(e=> (int) e.ItemArray[3]);  
Console.WriteLine("\t The sum of all the persons's age = " + sumOfAges);

The following code skips everyone whose age is less than 60 years. Here we keep the filter criteria empty and then use a Lambda expression in the SkipWhile () method, whereas ItemArray[3] indicates the Age column. See:

Console.WriteLine("\n Skipping every person whose age is less than 60 years...");  
foreach (DataRow o in dt.Select().SkipWhile(e=> (int)e.ItemArray[3] < 60))  
{  
    Console.WriteLine("\t"+o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}

The following code gets everyone until we find a person with a name beginning with any character other than "S". Here we keep the filter criteria empty and then use a Lambda expression in the Where () method, whereas ItemArray[1] indicates the Name column. See:

Console.WriteLine(" Displaying the persons until we find a person with name starts with other than 'S'");  
foreach (DataRow o in dt.Select().Where(e=> e.ItemArray[1].ToString().StartsWith("S")))  
{  
    Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}

The following code checks everyone to determine if they have a SSN. Here we keep the filter criteria empty and then use a Lambda expression in the All () method, whereas ItemArray[0] indicates the SSN column. See:

Console.WriteLine("\n Checking all the persons have SSN or not ...");  
if(dt.Select().All(e => e.ItemArray[0] != null))  
{  
    Console.WriteLine("\t No person is found without SSN");  
}

The following code searches for whoever has the SSN "203456876":

Console.WriteLine("\n Finding the person whose SSN = 203456876 in the list");  
foreach (DataRow o in dt.Select("SSN = '203456876'"))  
{  
    Console.WriteLine("\t" + o["SSN"] + "\t" + o["NAME"] + "\t" + o["ADDR"] + "\t" + o["AGE"]);  
}