LINQ Tutorial

Developers worldwide have constantly experienced problems in querying the data due to the lack of specified path and the need to learn multiple technologies like SQL, XQuery, Web Services, etc.

Designed in Visual Studio 2008 and Developed by Anders Hejlsberg, Language Integrated Query(LINQ) enables writing queries without learning the query languages like XML, SQL, etc.

It is completely integrated and provides data access from in-memory databases, XML documents, objects, etc. 

We can write LINQ queries for various data types. LINQ can integrate the queries in Visual Basic and C#. It offers a constant query experience to the query objects, relational databases, and XML.  In this LINQ tutorial, you will learn the core elements of LINQ and study to write different kinds of LINQ queries. 

Table of Content - LINQ Tutorial

➤What is LINQ? ➤ Why LINQ? ➤ LINQ Architecture ➤ LINQ Advantages ➤ Disadvantages of LINQ ➤ Importance of LINQ ➤ Define Query? ➤ Lambda Expression ➤ Aggregate Operators ➤ Sorting Operators

What is LINQ?

LINQ refers to “Language Integrated Query.” LINQ offers the best way to change the data, whether to or from the database with the XML file or with the list of the dynamic data. It is the uniform query system in the C# for retrieving the data from various sources of formats and data.

It is incorporated with the C# for deleting the mismatch between the database and programming language. It offers single query interfaces for different data sources.

For instance, SQL is the Structured Query Language that we use for saving and retrieving data from the database. In the same way. LINQ is the Structured Query Syntax. LINQ is developed in the C#. We use it for retrieving the data from different data sources like docs, XML, collections, MS SQL Server, ADO.Net Dataset, Web Service,  etc.

If you want to enrich your career and become a professional in .Net, then enroll in ".Net Online Training" - This course will help you to achieve excellence in this domain.

Why LINQ?

LINQ(Language Integrated Query) originated in C# 3.0 & .NET framework 3.5. 

For example, if we are working on the SQL Server. For working on the SQL Server, we have to learn the Syntax of SQL Server and SQL-associated ADO.NET objects. After that, we can switch to Oracle database; thus, we have to grasp command on the Oracle syntax and Oracle ADO.NET objects for working with the C#. This problem comes with all the technologies we use. 

In this case, we have to learn all these languages. Therefore, to avoid this problem, Microsoft introduced LINQ.

In the LINQ, we not only work with the famous collections from the database but will work with other data sources like an XML file, memory-data table, etc. We can utilize LINQ queries in two different ways:

1. Method Syntax
2. Query Syntax

LINQ Architecture

This is the LINQ Architecture. The LINQ programming can give the aforementioned .NET programming language. Between the real LINQ query and inherent data source, we have another component called “LINQProvider.”

Provider

The role of the Provider is to transform the LINQ Query into a format such that the data source can comprehend it.

For example, if we take a scenario, let us say that the application needs to retrieve data from the SQL database. In this scenario, it will transform the LINQ query into T-SQL such that the inherent database can grasp it similarly if there is a requirement to retrieve the data from an XML document.

We will utilize the same LINQ query in this scenario also, which is LINQ to XML provider. XML provider will transform the LINQ queries into XSLT such that “XMLDataSource” can grasp them.

LINQ Objects

We have different LINQ objects in VD.NET and C#; they are:

1. LINQ to Datasets
2. LINQ to SQL
3. LINQ to Objects
4. LINQ to XML
5. LINQ to Entities

LINQ Advantages

Following are the advantages of LINQ:

1. We do not have to master the latest query language syntaxes for many data sources as it offers the standard query syntax for many data sources.
2. In LINQ, we need to write less code than the conventional methodology. Through LINQ, we can reduce the code.
3. LINQ offers compile-time error checking and intelligence support in Visual Studio. This feature allows us to evade run-time errors.
4. LINQ offers various in-built methods that we can use to carry out different operations like ordering, filtering, grouping, etc.
5. We can reuse the LINQ queries.

Disadvantages of LINQ

1. Through LINQ, it is difficult to write complex queries like SQL.
2. It was developed in the code, and we cannot use the cache execution plan, which is the feature of SQL like we do in the stored procedures.
3. If the query is not written correctly, then performance can be degraded.
4. If we make some modifications to the queries, then we have to recompile the application and redeploy the dll file to the server.

