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

Top N Record using LINQ in C#

In this post, I will explain how to get Top N Record using LINQ in C#. 

In below example, I want a record of top 3 employees based on their salary. To get the top 3 records using LINQ I have used Take() extension method of LINQ. 

LINQ Query in C# : 

 int N = 3;

var employees =
    db.Employees
    .Select(emp => emp)
    .OrderBy(emp => emp.Salary)
    .Take(N);

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


Employee Table Script:

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,

)

Employee Table

Top N Employee Record

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

LINQ-To-SQL Vs Stored Procedures?

Advantages of LINQ Vs Stored Procedures :

Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this support. It would be MUCH harder to do this data access code that simply calls Stored Procedures.

Debugging support: I can use any .NET debugger to debug the queries. With sprocs, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn't enough).

Vendor agnostic: LINQ works with lots of databases and the number of supported databases will only increase. Sprocs are not always portable between databases, either because of varying syntax or feature support (if the database supports sprocs at all).

Deployment: Others have mentioned this already, but it's easier to deploy a single assembly than to deploy a set of sprocs. This also ties in with #4.

Easier: You don't have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the sprocs. 

Disadvantages of LINQ Vs Stored Procedures :

Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.

Less flexible: Sprocs can take full advantage of a database's feature set. LINQ tends to be more generic in it's support. This is common in any kind of language abstraction (e.g. C# vs assembler).

Recompiling: If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Sprocs can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.

Security and manageability are something that people argue about too.

Security: For example, you can protect your sensitive data by restricting access to the tables directly, and put ACLs on the sprocs. With LINQ, however, you can still restrict the direct access to tables and instead put ACLs on updatable table views to achieve a similar end (assuming your database supports updatable views).

Manageability: Using views also gives you the advantage of shielding your application non-breaking from schema changes (like table normalization). You can update the view without requiring your data access code to change.

Read More

What is Extension Methods in .Net?

​​

In this post, I will explain about What is Extension Methods in .net?.  An extension method is a method which used to extend a type, such as string or integer, without recompiling or modifying the original type. In essence, they are a type of static method, but they are called as if the method is native to the type. Extension methods are available from the 3.5 version of the .NET Framework and can be implemented on any type in the .NET Framework or any custom type that you define.

An extension method is a static method of a static class that can be invoked using the instance method syntax. Extension methods are used to add new behaviours to an existing type without altering. In the extension method, "this" keyword is used with the first parameter and the type of the first parameter will be the type that is extended by the extension method.

An extension method having the same name and signature like as an instance method will never be called since it has low priority than an instance method. The compiler doesn't cause an error if two extension methods with same name and signature are defined in two different namespaces and these namespaces are included in the same class file using directives. The compiler will cause an error if you will try to call one of them extension method. An extension method is only called when there is no native method found.

​​

Definition of extension method

Public Static class <class_name>

{

​​

        Public static <return_type>  <method_name>(this <type> <type_obj>)

        {

        }

}

Read More