C# : What is LINQ in C#

LINQ stands for Language Integrated Query. LINQ is a data querying methodology which provides querying capabilities to .NET languages with a syntax similar to a SQL query

LINQ has a great power of querying on any source of data. The data source could be collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable<T> interface.

Advantages of LINQ

1. LINQ offers an object-based, language-integrated way to query over data no matter where that data came from. So through LINQ we can query database, XML as well as collections. 
2. Compile time syntax checking.
3. It allows you to query collections like arrays, enumerable classes etc in the native language of your application, like VB or C# in much the same way as you would query a database using SQL.
   
   

LiNQ stands for Language-Integrated Query. Basically LINQ address the current database development model in the context of Object Oriented Programming Model. If some one wants to develop database application on .Net platform the very simple approach he uses ADO.Net. ADO.Net is serving as middle ware in application and provides complete object oriented wrapper around the database SQL.So developer must have good knowledge of object oriented concept as well as SQL to develop an application. But incase of Linq SQL statements are become part of the C# and VB.Net code so there are less chance of mistake.

LINQ enables developers to query data sources using a query like syntax with both C# and VB.NET. Using LINQ we can query any database and collection.

LINQ Flavors

1. LINQ to Objects

   It provides the facility to query any kind of C# in-memory objects, like as arrays, lists, generic list and other collection types.LINQ to object query returns IEnumerable collection. It provides a new approach to query collection with powerful filtering, ordering and grouping capablities with minimum code.

2. LINQ to Ado.Net

   LINQ to ADO.NET includes different flavors of LINQ to query data from different databases like as Microsoft SQL Server, Oracle, and others. It has following flavours

   1. LINQ to SQL

      It is specifically designed for working with Sql Server database. It provides run-time infrastructure for managing relational data as objects. It also supports transactions, views and stored procedures. It is an object-relational mapping (ORM) framework that allow 1-1 mapping of Sql Server database to .net classes. In this mapping the classes that match the database table are created automatically from the database itself and we can use these classes immediately.

   2. LINQ to DataSet

      It is an easy and faster way to query data cached in a DataSet object. It also allow LINQ to query over any database that can be query with Ado.Net.

   3. LINQ to Entities

      In many ways it is very similar to LINQ to SQL. It uses a conceptual Entity Data Model (EDM). The ADO.NET Entity Framework has been improved in .NET framework 4.0 to query any database like Sql Server, Oracle, MySql, DB2 and many more.

3. LINQ to XML

   It provides an improved XML programming interface. Using this we can query, modify xml document and also save document after modification. System.Xml.Linq namespace contains classes for LINQ to XML.

4. PLINQ (Parallel LINQ)

   PLINQ was introduced in .Net framework 4.0. It extends LINQ to Objects with a new parallel programming library. Using this, we can break/split up a query to execute simultaneously/parallel on different processors.

Different ways to write LINQ query

LINQ provides a uniform programming model (i.e. common query syntax) to query data sources (like SQL databases, XML documents, ADO.NET Datasets, Various Web services and any other objects such as Collections, Generics etc.). LINQ provides you three different ways to write a LINQ query in C# or VB.

Query Expression (Query Syntax)

Query expression syntax is like as SQL query syntax with just a few minor deviations. The result of a query expression is a query object, which is usually a collection of type IEnumerable<T> or IQueryable<T>. This syntax is easy to read and write and at compile time, query expression is converted into Method Invocation.

Query Expression Syntax

from [identifier] 
in [source collection]
let [expression]
where [boolean expression]
order by [expression(ascending/descending)]
select [expression]
group [expression] by [expression] 
into [expression]

Query Expression Example

// Datasource
List<int> numbers = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
 
// Query based syntax
IEnumerable query =
 from num in numbers
 where num > 5 && num < 10
 select num;
//result: 6,7,8,9

Method Invocation (Method Syntax)

Method syntax is complex as compared to Query expression since it uses lambda expression to write LINQ query. It is easily understood by .NET CLR. Hence at compile time, Query expression is converted into Method Invocation. The result of a Method syntax is also a query object, which is usually a collection of type IEnumerable<T> or IQueryable<T>.

[source collection]
.Where [boolean expression]
.OrderBy [expression(ascending/descending)]
.Select [expression]
.GroupBy [expression]

Method Syntax Example

// Datasource
List<int> numbers = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
 
// Method based syntax
IEnumerable<int> query =numbers.Where(num => num > 5 && num < 10)
//result: 6,7,8,9

Note

There are no semantic differences (in terms of performance, execution) between Query Syntax and Method Syntax.

Mixed Syntax

