Post
Ask Question

How To Use Data Sorting Operations With Database Using LINQ

Introduction

In this article, I will demonstrate how we can use OrderBy, OrderByDescending, ThenBy, ThenByDescending and Reverse operator of Language-Integrated Query (LINQ). OrderBy, OrderByDescending, ThenBy, ThenByDescending and Reverse operators are part of sorting data in a Language-Integrated Query (LINQ). A sorting operation orders the elements of a sequence based on one or more attributes. The first sort criterion performs a primary sort on the elements. By specifying a second sort criterion, you can sort the elements within each primary sort group.

  • OrderBy - Sorts values in ascending order.
  • OrderByDescending - Sorts values in descending order.
  • ThenBy - Performs a secondary sort in ascending order.
  • ThenByDescending - Performs a secondary sort in descending order.
  • Reverse - Reverses the order of the elements in a collection.

Step 1

Open SQL server 2014 and create Employee table to insert some records.

  1. CREATE TABLE [dbo].[Employee](  
  2.     [Employee_Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [First_Name] [nvarchar](50) NULL,  
  4.     [Last_Name] [nvarchar](50) NULL,  
  5.     [Salary] [int] NULL,  
  6.     [Joing_Date] [nvarchar](50) NULL,  
  7.     [Department] [nvarchar](50) NULL,  
  8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [Employee_Id] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  

Step 2

Open Visual Studio 2015 and create a new console application with a meaningful name.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item, then click on it.

Screenshot for adding entity framework 1

LINQ 

After clicking on the new item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory you can give any name) and click on Add.

Screenshot for adding entity framework 2

LINQ

After you click on "Add a window", the wizard will open, choose EF Designer from the database and click Next.

Screenshot for adding entity framework 3

LINQ 

After clicking on Next a window will appear. Choose New Connection. Another window will appear, add your server name if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 4

LINQ 

Connection will be added. If you wish save connect as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding entity framework 5

LINQ 

After clicking on NEXT another window will appear, choose database table name as shown in the below screenshot, then click on Finish.

Screenshot for adding entity framework 6

LINQ 

Entity framework will be added and respective class gets generated under Models folder.

Screenshot for adding entity framework 7

LINQ

Following class will be added

  1. namespace SortingData_Demo  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public partial class Employee  
  7.     {  
  8.         public int Employee_Id { get; set; }  
  9.         public string First_Name { get; set; }  
  10.         public string Last_Name { get; set; }  
  11.         public Nullable<int> Salary { get; set; }  
  12.         public string Joing_Date { get; set; }  
  13.         public string Department { get; set; }  
  14.     }  
  15. }  

Step 4

Write a program to call database class

Example of OrderBy

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace SortingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.           
  10.         static void Main(string[] args)  
  11.         {  
  12.             using (DBModel db = new DBModel())  
  13.             {  
  14.   
  15.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  16.   
  17.                 Console.WriteLine("EMPLOYEE LIST BEFOR SORTING \n");  
  18.   
  19.                 foreach (Employee emp in employeeList)  
  20.                 {  
  21.                     Console.WriteLine(emp.Employee_Id+"\t"+emp.First_Name + "\t" + emp.Last_Name + "\t" +emp.Salary+ "\t" +emp.Joing_Date + "\t" +emp.Department);  
  22.   
  23.                 }  
  24.   
  25.                 Console.WriteLine(" \n");  
  26.                 Console.WriteLine("EMPLOYEE LIST AFTER SORTING \n");  
  27.   
  28.                 var result = employeeList.OrderBy(e => e.First_Name);  
  29.   
  30.                 foreach (var emp in result)  
  31.                 {  
  32.                     Console.WriteLine(emp.Employee_Id + "\t" + emp.First_Name);  
  33.                 }   
  34.             }  
  35.   
  36.         }  
  37.     }  
  38. }  
Output

LINQ 

