How To Use Data Partitioning Operations With Database Using LINQ

Introduction

In this article, I will demonstrate how we can use skip, SkipWhile, take, TakeWhile operator of Language-Integrated Query (LINQ). Skip, SkipWhile, Take and TakeWhile operators are part of partitioning data in Language-Integrated Query (LINQ).Partitioning in LINQ refers to the operation of dividing an input sequence into two sections, without rearranging the elements, and then returning one of the sections.

• Skip operator skips a specified number of elements in a collection and then returns the remaining elements. The number of items to skip is specified using the count parameter this method expects.
  
  
• SkipWhile operator skips elements in a collection as long as the given condition specified by the predicate is true, and then returns the remaining elements.
  
  
• Take operator retunes a specified number of elements from the start of the collection. The number of items to return is specified using the count parameter this expects.
  
  
• TakeWhile operator returns elements from a collection as long as the given condition specified by the predication is true.

Step 1: Open SQL server 2014 and create table Student and insert some records

CREATE TABLE [dbo].[Student](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Student_Name] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [Course] [nvarchar](50) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

 

After clicking on 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

 

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

Screenshot for adding entity framework 3

 

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

 

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

 

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

 

Screenshot for adding entity framework 7

 

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

Following class will be added

namespace Partitioning_Data
{
    using System;
    using System.Collections.Generic;

    public partial class Student
    {
        public int ID { get; set; }
        public string Student_Name { get; set; }
        public Nullable<int> Age { get; set; }
        public string Course { get; set; }
    }
}

Step 4: Write a program to call database class

Example of Skip Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace Partitioning_Data
{
    class Skip
    {
        static void Main()
        {
            using (DBModel db = new DBModel())
            {
                IEnumerable<Student> listStudent = db.Students.ToList();

                Console.WriteLine("LIST OF STUDENT\n");

                foreach (var student in listStudent)
                {
                    Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);
                }

                Console.WriteLine();
                Console.WriteLine("AFTER SKIP OPERATION (Skip first 2 records)\n");

                var result = listStudent.Skip(2);

                foreach (var stud in result)
                {
                    Console.WriteLine("ID:" + "\t" + stud.ID + "\t" + "Student Name:" + stud.Student_Name + "\t" + "Age:" + stud.Age + "\t" + "Course:" + stud.Course);
                }
            }
        }
    }
}

output

 

Example of SkipWhile Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace Partitioning_Data
{
    class SkipWhile
    {
        static void Main()
        {
            using (DBModel db = new DBModel())
            {
                IEnumerable<Student> listStudent = db.Students.ToList();

                Console.WriteLine("LIST OF STUDENT\n");

                foreach (var student in listStudent)
                {
                    Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);
                }

                Console.WriteLine();
                Console.WriteLine("AFTER SKIP WHILE OPERATION (Display records until string length become 2 records)n");

                IEnumerable<Student>result = listStudent.SkipWhile(s => s.Course.Length>2);

                foreach (var stud in result)
                {
                    Console.WriteLine("ID:" + "\t" + stud.ID + "\t" + "Student Name:" + stud.Student_Name + "\t" + "Age:" + stud.Age + "\t" + "Course:" + stud.Course);
                }
            }
        }
    }
}

Output

 

Example of Take Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace Partitioning_Data
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DBModel db = new DBModel())
            {
                IEnumerable<Student> listStudent = db.Students.ToList();

                Console.WriteLine("LIST OF STUDENT\n");

                foreach (var student in listStudent)
                {
                    Console.WriteLine("ID:"+ "\t"+student.ID+"\t"+"Student Name:"+student.Student_Name+ "\t"+"Age:" +student.Age+"\t"+"Course:"+student.Course);
                }

                Console.WriteLine();
                Console.WriteLine("AFTER TAKE OPERATION\n");

                var result=listStudent.Take(3);

                foreach (var stud in result)
                {
                    Console.WriteLine("ID:" + "\t"+stud.ID+ "\t" + "Student Name:"+stud.Student_Name+"\t" + "Age:"+stud.Age+ "\t" + "Course:"+stud.Course);
                }
            }

        }
    }
}

Output

 

Example of TakeWhile Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace Partitioning_Data
{
    class TakeWhile
    {
        static void Main()
        {
            using (DBModel db = new DBModel())
            {
                IEnumerable<Student> listStudent = db.Students.ToList();

                Console.WriteLine("LIST OF STUDENT\n");

                foreach (var student in listStudent)
                {
                    Console.WriteLine("ID:" + "\t" + student.ID + "\t" + "Student Name:" + student.Student_Name + "\t" + "Age:" + student.Age + "\t" + "Course:" + student.Course);
                }

                Console.WriteLine();
                Console.WriteLine("AFTER TAKE WHILE OPERATION (Display records until it reaches age 20)\n");

                var result = listStudent.TakeWhile(s => s.Age>20);

                foreach (var stud in result)
                {
                    Console.WriteLine("ID:" + "\t" + stud.ID + "\t" + "Student Name:" + stud.Student_Name + "\t" + "Age:" + stud.Age + "\t" + "Course:" + stud.Course);
                }
            }
        }
    }
}

Output