You can use a mixture of both syntax by enclosing a query expression inside parentheses and make a call to method.

Mixed Syntax Example

// Datasource
List<int> numbers = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
 
// Mixed syntax
int query = (from num in numbers
 where num > 5 && num < 10
 select num).Count();
//result: 4





--------------------------------------------------------------------------





Difference between ADO.NET and LINQ to SQL







 As you know LINQ provides a common query syntax to query any data source and ADO.NET allows you to execute query against any RDBMS like SQL Server, Oracle etc. In this article, I am sharing my view on LINQ and ADO.NET.



ADO.NET

LINQ to SQL



It is a part of .NET Framework since .NET Framework 1.0

It is a part of .NET Framework since .NET Framework 3.5



SqlConnection/OleDbConnection is used for database connectivity.

We can use context for database connectivity.



Difficult to debug and cause syntax errors at run-time.

Easy to debug and cause syntax errors at compile-time.



It has full type checking at run-time and not IntelliSense support in Visual Studio, since it used the T-SQL to query the database.

It has full type checking at compile-time and IntelliSense support in Visual Studio, since it used the .NET Framework languages like C# and VB.



It used T-SQL to query the data to query the database and some other syntax for querying the other data source.

It used LINQ to query the data which provides the uniform programming model (means common query syntax) to query the various data sources.












-----------------------------------------------------





Comparing LINQ with Stored Procedure






LINQ provide you common query syntax to query various data sources like SQL Server, Oracle, DB2, WebServices, XML and Collection etc. LINQ also has full type checking at compile-time and IntelliSense support in Visual Studio, since it used the .NET framework languages like C# and VB.NET.



On the other hand a stored procedure is a pre-compiled set of one or more SQL statements that is stored on RDBMS (SQL Server, Oracle, DB2 and MySQL etc.). The main advantage of stored procedures is that they are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic.

A brief comparison of LINQ and Stored Procedure


Stored procedures are faster as compared to LINQ query since they have a predictable execution plan and can take the full advantage of SQL features. Hence, when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure.


LINQ has full type checking at compile-time and Intellisense support in Visual Studio as compared to stored procedure. This powerful feature helps you to avoid run-time errors.


LINQ allows debugging through .NET debugger as compared to stored procedure.


LINQ also supports various .NET framework features like multi –threading as compared to stored procedures.


LINQ provides the uniform programming model (means common query syntax) to query the multiple databases while you need to re-write the stored procedure for different databases.


Stored procedure is a best way for writing complex queries as compared to LINQ.


Deploying LINQ based application is much easy and simple as compared to stored procedures based. Since in case of stored procedures, you need to provide a SQL script for deployment but in case of LINQ everything gets complied into the DLLs. Hence you need to deploy only DLLs.



Limitation of LINQ over Stored Procedures


LINQ query is compiled each and every time while stored procedures re-used the cached execution plan to execute. Hence, LINQ query takes more time in execution as compared to stored procedures.


LINQ is not the good for writing complex queries as compared to stored procedures.


LINQ is not a good way for bulk insert and update operations.


Performance is degraded if you don't write the LINQ query correctly.


If you have done some changes in your query, you have to recompile it and redeploy its DLLs to the server.









----------------------------------------------------------------------------------------


SQL Joins with C# LINQ






There are Different Types of SQL Joins which are used to query data from more than one tables. In this article, I would like to share how joins work in LINQ. LINQ has a JOIN query operator that provide SQL JOIN like behavior and syntax. Let's see how JOIN query operator works for joins. This article will explore the SQL Joins with C# LINQ.




INNER JOIN


LEFT OUTER JOIN


CROSS JOIN


GROUP JOIN







The JOIN query operator compares the specified properties/keys of two collections for equality by using the EQUALS keyword. By default, all joins queries written by the JOIN keyword are treated as equijoins.

LINQ PAD for running and debugging LINQ Query

I am a big fan of LINQ Pad since it allow us to run LINQ to SQL and LINQ to Entity Framework query and gives the query output. Whenever, I need to write LINQ to SQL and LINQ to Entity Framework query then, I prefer to write and run query on LINQ PAD. By using LINQ PAD, you can test and run your desired LINQ query and avoid the head-ache for testing LINQ query with in Visual Studio. You can download the LINQ Pad script used in this article by using this link.

In this article, I am using LINQ PAD for query data from database. It is simple and useful. For more help about LINQ PAD refer the link. You can download the database script used in this article by using this link. Suppose we following three tables and data in these three tables is shown in figure.



INNER JOIN

Inner join returns only those records or rows that match or exists in both the tables.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList(); 