Example of OrderByDescending
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace SortingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.           
  10.         static void Main(string[] args)  
  11.         {  
  12.             using (DBModel db = new DBModel())  
  13.             {  
  14.   
  15.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  16.   
  17.                 Console.WriteLine("EMPLOYEE LIST BEFOR SORTING \n");  
  18.   
  19.                 foreach (Employee emp in employeeList)  
  20.                 {  
  21.                     Console.WriteLine(emp.Employee_Id+"\t"+emp.First_Name + "\t" + emp.Last_Name + "\t" +emp.Salary+ "\t" +emp.Joing_Date + "\t" +emp.Department);  
  22.   
  23.                 }  
  24.   
  25.                 Console.WriteLine(" \n");  
  26.                 Console.WriteLine("EMPLOYEE LIST AFTER SORTING \n");  
  27.   
  28.                 var result = employeeList.OrderByDescending(e => e.First_Name);  
  29.   
  30.                 foreach (var emp in result)  
  31.                 {  
  32.                     Console.WriteLine(emp.Employee_Id + "\t" + emp.First_Name);  
  33.                 }   
  34.             }  
  35.   
  36.         }  
  37.     }  
  38. }  
Output

LINQ 

Example of ThenBy
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace SortingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.           
  10.         static void Main(string[] args)  
  11.         {  
  12.             using (DBModel db = new DBModel())  
  13.             {  
  14.   
  15.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  16.   
  17.                 Console.WriteLine("EMPLOYEE LIST BEFOR SORTING \n");  
  18.   
  19.                 foreach (Employee emp in employeeList)  
  20.                 {  
  21.                     Console.WriteLine(emp.Employee_Id+"\t"+emp.First_Name + "\t" + emp.Last_Name + "\t" +emp.Salary+ "\t" +emp.Joing_Date + "\t" +emp.Department);  
  22.   
  23.                 }  
  24.   
  25.                 Console.WriteLine(" \n");  
  26.                 Console.WriteLine("EMPLOYEE LIST AFTER SORTING \n");  
  27.   
  28.                 var result = employeeList.OrderBy(e => e.First_Name).ThenBy(e => e.Last_Name);  
  29.   
  30.                 foreach (var emp in result)  
  31.                 {  
  32.                     Console.WriteLine(emp.Employee_Id + "\t" + emp.First_Name + "\t" + emp.Last_Name);  
  33.                 }   
  34.             }  
  35.   
  36.         }  
  37.     }  
  38. }  
Output

LINQ 

Example of ThenByDescending
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace SortingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.           
  10.         static void Main(string[] args)  
  11.         {  
  12.             using (DBModel db = new DBModel())  
  13.             {  
  14.   
  15.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  16.   
  17.                 Console.WriteLine("EMPLOYEE LIST BEFOR SORTING \n");  
  18.   
  19.                 foreach (Employee emp in employeeList)  
  20.                 {  
  21.                     Console.WriteLine(emp.Employee_Id+"\t"+emp.First_Name + "\t" + emp.Last_Name + "\t" +emp.Salary+ "\t" +emp.Joing_Date + "\t" +emp.Department);  
  22.   
  23.                 }  
  24.   
  25.                 Console.WriteLine(" \n");  
  26.                 Console.WriteLine("EMPLOYEE LIST AFTER SORTING \n");  
  27.   
  28.                 var result = employeeList.OrderBy(e => e.First_Name).ThenBy(e => e.Last_Name).ThenByDescending(e=>e.Salary);  
  29.   
  30.                 foreach (var emp in result)  
  31.                 {  
  32.                     Console.WriteLine(emp.Employee_Id + "\t" + emp.First_Name + "\t" + emp.Last_Name + "\t" + emp.Salary);  
  33.                 }   
  34.             }  
  35.   
  36.         }  
  37.     }  
  38. }  
Output

LINQ 

Example of Reverse
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace SortingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.           
  10.         static void Main(string[] args)  
  11.         {  
  12.             using (DBModel db = new DBModel())  
  13.             {  
  14.   
  15.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  16.   
  17.                 Console.WriteLine("EMPLOYEE LIST BEFOR SORTING \n");  
  18.   
  19.                 foreach (Employee emp in employeeList)  
  20.                 {  
  21.                     Console.WriteLine(emp.Employee_Id+"\t"+emp.First_Name + "\t" + emp.Last_Name + "\t" +emp.Salary+ "\t" +emp.Joing_Date + "\t" +emp.Department);  
  22.   
  23.                 }  
  24.   
  25.                 Console.WriteLine(" \n");  
  26.                 Console.WriteLine("EMPLOYEE LIST AFTER SORTING \n");  
  27.   
  28.                 var result = employeeList.Reverse();  
  29.   
  30.                 foreach (var emp in result)  
  31.                 {  
  32.                     Console.WriteLine(emp.Employee_Id + "\t" + emp.First_Name);  
  33.                 }   
  34.             }  
  35.   
  36.         }  
  37.     }  
  38. }  
Output

LINQ


Similar Articles