Query DataTable using LINQ in C#

This tutorial will show you how to Query DataTable using LINQ in C#. LINQ is an acronym for Language Integrated Query, which is descriptive for its use and what it does. The Language Integrated part means that LINQ is part of programming language syntax.

LINQ can be very useful for querying for different type of data sources. One of the advantage of using LINQ in C# is that C# compiler provide IntelliSense. This means we can easily locate all the method available for LinQ. To understand it more I have prepare example below.

For more tutorial visit this link Blog Page.

How to Query DataTable using LINQ in C#

Employee Table

Name	Last Name
John	Dela Cruz
Johny	Dela Ruz
Jan	Dela Cruz

1. Most of LinQ query result is in an array or list data, but using this method we can easily convert the result to a Datatable. In this example we have an Employee table where we want to get a specific record.

CopyToDataTable();

Below snippet will select row from the Employee Table above and place the result under a new table.

DataTable NewTable = (from emp in Employee.AsEnumerable()
                                     where emp.Field<string>("Name") == "John"
                                     select emp).CopyToDataTable();

2. The snippet below will select all the data from the Employee table. If you are familiar with SQL query this is so much alike. You can add a where clause from this method. Depends on what data you wanted to get.

Select

 var emp_selectAll = from emp in Employee.AsEnumerable()
                           select emp;
      

3. This time we will try to select a custom columns from Employee table. We are going to use a where clause and select the columns we want to get. In the snippet below we will only get the column “Name”.

Select, Where

   var emp_Select_column = from emp in Employee.AsEnumerable()
                               where emp.Field<string>("Name").Trim() == "name"
                               select new
                               {
                                   Name = emp.Field<string>("Name"), //variable
                               };    

4. LinQ can also count the record from a data sources. To do so, you may copy the snippet below to test it yourself.

Count()

 var emp_Select_Count = (from emp in Employee.AsEnumerable()
                               where emp.Field<string>("Name").Trim() == "name"
                               select new
                               {
                                   Name = emp.Field<string>("Name"), //variable
                                   LastName = emp.Field<string>("LastName") //variable
                               }).Count();      

5. You can also count number of data that contains a field name of your choice. Like tha snippets below.

Count(); Field<string>(“column”)

var Count = Employee.AsEnumerable().Count(data => data.Field<string>("Name") == "John");       

6. This method will convert a result to a List Type.

ToList()

 var ToList = (from emp in Employee.AsEnumerable()
                               where emp.Field<string>("Name").Trim() == "name"
                               select new
                               {
                                   Name = emp.Field<string>("Name"),  //variable
                                   LastName = emp.Field<string>("LastName") //variable

                               }).ToList();    

7. Sum method is also available in linQ. To use it you can follow the snippet below. Just make sure to sum an integer type columns.

SUM()

  var Sum = Employee.AsEnumerable().Sum(data => data.Field<int>("NUmberOfEmployee"));    

8. Like an SQL queries you can sort or group your queries using a Group BY and an Order By.

Group BY & order by

var data = dt.AsEnumerable() 
                                   .Where(a => DateTime.Parse(a.Field<string>("date")).Month == DateTime.Parse("01/01/2018").Month) 
                                   .GroupBy(x => new { dcount = x.Field<string>("ID") }) 
                                   .Select(group => new 
                                   { 
                                       x = group.Key.dcount, 
                                       Count = group.Count() 
                                   }) 
                                   .OrderByDescending(o => o.x);

Summary

We have cover most of the basic part of a LINQ command. We also provide a sample table data were we use to implement query command that illustrate how LINQ works and how helpful it would be when used correctly. LINQ can also be problem specially when debugging since you can’t use line by line debugging. But overall LINQ is very handy especially if you want your code to be short and fast. Hopes this can guide you and help you get the query you wanted for your project. Happy coding!!