LINQ Pad Query





INNER JOIN among more than two tables

Like SQL, we can also apply join on multiple tables based on conditions as shown below.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID join ct in dataContext.tblCustomers on od.CustomerID equals ct.CustID orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 Customer=ct.Name //define anonymous type Customer
 }).ToList(); 



LINQ Pad Query





INNER JOIN On Multiple Conditions

Sometimes, we required to apply join on multiple coditions. In this case, we need to make two anonymous types (one for left table and one for right table) by using new keyword then we compare both the anonymous types.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID join ct in dataContext.tblCustomers on new {a=od.CustomerID,b=od.ContactNo} equals new {a=ct.CustID,b=ct.ContactNo} orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 Customer=ct.Name //define anonymous type Customer
 }).ToList(); 



LINQ Pad Query







NOTE


Always remember, both the anonymous types should have exact same number of properties with same name and datatype other wise you will get the compile time error "Type inferencce failed in the call to Join".


Both the comparing fields should define either NULL or NOT NULL values.


If one of them is defined NULL and other is defined NOT NULL then we need to do typecasting of NOT NULL field to NULL data type like as above fig.



LEFT JOIN or LEFT OUTER JOIN

LEFT JOIN returns all records or rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

In LINQ to achieve LEFT JOIN behavior, it is mandatory to use "INTO" keyword and "DefaultIfEmpty()" method. We can apply LEFT JOIN in LINQ like as :

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t from rt in t.DefaultIfEmpty() orderby pd.ProductID select new { //To handle null values do type casting as int?(NULL int) 
 //since OrderID is defined NOT NULL in tblOrders
 OrderID=(int?)rt.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 //no need to check for null since it is defined NULL in database
 rt.Quantity,
 rt.Price,
 }).ToList(); 



LINQ Pad Query





CROSS JOIN

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records or rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

In LINQ to achieve CROSS JOIN behavior, there is no need to use Join clause and where clause. We will write the query as shown below.

C# Code

 var q = from c in dataContext.Customers from o in dataContext.Orders select new { c.CustomerID,
 c.ContactName,
 a.OrderID,
 a.OrderDate
 }; 



LINQ Pad Query





GROUP JOIN

Whene a join clause use an INTO expression, then it is called a group join. A group join produces a sequence of object arrays based on properties equivalence of left collection and right collection. If right collection has no matching elements with left collection then an empty array will be produced.

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t orderby pd.ProductID
 select new
 {
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 Order=t
 }).ToList(); 



LINQ Pad Query





Basically, GROUP JOIN is like as INNER-EQUIJOIN except that the result sequence is organized into groups.

GROUP JOIN As SubQuery

We can also use the result of a GROUP JOIN as a subquery like as:

C# Code

 var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t from rt in t where rt.Price>70000 orderby pd.ProductID select new { rt.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 rt.Quantity,
 rt.Price,
 }).ToList(); 



LINQ Pad Query











-------------------------------------------------------------------------------


Single() / SingleOrDefault()




First () / FirstOrDefault()


Single() - There is exactly 1 result, an exception is thrown if no result is returned or more than one result. 

SingleOrDefault() – Same as Single(), but it can handle the null value.

First() - There is at least one result, an exception is thrown if no result is returned.

FirstOrDefault() - Same as First(), but not thrown any exception or return null when there is no result.


Single() asserts that one and only one element exists in the sequence.

First() simply gives you the first one.


When to use

Use Single / SingleOrDefault() when you sure there is only one record present in database or you can say if you querying on database with help of primary key of table.

When to use

Developer may use First () / FirstOrDefault() anywhere,  when they required single value from collection or database.


Single() or SingleOrDefault() will generate a regular TSQL like "SELECT ...".

The First() or FirstOrDefault() method will generate the TSQL statment like "SELECT TOP 1..."


In the case of Fist / FirstOrDefault, only one row is retrieved from the database so it performs slightly better than single / SingleOrDefault. such a small difference is hardly noticeable but when table contain large number of column and row, at this time performance is noticeable.








Single()


Returns a single specific element of a query

When Use: If exactly 1 element is expected; not 0 or more than 1. If the list is empty or has more than one element, it will throw an Exception "Sequence contains more than one element"


SingleOrDefault()


Returns a single specific element of a query, or a default value if no result found

When Use: When 0 or 1 elements are expected. It will throw an exception if the list has 2 or more items.


First()


Returns the first element of a query with multiple results.

When Use: When 1 or more elements are expected and you want only the first. It will throw an exception if the list contains no elements.


FirstOrDefault()




Returns the first element of a list with any amount of elements, or a default value if the list is empty.