Related Article: .Net Framework

Importance of LINQ

LINQ is easy, high-level, and more ordered than SQL. When we have to use the Querying database, in all cases, LINQ is a more effective query language than SQL. We also have the advantages of the Intellisense as the LINQ query is developed behind the code. 

LINQ also includes full type checking at the compilation time to find the error in the compilation time itself. In VB.NET or C# for writing the query in LINQ is more straightforward. LINQ offers a “Unified Programming Model.” It enables us to work with many data sources through a standard or unified coding style. Therefore, we don’t need to learn different syntaxes to the query for several data sources.

Define Query?

A query is defined as a group of instructions. They are used in the data sources for performing the operations and display the output shape from that query. It indicates that Query is not accountable for what is output; instead, it is accountable for output shape. Every Query is a mix of the following things:

1. Initialization(for working with a specific data source)
2. Condition(sorting, were, filtering condition)
3. Selection(Group Selection or Joining, Single Selection)

LINQ is the short form of “Language Integrated Query.” The primary feature is enabling the users to develop the queries in the syntax of SQL queries in the code through the query syntaxes.  .Net framework offers the group of in-built query keywords in LINQ to enable the users to develop the SQL style queries. Three methods to write the queries:

1. Using Method Syntax
2. Using Query Syntax
3. Using Mixed Syntax

Syntax of LINQ Query is:

from an object in the data source

where condition

select object

Lambda Expression

Lambda Expression is the function without any name. Lambda Expression makes the syntax clear and short. Although a lambda expression is not readable like the LINQ query, it is similarly essential to the LINQ query, and it transforms into Lambda implicitly. The scope of the lambda expression is restricted when we utilize it as the expression, Lambda expression cannot be utilized afterward. Syntax of Lambda expression is: 

(Input Parameter) => Method Expression

It is dynamic and determines the type at the compilation time. In the lambda expression, on the left-hand side, we have the bracket(), which includes the input parameter. The parameter name can be anything, and after that parameter, we have the “=” symbol followed by the “>” symbol, which we use for sending or passing the parameters from the left side to the right side.

On the right side, we carry out the operation through an input parameter that will pass from the left-side parameter. Example:

Y => Y+10

Related Article: .Net Interview Questions and Answers

Aggregate Functions

Min() Function

It is useful for retrieving the minimum values from a list or collection. It is straightforward to determine the minimum value from the available data source. In LINQ, we have to write the bit code for getting the minimum value from the list of the existing values. Syntax of Min() Function:

Int[] x = {1,2,3,4,5,6,8,7,9,10};

Int minimumNum = x.Min();

Max() Function

It is used to get the maximum value from the collection or list. Using Max() function, we can find the maximum value from the given data set or data source. Syntax of Max() Function:

Int[] y = {4,1,90,2,5,6,8,7,3,9};

int maximumNum = x.Max();

Sum() Function

In LINQ, we use the sum() function for calculating the sum of the items in the lists or collections. We use the below syntax for defining the “sum()” function in C# to sum the items in lists or collections.

int[] z = {2,7,9,5,10,15,12,13,19};

int Sum = z.Sum();

Count() Function

In LINQ, the Count() function is used for counting the number of elements in the collection or list. Aggregate function carries out the action on the first two elements and next carries over the result and third element and then carryforwards. Syntax of Aggregate:

Int[] Num1 = {3,5,7,9};

