| by Achyut Kendre | No comments

What is LINQ? How to use LINQ ?

LINQ
LINQ Fundamentals

Example Code: https://drive.google.com/file/d/1CMK36IwA-9Bfay7H9jg71sBtvuCDnQ0d/view?usp=sharing

LINQ is called Language Integrated Query Technology which allow you to query data from various data sources like – Database, XML ,JSON, Entities etc. LINQ is introduced in .NET 3.5 and Visual Studio 2008. The beauty of LINQ is it provides the ability to .NET languages(like C#, VB.NET, etc.) to generate queries to retrieve data from the data source.

Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support. Furthermore, you have to learn a different query language for each type of data source: SQL databases, XML documents, various Web services, and so on.

With LINQ, a query is a first-class language construct, just like classes, methods, events. You write queries against strongly typed collections of objects by using language keywords and familiar operators. The LINQ family of technologies provides a consistent query experience for objects (LINQ to Objects), relational databases (LINQ to SQL), and XML (LINQ to XML).

What are Linq Providers?

LINQ providers you following providers by default when you install the visual studio, you can also download the other providers from Microsoft market place.

LingToSql :- allow you to write LINQ query against any SQL Database.
LinqToObject :- allow you to write LINQ queries against the Objects/ Collections
LinqToXml :- allow you to write LINQ quires against the XML Data Source /XML Files.
LinqToEntities :- allow you to write LINQ quries against the Entity Framework Entieis.
LinqToDatasets:- allow you to write LINQ queries against the ADO.NET Data sets.

You can use the LINQ using –

  1. Query Syntax
  2. Extension Methods
  3. Combine Query Syntax + Extension Methods.

LINQ Queries

LINQ provides you a query syntax like SQL, you can use it to query any data source , following basic operations –

Selection:- Select Data using LINQ

To select data using LINQ we use following syntax –

var variablename=from variablename in datasource select variablename;

Assume a array and numbers and we want to select all elements from that number array you can write LINQ query as follows –

 int[] num = { 34, 45, 56, 12, 37, 67, 78, 89, 90 ,21,23,28};
 var v = from t in num select t;
  foreach (int temp in v)    {
       Console.WriteLine(temp);
   }

Filtration – Filter Data using Condition

We can filter data using where condition in LINQ query –

var variablename= from variablename in datasource where condition select variablename;

Assume array of integer and search for even number greater > 50 as follows –

int[] num = { 34, 45, 56, 12, 37, 67, 78, 89, 90 ,21,23,28};
var v = from t in num where t % 2 == 0 && t>50 select t;
foreach (int temp in v) {
Console.WriteLine(temp);
}

Ordering :- Order Data in ascending or descending order.

We can also order data in ascending or descending data using LINQ query. 

 var variablename = from variablename in datasource
                                    order by variablename asc/desc
                                    select variablename
int[] num = { 34, 45, 56, 12, 37, 67, 78, 89, 90 ,21,23,28};
 var v = from t in num orderby t ascending select t;
 foreach (int temp in v)  {
                Console.WriteLine(temp);
 }

Projection :- select part of object or instance.

Projection enable us, to select part of object or instance is called projection, you can implement projection in LINQ as follows –

var variablename = from variablename in datasource 
                    select new {
                         list of attributes / properties. 
                         };

Implement a class Emp as follows for demo-

class Emp  { 
       public Int64 EmpID { get; set; }
        public string EmpName { get; set; }
        public string DeptName { get; set; }
        public decimal Salary { get; set; }
        public string EmailID { get; set; }
        public void DispEmp()        {
            Console.WriteLine("Emp ID:" + this.EmpID);
            Console.WriteLine("Emp Name:" + this.EmpName);
            Console.WriteLine("Dept Name:" + this.DeptName);
            Console.WriteLine("Salary :" + this.Salary);
            Console.WriteLine("Email ID:" + this.EmailID);
        }
    }
  List<Emp> lst = new List<Emp>();
            lst.Add(new Emp() { EmpID = 121, EmpName = "Akash", DeptName = "Computer", EmailID = "Akash@hotmail.com", Salary = 12000 });
            lst.Add(new Emp() { EmpID = 122, EmpName = "Amol", DeptName = "Accounts", EmailID = "Amol@hotmail.com", Salary = 15000 });
            lst.Add(new Emp() { EmpID = 123, EmpName = "Sunil", DeptName = "Computer", EmailID = "Sunil@hotmail.com", Salary = 19000 });
            lst.Add(new Emp() { EmpID = 124, EmpName = "Sudhir", DeptName = "Purchase", EmailID = "Sudhir@hotmail.com", Salary = 32000 });
            lst.Add(new Emp() { EmpID = 125, EmpName = "Suresh", DeptName = "Computer", EmailID = "Suresh@hotmail.com", Salary = 22000 });
            lst.Add(new Emp() { EmpID = 125, EmpName = "Satish", DeptName = "Purchase", EmailID = "Satish@hotmail.com", Salary = 82000 });
            lst.Add(new Emp() { EmpID = 126, EmpName = "Sandeep", DeptName = "Computer", EmailID = "Sandeep@hotmail.com", Salary = 72000 });

Following query sort the data in descending order of salary and ascending order by name. 

var v = from t in lst where t.DeptName=="Computer" && t.Salary>50000 orderby t.Salary descending,t.EmpName ascending select t;

Following query select Empname, Deptname and salary from list of employees –

var v = from t in lst select new { 
 EName=t.EmpName,  ESal=t.Salary, EDept=t.DeptName
 };

foreach (var temp in v)
{
  Console.WriteLine(temp.EName);
  Console.WriteLine(temp.EDept);
  Console.WriteLine(temp.ESal);
 }

Grouping :- group data for performing summary operations.

Grouping enable us to group data to generate summary from data like sum, max, min ,avg count using the syntax –

var variable = from variablename in datasource group variablename by fieldname into grouped variable select new 
{
 select variable / properties;
}

Find the no of employees , total salary and max of salray, min of salary from list of emps as follows –

  var v = from t in lst group t by t.DeptName into g
          select new {
                        DeptName = g.Key,
                        TotalEmps = g.Count(),
                        TotalSal = g.Sum(p => p.Salary),
                        MaxSal = g.Max(p => p.Salary),
                        MinSal = g.Min(p => p.Salary),
                        AvgSal = g.Average(p => p.Salary)
                    };
 foreach (var temp in v)            {
  Console.WriteLine("Dept Name: =>" + temp.DeptName + " Count:" + temp.TotalEmps + " Total Sal:" + temp.TotalSal + "Max Sal:" + temp.MaxSal + " Min Sal:=>" + temp.MinSal + " AvgSal:" + temp.AvgSal);
      Console.WriteLine("--------------------------------------");
}

That’s all for this article, in next article we will us LINQ to Entities.

« Prev