LINQ - Language Integrated Query - DotNet and DataBase

Wednesday, 25 July 2018

LINQ - Language Integrated Query




LINQ stands for  Language INtegrated Query. LINQ is a powerful querying language which was integrated with .Netframework with .NET 3.5 version.  LINQ provides a powerful querying feature on different data collections like "Collections, datasets and data tables". We can perform different operations using LINQ like "SELECT, Join, Order by, Group by, Where, Aggregate, Mathematical operations" etc..
After we fetch the raw data from the database, we can close the connection with the database and store the raw data on any collection and manipulate the data using LINQ and use on the required base.
So, LINQ reduces the pressure on the database by reducing communication.

We have different LINQ techniques like "SELECT, Join, Order by, Group by, Where, Aggregate, Mathematical operations" etc..
Let us see how to work with these features.

SELECT: To retrieve the required data from the data collection.

JOIN():  To join two different data collections.

ORDER BY(): To sort the collection either in Ascending or Descending order 

CONTAINS():  To check required data exists in the collection.

WHERE(): To get the required data on conditional base.

AGGREGATE(): To concat values in the collection.

AVERAGE(): To get the average value of a collection.

COUNT(): To get the number of elements in the collection.

MAX(): To get the maximum value in the collection

SUM():  To get the sum of values in the collection

GROUP BY(): To remove the duplicate records from the collection.


Example
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LINQ
{
    class StidentProgress
    {
        public int ID { get; set; }
        public string StudentName { get; set; }
        public double Percent { get; set; }
    }
    class StidentProgressAddinfo
    {

        public List AddSStudents()
        {
            List lstsp = new List();
            StidentProgress sp = new StidentProgress();
            sp.ID = 1;
            sp.StudentName = "Naga";
            sp.Percent = 70;
            lstsp.Add(sp);

            StidentProgress sp1 = new StidentProgress();
            sp1.ID = 2;
            sp1.StudentName = "Mahesh";
            sp1.Percent = 80;
            lstsp.Add(sp1);

            StidentProgress sp2 = new StidentProgress();
            sp2.ID = 3;
            sp2.StudentName = "Suresh";
            sp2.Percent = 60;
            lstsp.Add(sp2);

            StidentProgress sp3 = new StidentProgress();
            sp3.ID = 4;
            sp3.StudentName = "Sujeet";
            sp3.Percent = 75;
            lstsp.Add(sp3);

            StidentProgress sp4 = new StidentProgress();
            sp4.ID = 5;
            sp4.StudentName = "Suresh";
            sp4.Percent = 95;
            lstsp.Add(sp4);

            return lstsp;
        }
    }
    class Studentsinformation
    {
        static void Main()
        {

            StidentProgressAddinfo stuAddinfo = new StidentProgressAddinfo();
            List spInfo = stuAddinfo.AddSStudents();

            Console.WriteLine("General Select command");
            Console.WriteLine("================");
            var stu = from p in spInfo
                      select p;

            foreach (var item in stu)
            {
                Console.WriteLine("ID={0},Name={1},Percent={2}", item.ID, item.StudentName, item.Percent);
            }

            Console.WriteLine("\n");
            Console.WriteLine("Select required columns command");
            Console.WriteLine("================");
            var stu1 = from p in spInfo
                       select (new { Name = p.StudentName });

            foreach (var item in stu1)
            {
                Console.WriteLine("Name={0}", item.Name);
            }

            Console.WriteLine("\n");
            Console.WriteLine("Select columns Orderby Ascending");
            Console.WriteLine("==================================");
            var stu2 = from p in spInfo
                       orderby p.StudentName
                       select p;

            foreach (var item in stu2)
            {
                Console.WriteLine("ID={0},Name={1},Percent={2}", item.ID, item.StudentName, item.Percent);
            }



            Console.WriteLine("\n");
            Console.WriteLine("Select columns Orderby Descending order");
            Console.WriteLine("=========================================");
            var stu3 = from p in spInfo
                       orderby p.StudentName descending
                       select p;

            foreach (var item in stu3)
            {
                Console.WriteLine("ID={0},Name={1},Percent={2}", item.ID, item.StudentName, item.Percent);
            }


            Console.WriteLine("\n");
            Console.WriteLine("Contans command");
            Console.WriteLine("=========================================");
            var stu4 = from p in spInfo
                       where p.StudentName.Contains("Mahesh")
                       select p;

            foreach (var item in stu4)
            {
                Console.WriteLine("ID={0},Name={1},Percent={2}", item.ID, item.StudentName, item.Percent);
            }

            Console.WriteLine("\n");
            Console.WriteLine("Where command");
            Console.WriteLine("=========================================");
            var stu5 = from p in spInfo
                       where p.Percent >= 60 && p.Percent <= 75
                       select p;

            foreach (var item in stu5)
            {
                Console.WriteLine("ID={0},Name={1},Percent={2}", item.ID, item.StudentName, item.Percent);
            }

            Console.WriteLine("\n");
            Console.WriteLine("Aggrigate command");
            Console.WriteLine("=========================================");

            string agg = stu1.Select(query => query.ToString())
                       .Aggregate((a, b) => a + ", " + b);

            Console.WriteLine(agg);

            Console.WriteLine("\n");
            Console.WriteLine("Average command");
            Console.WriteLine("=========================================");

            string avg = spInfo.Average(s => s.Percent).ToString();
            Console.WriteLine(avg);

            Console.WriteLine("\n");
            Console.WriteLine("Count command");
            Console.WriteLine("=========================================");

            int stuCount = spInfo.Count();
            Console.WriteLine(stuCount);

            Console.WriteLine("\n");
            Console.WriteLine("Max command");
            Console.WriteLine("=========================================");

            double StuMaxPercent = spInfo.Max(x => x.Percent);
            Console.WriteLine(StuMaxPercent);

            Console.WriteLine("\n");
            Console.WriteLine("Sum command");
            Console.WriteLine("=========================================");

            double StuSum = spInfo.Sum(x => x.Percent);
            Console.WriteLine(StuSum);

            Console.WriteLine("\n");
            Console.WriteLine("Group by command");
            Console.WriteLine("=========================================");

            var stu6 = from p in spInfo
                       group p by p.StudentName;

            foreach (var item in stu6)
            {
                Console.WriteLine("Name={0}", item.Key);
            }

            Console.Read();
        }
    }
}

