How To Use Data Filtering Operations With Database Using LINQ

Introduction

In this article, I will demonstrate the use of Where and OfTypeoperator of Language-Integrated Query (LINQ). Where and OfTypeoperators are part of Data Filtering in Language-Integrated Query (LINQ). Filtering refers to the operation of restricting the result set to contain only those elements that satisfy a specified condition. It is also known as selection. Filtering operators in LINQ filter the sequence (collection) based on some given criteria.

Where - Selects values, depending on their ability to be cast to a specified type.

OfType - Selects values that are based on a predicate function.

Step 1

Open SQL server 2014 and create table Employee and insert some record.

  1. CREATE TABLE [dbo].[Student](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Student_Name] [nvarchar](50) NULL,  
  4.     [Age] [int] NULL,  
  5.     [Course] [nvarchar](50) NULL,  
  6.  CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [ID] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  10. ) ON [PRIMARY]  
  11.   
  12. 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

How To Use Data Filtering Operations With Database Using 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

How To Use Data Filtering Operations With Database Using 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

How To Use Data Filtering Operations With Database Using 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 a dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 4

How To Use Data Filtering Operations With Database Using LINQ 

The connection will be added. If you wish to 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

How To Use Data Filtering Operations With Database Using 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

How To Use Data Filtering Operations With Database Using LINQ 

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

Screenshot for adding entity framework 7

How To Use Data Filtering Operations With Database Using LINQ 

Screenshot for adding entity framework 8

How To Use Data Filtering Operations With Database Using LINQ 

Following class will be added

  1. namespace FilteringData_Demo  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public partial class Student  
  7.     {  
  8.         public int ID { get; set; }  
  9.         public string Student_Name { get; set; }  
  10.         public Nullable<int> Age { get; set; }  
  11.         public string Course { get; set; }  
  12.     }  
  13. }  

Step 4

Write a program to call database class

Example 1: Where Operator

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace FilteringData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db=new DBModel())  
  12.             {  
  13.                 IEnumerable<Student> listStudent = db.Students.ToList();  
  14.   
  15.                 Console.WriteLine("LIST OF STUDENTS\n");  
  16.   
  17.                 foreach (var student in listStudent)  
  18.                 {  
  19.                     Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);  
  20.                 }  
  21.   
  22.                 Console.WriteLine();  
  23.                 Console.WriteLine("After WHERE Cluse Filtered Data\n ");  
  24.   
  25.                 var result = listStudent.Where(s => s.Age > 20 && s.Age < 25);  
  26.   
  27.                 foreach (var stud in result)  
  28.                 {  
  29.                     Console.WriteLine("ID:" + "\t" + stud.ID + "\t" + "Student Name:" + stud.Student_Name + "\t" + "Age:" + stud.Age + "\t" + "Course:" + stud.Course);  
  30.                 }  
  31.             }  
  32.         }  
  33.     }  
  34. }  
Output
 
How To Use Data Filtering Operations With Database Using LINQ

Example 2: Where Operator

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace FilteringData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db=new DBModel())  
  12.             {  
  13.                 IEnumerable<Student> listStudent = db.Students.ToList();  
  14.   
  15.                 Console.WriteLine("LIST OF STUDENTS\n");  
  16.   
  17.                 foreach (var student in listStudent)  
  18.                 {  
  19.                     Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);  
  20.                 }  
  21.   
  22.                 Console.WriteLine();  
  23.                 Console.WriteLine("After WHERE Cluse Filtered Data (Display Students Age Greater than 20 and less 25)\n ");  
  24.   
  25.                 var result = from s in listStudent  
  26.                                       where s.Age > 20 && s.Age < 25  
  27.                                       select s;  
  28.   
  29.                 foreach (var stud in result)  
  30.                 {  
  31.                     Console.WriteLine("ID:" + "\t" + stud.ID + "\t" + "Student Name:" + stud.Student_Name + "\t" + "Age:" + stud.Age + "\t" + "Course:" + stud.Course);  
  32.                 }  
  33.             }  
  34.         }  
  35.     }  
  36. }  
Output
 
How To Use Data Filtering Operations With Database Using LINQ

Example 3: Where Operator

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace FilteringData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db=new DBModel())  
  12.             {  
  13.                 IEnumerable<Student> listStudent = db.Students.ToList();  
  14.   
  15.                 Console.WriteLine("LIST OF STUDENTS\n");  
  16.   
  17.                 foreach (var student in listStudent)  
  18.                 {  
  19.                     Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);  
  20.                 }  
  21.   
  22.                 Console.WriteLine();  
  23.                 Console.WriteLine("After WHERE Cluse Filtered Data (Display Students Age Greater than 20 and less 25)\n ");  
  24.   
  25.                 var result = from c in listStudent  
  26.                              where c.Course == "Bsc"  
  27.                              select c;  
  28.   
  29.                 foreach (var stud in result)  
  30.                 {  
  31.                     Console.WriteLine("ID:" + "\t" + stud.ID + "\t" + "Student Name:" + stud.Student_Name + "\t" + "Age:" + stud.Age + "\t" + "Course:" + stud.Course);  
  32.                 }  
  33.   
  34.                 Console.ReadLine();  
  35.             }  
  36.         }  
  37.     }  
  38. }  
Output
 
How To Use Data Filtering Operations With Database Using LINQ 

Example of OfType Operator

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace FilteringData_Demo  
  6. {  
  7.     class OfType  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db = new DBModel())  
  12.             {  
  13.                 IEnumerable<Student> listStudent = db.Students.ToList();  
  14.   
  15.                 Console.WriteLine("LIST OF STUDENTS\n");  
  16.   
  17.                 foreach (var student in listStudent)  
  18.                 {  
  19.                     Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);  
  20.                 }  
  21.   
  22.                 Console.WriteLine();  
  23.                 var result =listStudent.OfType<Student>().Where(s => s.Student_Name.ToUpper().Contains('P'));  
  24.   
  25.                 Console.WriteLine("\nThe Student Name Contain 'P':\n");  
  26.   
  27.                 foreach (var student in result)  
  28.                 {  
  29.                     Console.WriteLine("Student Name:"+ student.Student_Name);  
  30.                 }  
  31.   
  32.                 Console.ReadLine();  
  33.             }  
  34.         }  
  35.     }  
  36. }  
Output
 
How To Use Data Filtering Operations With Database Using LINQ