When Use: When multiple elements are expected and you want only the first. Or the list is empty and you want a default value for the specified type, the same as default(MyObjectType). For example: if the list type is list<int> it will return the first number from the list or 0 if the list is empty. If it is list<string>, it will return the first string from the list or null if the list is empty.





LINQ Single vs SingleOrDefault vs First vs FirstOrDefault




Many people get confused about the difference between Single, SingleOrDefault, First, and FirstOrDefaultmethods in Linq. Below is a chart explaining the difference between them and examples of each scenario.



Single()
SingleOrDefault()
First()
FirstOrDefault()
Description
Returns a single, specific element of a sequence
Returns a single, specific element of a sequence, or a default value if that element is not found
Returns the first element of a sequence
Returns the first element of a sequence, or a default value if no element is found
Exception thrown when
There are 0 or more than 1 elements in the result
There is more than one element in the result
There are no elements in the result
Only if the source is null (they all do this)
When to use
If exactly 1 element is expected; not 0 or more than 1
When 0 or 1 elements are expected
When more than 1 element is expected and you want only the first
When more than 1 element is expected and you want only the first. Also it is ok for the result to be empty



Examples

First we create an Employee table for querying. We will test with three difference scenarios:

Employeeid = 1: Only one employee with this ID
Firstname = Robert: More than one employee with this name
Employeeid = 10: No employee with this ID


Employeeid
Lastname
Firstname
Birthdate
1
Davolio
Nancy
12/8/1948 12:00:00 AM
2
Fuller
Andrew
2/19/1952 12:00:00 AM
3
Leverling
Janet
8/30/1963 12:00:00 AM
4
Peacock
Margaret
9/19/1937 12:00:00 AM
5
Buchanan
Robert
3/4/1955 12:00:00 AM
6
Suyama
Michael
7/2/1963 12:00:00 AM
7
King
Robert
5/29/1960 12:00:00 AM
8
Callahan
Laura
1/9/1958 12:00:00 AM
9
Dodsworth
Anne
1/27/1966 12:00:00 AM





Single()

Statement


Employee.Single(e => e.Employeeid == 1)




Expected Result
There is only one record where Employeeid == 1. Should return this record.
Actual Result

Employeeid
1
Lastname
Davolio
Firstname
Nancy
Birthdate
12/8/1948 12:00:00 AM




Statement


Employee.Single(e => e.Firstname == "Robert")




Expected Result
There are multiple records where Firstname == Robert. Should fail.
Actual Result
InvalidOperationException: Sequence contains more than one element


Statement


Employee.Single(e => e.Employeeid == 10)




Expected Result
There is no record with Employeeid == 10. Should fail.
Actual Result
InvalidOperationException: Sequence contains no elements






SingleOrDefault()

Statement


Employee.SingleOrDefault(e => e.Employeeid == 1)




Expected Result
There is only one record where Employeeid == 1. Should return this record.
Actual Result

Employeeid
1
Lastname
Davolio
Firstname
Nancy
Birthdate
12/8/1948 12:00:00 AM




Statement


Employee.SingleOrDefault(e => e.Firstname == "Robert")




Expected Result
There are multiple records where Firstname == Robert. Should fail.
Actual Result
InvalidOperationException: Sequence contains more than one element


Statement


Employee.SingleOrDefault(e => e.Employeeid == 10)




Expected Result
There is no record with Employeeid = 10. Should return default value.
Actual Result
null






First()

Statement


Employee.OrderBy(e => e. Birthdate)
.First(e => e.Firstname == "Robert")




Expected Result
There are multiple records where Firstname == Robert. Should return the oldest one.
Actual Result

Employeeid
5
Lastname
Buchanan
Firstname
Robert
Birthdate
3/4/1955 12:00:00 AM




Statement


Employee.OrderBy(e => e. Birthdate)
.First(e => e.Employeeid == 10)




Expected Result
There is no record with Employeeid = 10. Should fail.
Actual Result
InvalidOperationException: Sequence contains no elements






FirstOrDefault()

Statement


Employee.OrderBy(e => e. Birthdate)
.FirstOrDefault(e => e.Firstname == "Robert")




Expected Result
There are multiple records where Firstname == Robert. Should return the oldest one.
Actual Result

Employeeid
5
Lastname
Buchanan
Firstname
Robert
Birthdate
3/4/1955 12:00:00 AM




Statement


Employee.OrderBy(e => e. Birthdate)
.FirstOrDefault(e => e.Employeeid == 10)




Expected Result
There is no record with Employeeid = 10. Should return default value.
Actual Result
null