Output
General Select command
================
ID=1,Name=Naga,Percent=70
ID=2,Name=Mahesh,Percent=80
ID=3,Name=Suresh,Percent=60
ID=4,Name=Sujeet,Percent=75
ID=5,Name=Suresh,Percent=95

Select required columns command
================
Name=Naga
Name=Mahesh
Name=Suresh
Name=Sujeet
Name=Suresh

Select columns Orderby Ascending
==================================
ID=2,Name=Mahesh,Percent=80
ID=1,Name=Naga,Percent=70
ID=4,Name=Sujeet,Percent=75
ID=3,Name=Suresh,Percent=60
ID=5,Name=Suresh,Percent=95

Select columns Orderby Descending order
=========================================
ID=3,Name=Suresh,Percent=60
ID=5,Name=Suresh,Percent=95
ID=4,Name=Sujeet,Percent=75
ID=1,Name=Naga,Percent=70
ID=2,Name=Mahesh,Percent=80

Contans command
=========================================
ID=2,Name=Mahesh,Percent=80

Where command
=========================================
ID=1,Name=Naga,Percent=70
ID=3,Name=Suresh,Percent=60
ID=4,Name=Sujeet,Percent=75

Aggrigate command
=========================================
{ Name = Naga }, { Name = Mahesh }, { Name = Suresh }, { Name = Sujeet }, { Name = Suresh }

Average command
=========================================
76

Count command
=========================================
5

Max command
=========================================
95

Sum command
=========================================
380

Group by command
=========================================
Name=Naga
Name=Mahesh
Name=Suresh
Name=Sujeet




No comments:

Post a Comment

x

Get Updates On

Discussion updates

Straight Into Your INBOX!

Enter your email address to subscribe to this website and receive notifications of new posts by email.