What is LINQ?

LINQ (Language Integrated Query) is uniform query syntax in C# and VB.NET to retrieve data from different sources and formats. Language Integrated means it is a part of language. 

SQL is a Structured Query Language used to save and retrieve data from a database. In the same way, LINQ is a structured query syntax built in C# and VB.NET to retrieve data from different types of data sources such as collections, ADO.Net DataSet, XML Docs, web service and MS SQL Server and other databases.

• LINQ stands for Language Integrated Query. 
• LINQ is developed by Microsoft and is available in System.Linq namespace.
• LINQ is uniform query syntax which can be applied on different data source. 
• LINQ offers a compact, expressive, and intelligible syntax for manipulating data.
•  LINQ was introduced in .Net framework 3.5 and C# 3.0.
• LINQ queries return results as objects.

EXAMPLE OF LINQ QUERY

using System;

using System.Linq;

public class Program

{

public static void Main()

{

// Data source

string[] names = {"Harry", "John", "Peter"};

        

// LINQ Query 

                var myLinqQuery =  from name in names

                               where name.Contains('r')

                        select name;

        

// Query execution

                foreach (var name in myLinqQuery)

                    Console.Write(name + " ");

  }

}

Read More

Comma Delimited(Saperated) string of arraylist using LINQ Query

In this post, I will explain how to get Comma Delimited (Separated) string of ArrayList using LINQ Query.

In below example, I want a single string containing all elements from ArrayList with separation character comma(","). 

List<string> str = new List<string>(new string[]{"Harry", "Sandy", "John" , "Mahesh"});

Label1.Text = string.Join(", ", str);

ArrayList

Result Comma Separated String

Read More

Row wise Comma Delimited (Saperated) string using LINQ Query

In this post, I will explain how to get Row wise Comma Delimited (Separated) string using LINQ Query.

In below example, I have two table Department and Employee. Each department having multiple Employees. In result, I want the List of Department with no of employees and a comma-separated list of employees working in that department.

Result Table 

LINQ QUERY RESULT TABLE

In the below query, I have used string.Join extension method which returns a comma-separated value for a list of employees.

Syntax - string.Join(",", {array})  

Query to get Row wise Comma Delimited (Saperated) string in LINQ Query :

 var employees = db.Departments.GroupJoin(db.Employees,

                d => d.DepartmentId,

                em => em.DepartmentId,

                (d, em) => new { dept = d, emp = em })

                .Select(d =>

                    new

                    {

                        Department = d.dept.DepartmentName,

                        Count = d.emp.Count(),

                        EmployeeList = string.Join(", ", d.emp

                                          .Select(p => p.EmployeeName).Distinct())

                    });

            GridView1.DataSource = employees;

            GridView1.DataBind();

Department Table 

DEPARTMENT TABLE

CREATE TABLE [dbo].[Department](

[DepartmentId] [int] IDENTITY(1,1) NOT NULL,

[DepartmentName] [varchar](100) NULL,

)

Employee Table 

EMPLOYEE TABLE

CREATE TABLE [dbo].[Employee](

[EmployeeId] [int] IDENTITY(1,1) NOT NULL,

[EmployeeName] [varchar](100) NULL,

[DepartmentId] [int] NULL,

[Salary] [decimal](18, 2) NULL,

[Age] [int] NULL,

[ZoneId] [int] NULL,

 )

Read More

LINQ Query to find nth Lowest Salary

In this post, I will explain a LINQ Query to find nth Lowest Salary. 

In below LINQ query, the subquery returns the count of a distinct salary lower than current employee salary. Then the result of subquery will be compared with N and record with subquery result count equal N will be returned as result.

int N = 2;
var employees = DB.Employees
               .Where(em1 =>
                        db.Employees.Select(em2 => new { em2.Salary })
                        .Where(em2 => em2.Salary < em1.Salary)
                        .Distinct().Count().Equals(N)
                       );

            GridView1.DataSource = employees;
            GridView1.DataBind();


Table Script-

CREATE TABLE Employee(
 EmployeeId int IDENTITY(1,1) ,
 EmployeeName varchar(100) NULL,
 Salary decimal(18, 2) NULL,
) 

Read More

LINQ Query to find nth Highest Salary

In this post, I will explain a LINQ Query to find nth Highest Salary. 

In below LINQ query subquery returns the count of distinct salary greater than current employee salary. Then the result of subquery will be compared with N and record with subquery result count equal N will be returned as result.

int N = 2;
var employees = db.Employees
               .Where(em1 =>
                        db.Employees.Select(em2 => new { em2.Salary })
                        .Where(em2 => em2.Salary > em1.Salary)
                        .Distinct().Count().Equals(N)
                       );

            GridView1.DataSource = employees;
            GridView1.DataBind();


Table Script-

CREATE TABLE Employee(
 EmployeeId int IDENTITY(1,1) ,
 EmployeeName varchar(100) NULL,
 Salary decimal(18, 2) NULL,
) 

Read More