double Average = Num1.Aggregate((a,b => a+b);

Console.WriteLine(“{0}”, Average);

Sorting Operators

OrderBY

In LINQ, we use the OrderBY operator to sort the collections or lists in ascending order. In the LINQ, if we utilize the “OrderBy” operator, it will sort the values list in ascending order. We do not have ascending conditions in the statement. Syntax of OrderBy:

Var Student1ID = ObStudent1.OrderBy(y = > y.Name1);

OrderBy Descending Operator

In LINQ, OrderBy descending order sorts the elements of collection or item in the descending order. Example:

var studentname =  Obj.Student.OrderByDescending(y => y.name)

ThenBy Operator

ThenBy operator is used for implementing the sorting on the multiple fields. ThenBy operator sorts the group of items in ascending order. Usually, we use the ThenBy operator along with the “OrderBy” operator for implementing sorting on the multiple fields in the collection or list.

If we want multiple conditions on sorting, we will use the “ThenBy” clause with “OrderBy” clause. In LINQ, we will consider the “OrderBy” operator as the main sorting operator and “ThenBy” as the secondary sorting operator. Syntax of ThenBy operator is:

var EmployeeName = ObjEmployee.OrderBy(y = > y.EmployeeName).ThenBy(y = > y.RoleId);

ThenBy Descending Operator

ThenBy Descending operator implements sorting on multiple fields in the collection or list. ThenBy descending operator sorts a group of items in descending order. We can use the ThenByDescending operator along with the “OrderBy” operator. “ThenByDescending” operator specifies the second sorting condition, and the “OrderBy” operator specifies the condition on the primary sorting. Syntax of “ThenByDescending” operator:

var EmployeeName = ObjEmployee.OrderBy(y = > y.EmployeeName). ThenByDescending(y = > y.RoleId);

Partition Operators

In LINQ, we use Partition operators for partitioning the collections or lists items into two parts and returning one part of the list items. Following are the different kinds of partition operators in LINQ:

TAKE

We use the “TAKE” operator for returning the particular number of elements in order. Syntax of TAKE operator:

IEnumerable<string> result = countries.Take(5);

TAKEWHILE

We use the “TAKEWHILE” operator for returning the elements in sequence which fulfills a particular condition. Syntax of TAKEWHILE is as follows:

IEnumerable<string> result = countries.TakeWhile(y = > y.StartsWith(“V”));

SKIP

SKIP Operator skips a particular number of elements in order and returns the rest of the elements. Syntax:

IEnumerable<string>  result = countries.Skip(5);

SKIPWHILE

SKIPWHILE skips the elements in the sequence as per the condition, which is specified as true.

Related Article: Libraries in .Net

Conversion Operators

ToList()

ToList operator takes the element from the existing source and returns the new list. Thus, in this case, we can transform the input into a type list. Syntax of ToList Operator:

List<string> result = states.ToList();

ToArray()

In LINQ, ToArray() converts input elements in the collection to the Array. Syntax of ToArray operator:

string[] statarray = state.ToArray();

ToLookup()

ToLookup() is the extension method that we use for extracting a group of key or value pairs from the data source. Here, every element in the consequent collection is the generic Lookup object. Lookup object stores the subsequence items  

Cast()

In LINQ, we use the Cast() operator for casting or converting every element available in the collection into a particular data type of the new collection. For example, if we have to cast or convert different kinds of elements (Integer/string) in the collection, the collection will fail and throw the exception.

OfType()

OfType() operator returns the elements of a particular type and will neglect other elements from the list or collection. Syntax of OfType() operator is:

Element Operators

Element operators return the first and last elements of the list or single element from a specific collection or element as per the index value from the collection. Following are the Element operators of LINQ:

First

It returns the first element of the sequence or the collection as per the condition.

Last

It returns the last element in the sequence or the last element as per the matching condition.

First or Default

It is similar to the First operator, yet it returns the default value in the case when no element is discovered in the collection.

ElementAt

It returns the element in the sequence or last element as per the matching condition. 

ElementAtOrDefault

It is similar to ElementAt, yet it returns default if no element exists at a particular collection index.

Single

We use this operator for returning a particular element from the collection.

DefaultEmpty

It returns a default value if the collection or list includes null or empty values.

SingleOrDefault

It is similar to single, yet it returns the default value in case no element is discovered in the collection.

GroupBy()

We use the GroupBy operator to group the collection or list items per a particular key value, and it returns the collection of the IGrouping<key, Values>. The GroupBy

method is similar to the SQL “GroupBy” statement. Syntax of GroupBy:

Var student1 = objEmployee.GroupBy(y = > y.Id);

Join() Operators

We use Join() operators for joining multiple lists or collections and retrieving the matched data from the collection as per the defined conditions. The functionality and behavior of the Join() operators are similar to the SQL joins. Following are the different Joins:

Inner Join

It gives the element from collections that satisfy a particular expression.

Cross Join

It gives the cartesian product of the elements in the collection.

Left Outer Join

It returns every element of the left side collection and right side items that match the left side.

Group Join

This join includes the “into” expression.

Set Operators

In LINQ, we use set operators for returning the group of results according to the presence or absence of the corresponding elements in similar or individual collections.  Following are the set operators:

UNION

Union operator merges into a single collection and returns the consequent collection with distinct elements.

DISTINCT

It deletes the duplicate elements from the collection and returns the collection with distinct values.

INTERSECT

It returns the elements in an order which exists in both the input sequences.

EXCEPT

It gives the element from the first input sequence which does not exist in the second input sequence.

LINQ to SQL

LINQ to SQL is an element of the .NET framework. It offers the run-time infrastructure for handling relational data as the objects. It enables us to use and retrieve the data from SQL Database with the LINQ queries. It also enables us to execute insert, select, delete, and update operations on the tables like SQL through LINQ query.

In LINQ to SQL, we map the relational database of the data model to the object model. During the execution of the LINQ, the Object model is transformed into SQL for getting the needed data from a database. While getting the data from the database,  LINQ to SQL will transform the SQL results to the LINQ objects model.

Through LINQ to SQL, we can execute different operations like delete, update, and insert. We can retrieve the data with minor coding in comparison to ADO.Net.

Creating LINQ to SQL and using it in the Web Application

Step1: First, we create a new application. For that, open “Visual Studio/Go to File/New/ Project.” 

Step2: We choose “Asp.Net Empty Web Application” and provide the project name as “LINQTOSQL” and press “OK” as displayed below.

Step3: For using “LINQ to SQL” in our application, we have to create the “DBML” file. DBML files include the source code that enables us to develop LINQ queries for getting the data from the SQL database.

To create the DBML file, “Right-click the project/ Press Add/Choose New Item/Choose template LINQ to SQL” and provide the name as “Student” as displayed below:

Step4: After inserting LINQ to the SQL file, we can open the window as follows:

In the above screen, we can see two sections on the left side. In these sections, we can create or add the tables as the objects, and on the right side, we can insert the stored procedures for getting the data.

Step5: Open the Explorer and right-click the “Data connection,” and Add “Add connection ” as displayed below:

After choosing the Add connection, the latest pop-up will be displayed in that we have to choose “Data Source,” We will be connecting to the available database. That’s why we choose “Microsoft SQL Server‘ and press continue, as displayed below:

Enter the details of the database server for connecting:

After entering the details for connecting to the database, press the “OK” button. After inserting the database, we create the “StudentData” table through the below script:

Create Database College
use college
Create table StudentData
(
RollNumber int Primary Key,
StudentName varchar(50),
FatherName varchar(50),
MotherName varchar(50),
)
Insert into StudentData(RollNumber, StudentName, FatherName, MotherName) values(101.'Akshay','Rakesh Tyagi','Somesh Tyagi')
Insert into StudentData(RollNumber, StudentName, FatherName, MotherName) values(102.'Vaishali','Ashok Tyagi','Munesh Tyagi')
Insert into StudentData(RollNumber, StudentName, FatherName, MotherName) values(103.'Arpitha','Arvind Rai','Anni Rai')
Select * from StudentData

After creating the “StudentData” table in the database, that would be displayed as:

Now drag and drop the table in the “student. dbml” file.

We have to use the “LINQ to SQL” for getting the “StudentData” in the application. For that, right-click the “Application/select Add/ New Item/Select Web Form” Provide name as “Default. aspx” and press “OK” button.

The designing phase of the application

Design:

LINQ to ADO.Net

LINQ to ADO.Net means utilizing the LINQ queries on objects in ADO.Net. The LINQ to ADO.Net provides us the opportunity for writing the LINQ queries on the Enumerable object in the ADO.Net, and LINQ to ADO.Net contains three kinds of LINQ technologies. They are LINQ to SQL, LINQ to Entities, and LINQ to Dataset.

Syntax of LINQ to ADO.Net objects:

Var result=from dt in ds.Tables[ 0 ] . AsEnumerable()
Where(dt.Field <string> ("Gender" == "Male")
select new
{
Name=dt.Field<string>("empname"),
Location=dt.Field<string>("location"),
Gender=dt.Field<string>("gender"),
};

Conclusion

LINQ is a query language that we use for storing and retrieving data. LINQ includes different operators and conditions to filter the data and get the required data. I hope this LINQ tutorial offers sufficient information about LINQ queries and LINQ features. If you have any queries, let us know by commenting below.