LINQ (Language Integrated Query)

Introduction: LINQ is a uniform query syntax in C# to save and retrieve data from different sources. It is integrated into C# eliminating the impedance mismatch between programming languages and databases, as well as providing a single querying interface for different types of data sources.

For example, SQL is a Structured Query Language to save and retrieve data from the database the same way LINQ is a structured query syntax built-in C# to save and retrieve data from different types of data sources and formats like an Object Collection, SQL server database, XML, web services, etc.

LINQ queries return results as objects. It enables you to use an object-oriented approach to the result set.

OBJECT ←RETURNS—– LINQ ←EXECUTE QUERY–RETRIEVE RESULT———–> DATA SOURCE

You will not get the result of a LINQ query until you execute it.
Every LINQ query must query to some kind of data source whether it can be array, collections, XML, or other databases. After writing the LINQ query, it must be executed to get the result

Advantages of LINQ:

• Familiar language:- Developers don’t have to learn a new query language for each type of data source or data format.
• Less coding:- It reduces the amount of code to be written as compared with a more traditional approach.
• Readable code:- LINQ makes the code more readable so other developers can easily understand and maintain it.
• A standardized way of querying multiple data sources:- The same LINQ syntax can be used to query multiple data sources.
• Compile-time safety of queries:- It provides type checking of objects at compile time.
• Shaping data:- You can retrieve data in different shapes.
• IntelliSense Support:- LINQ provides IntelliSense for generic collections.

LINQ queries are written for the classes that implement IEnumerable<T> or IQueryable<T>.
LINQ queries use extension methods for classes that implement IEnumerable or IQueryable interfaces. The Enumerable and Queryable are two static classes that contain extension methods to write LINQ queries.
All the built-in collection classes implement IEnumerable<T> interface and so we can write LINQ queries to retrieve data from the built-in collections.
The IQueryable<T> interface is used to provide querying capabilities against a specific data source where the type of the data is known.
It is used to retrieve data from sources like Databases, Web Services, Entity Frameworks, etc.

• Use System.Linq namespace to use LINQ.
• LINQ API includes two main static classes Enumerable & Queryable.
• The static Enumerable class includes extension methods for classes that implement the IEnumerable<T> interface(collections like List, Dictionary, SortedList, Queue, HashSet, and LinkedList).
• The static Queryable class includes extension methods for classes that implement the IQueryable<T> interface.
• Remote query provider implements e.g. Linq-to-SQL, LINQ-to-Amazon, etc.

LINQ Query Syntax:

There are two basic ways to write a LINQ query to IEnumerable collection or IQueryable data sources.

• Query Syntax or Query Expression Syntax
• Method Syntax or Method Extension Syntax or Fluent

Query Syntax:
Query syntax is similar to SQL (Structured Query Language) for the database.

From <range variable> in <IEnumerable<T> or IQueryable<T> Collection>
<Standard Query Operators> <lambda expression>
<select or groupBy operator> <result formation>

Query syntax starts with a From clause followed by a Range variable. After the From clause, you can use different Standard Query Operators to filter, group, join elements of the collection.
We can use the Where clause or Lambda Expression (=>) to express the condition.
LINQ query syntax always ends with a Select or Group By clause. The Select clause is used to shape the data. You can select the whole object as it is or only some properties of it.

• Query Syntax is the same as SQL (Structured Query Language) syntax.
• Query Syntax starts with from clause and can end with the Select or GroupBy clause.
• Use various other operators like filtering, joining, grouping, sorting operators to construct the desired result.
• Implicitly typed variable – var can be used to hold the result of the LINQ query.

LINQ Method Syntax:

Method syntax (also known as fluent syntax) uses extension methods included in the Enumerable or Queryable static class

• Method Syntax is like calling the extension method.
• LINQ Method Syntax (Fluent syntax), allows a series of extension methods to be called.

Lambda Expression:
The lambda expression is a shorter way of representing the anonymous method using some special syntax.
Lambda Expression Structure:
<Parameter> <Lambda Operator> <Body Expression>
x => x>1;

• You can wrap the parameters in parentheses if you need to pass more than one parameter.
• You can also give the type of each parameter if parameters are confusing.
• It is not necessary to have at least one parameter in a lambda expression. The lambda expression can be specified without any parameter also.
• You can wrap expressions in curly braces if you want to have more than one statement in the body.
  (x , y) =>
  {
  //Multiple Statements
  return x >= y;
  }
• You can declare a variable in the expression body to use it anywhere in the expression body.
• The lambda expression can be assigned to Func<in T, out TResult> type delegate. The last parameter type in a Func delegate is the return type and the rest are input parameters.
• An Action delegate can only have input parameters. Use the Action delegate type when you don’t need to return any value from the lambda expression.
• Usually, a lambda expression is used with the LINQ query. Enumerable static class includes the Where extension method for IEnumerable<T> that accepts Func<TSource, bool>. So, the Where() extension method for IEnumerable<Student> collection is required to pass Func.
• Lambda Expression can be invoked in a similar way to delegate.

Standard Query Operators:

Standard Query Operators in LINQ are extension methods for IEnumerable<T> and IQueryable<T> types. They are defined in the System.Linq.Enumerable and System.Linq.Queryable classes. The standard query operators available in LINQ provide different functionalities like filtering, sorting, grouping, aggregation, concatenation, etc.
Standard query operators in query syntax are converted into extension methods at compile time. They can be classified based on the functionality they provide.

• Filtering:- Where, OfType
• Sorting:- OrderBy, OrderByDescending, ThenBy, ThenByDescending, Reverse
• Grouping:- GroupBy, ToLookup
• Join:- GroupJoin, Join
• Projection:- Select, SelectMany
• Aggregation:- Aggregate, Average, Count, LongCount, Max, Min, Sum
• Quantifiers:- All, Any, Contains
• Elements:- ElementAt, ElementAtOrDefault, First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault
• Set:- Distinct, Except, Intersect, Union
• Partitioning:- Skip, SkipWhile, Take, TakeWhile
• Concatenation:- Concat
• Equality:- SequenceEqual
• Generation:- DefaultEmpty, Empty, Range, Repeat
• Conversion:- AsEnumerable, AsQueryable, Cast, ToArray, ToDictionary, ToList

Filtering Operators:

Filtering operators in LINQ filter the sequence (collection) based on some given criteria.
Where:- Returns values from the collection based on a predicate function.
OfType:- Returns values from the collection based on a specified type. However, it will depend on their ability to cast to a specified type.

Where:- The Where operator (Linq extension method) filters the collection based on a given criteria expression and returns a new collection. The criteria can be specified as a lambda expression or Func delegate type. The Where extension method has two overloads. Both overload methods accept a Func delegate type parameter. One overload required Func<TSource, bool> input parameter, and the second overload method required Func<TSource, int, bool> input parameter where int is for index.

• Where is used for filtering the collection based on given criteria.
• Where extension method has two overload methods. Use a second overload method to know the index of the current element in the collection.
• Method Syntax requires the whole lambda expression in the Where extension method whereas Query syntax requires only the expression body.
• Multiple Where extension methods are valid in a single LINQ query.

OfType:- The OfType operator filters the collection based on the ability to cast an element in a collection to a specified type.
OfType<TResult>()
The Where operator filters the collection based on a predicate function.
The OfType operator filters the collection based on a given type
Where and OfType extension methods can be called multiple times in a single LINQ query.

Sorting Operators:
A sorting operator arranges the elements of the collection in ascending or descending order. LINQ includes the following sorting operators.

• OrderBy:- Sorts the elements in the collection based on specified fields in ascending or descending order.
• OrderByDescending:- Sorts the collection based on specified fields in descending order. Only valid in method syntax.
• ThenBy:- Only valid in method syntax. Used for second-level sorting in ascending order.
• ThenByDescending:- Only valid in method syntax. Used for second-level sorting in descending order.
• Reverse:- Only valid in method syntax. Sorts the collection in reverse order.

OrderBy:
OrderBy sorts the values of a collection in ascending or descending order. It sorts the collection in ascending order by default because ascending keyword is optional here. Use descending keywords to sort the collection in descending order.
Method syntax does not allow the descending keyword to sort the collection in descending order. Use OrderByDescending() method for it.

OrderByDescending:(only in Method Syntax)
OrderByDescending sorts the collection in descending order.
OrderByDescending is valid only with the Method syntax. It is not valid in query syntax because the query syntax uses ascending and descending attributes.

Multiple Sorting:(only in Query Syntax)
You can sort the collection on multiple fields separated by commas. The given collection would be first sorted based on the first field and then if the value of the first field would be the same for two elements it would use the second field for sorting and so on.

ThenBy and ThenByDescending:(only in Method Syntax)
Multiple sorting in method syntax is supported by using ThenBy and ThenByDescending extension methods.
The OrderBy() method sorts the collection in ascending order based on the specified fields. Use ThenBy() method after OrderBy to sort the collection on another field in ascending order. Linq will first sort the collection based on the primary field which is specified by the OrderBy method and then sort the resulting collection in ascending order again based on the secondary field specified by the ThenBy method.
In the same way, use the ThenByDescending method to apply secondary sorting in descending order.

Grouping Operators:

The grouping operators do the same thing as the GroupBy clause of SQL query. The grouping operators create a group of elements based on the given key. This group is contained in a special type of collection that implements an IGrouping<TKey, TSource> interface where TKey is a key-value, on which the group has been formed and TSource is the collection of elements that matches with the grouping key value.

GroupBy:- The GroupBy operator returns groups of elements based on some key value. Each group is represented by IGrouping object.
ToLookup:- ToLookup is the same as GroupBy; the only difference is the execution of GroupBy is deferred whereas ToLookup execution is immediate.

GroupBy:
The GroupBy operator returns a group of elements from the given collection based on some key value. Each group is represented by IGrouping<TKey, TElement> object. Also, the GroupBy method has eight overload methods, so you can use the appropriate extension method based on your requirement in method syntax.

ToLookup:
ToLookup is the same as GroupBy; the only difference is GroupBy execution is deferred, whereas ToLookup execution is immediate. Also, ToLookup is only applicable in Method syntax. ToLookup is not supported in the query syntax.

Logically they are the same thing but the performance implications of each are completely different.

• Calling ToLookup means I want a cache of the entire thing right now organized by group.
  .ToLookup() returns a ready-to-use object that already has all the groups (but not the group’s content) eagerly loaded.
• Calling GroupBy means “I am building an object to represent the question ‘what would these things look like if I organized them by group?’”
  .GroupBy() returns a lazy-loaded sequence of groups.

Joining Operator:

The joining operators join the two sequences (collections) and produce a result.
Join:- The Join operator joins two sequences (collections) based on a key and returns a resulting sequence.
GroupJoin:- The GroupJoin operator joins two sequences based on keys and returns groups of sequences. It is like Left Outer Join of SQL.

Join:
The Join operator operates on two collections, inner collection & outer collection. It returns a new collection that contains elements from both the collections which satisfy the specified expression. It is the same as an inner join of SQL.

GroupJoin:
The GroupJoin operator performs the same task as the Join operator except that GroupJoin returns a result in a group based on a specified group key. The GroupJoin operator joins two sequences based on key and groups the result by matching key and then returns the collection of grouped result and key.
GroupJoin requires the same parameters as Join.

Projection Operators:

There are two projection operators available in LINQ.

• Select
• SelectMany

Select:
The Select operator always returns an IEnumerable collection that contains elements based on a transformation function. It is similar to the Select clause of SQL that produces a flat result set.
LINQ query syntax must end with a Select or GroupBy clause.
The select operator can be used to formulate the result as per our requirement. It can be used to return a collection of custom classes or anonymous types which include properties as per our need.

Select Many:
The SelectMany operator projects sequences of values that are based on a transform function and then flatten them into one sequence.

Quantifier Operators:

The quantifier operators evaluate elements of the sequence on some condition and return a boolean value to indicate that some or all elements satisfy the condition.
All:- Checks if all the elements in a sequence satisfy the specified condition
Any:- Checks if any of the elements in a sequence satisfies the specified condition
Contains:- Checks if the sequence contains a specific element.

All:
The All operator evaluates each element in the given collection on a specified condition and returns True if all the elements that satisfy a condition.

Any:
Any checks whether any element satisfies a given condition or not?

Contains:
The Contains operator checks whether a specified element exists in the collection or not and returns a boolean.

Quantifier operators are Not Supported with query syntax in C#
Use custom class that derives IEqualityOperator with Contains to check for the object in the collection. We also need to override Equals and HashCode

Aggregation Operators:

The aggregation operators perform mathematical operations like Average, Aggregate, Count, Max, Min, and Sum, on the numeric property of the elements in the collection.
C# Query Syntax doesn’t support aggregation operators.

• Aggregate:- Performs a custom aggregation operation on the values in the collection.
• Average:- Calculates the average of the numeric items in the collection.
• Count:- Counts the elements in a collection.
• LongCount:- Counts the elements in a collection.
• Max:- Finds the largest value in the collection.
• Min:- Finds the smallest value in the collection.
• Sum:- Calculates the sum of the values in the collection.

Aggregate:
The Aggregate method performs an accumulate operation.

Average:
The average extension method calculates the average of the numeric items in the collection. The average method returns nullable or non-nullable decimal, double, or float values.
You can specify an int, decimal, double, or float property of a class as a lambda expression of which you want to get an average value.

Count:
The Count operator returns the number of elements in the collection or the number of elements that have satisfied the given condition.

Max:
The Max operator returns the largest numeric element from a collection.
Max returns a result of any data type.

Sum:
The Sum() method calculates the sum of numeric items in the collection.

Element Operators:

Element operators return a particular element from a sequence (collection).

• ElementAt:- Returns the element at a specified index in a collection
• ElementAtOrDefault:- Returns the element at a specified index in a collection or a default value if the index is out of range.
• First:- Returns the first element of a collection or the first element that satisfies a condition.
• FirstOrDefault:- Returns the first element of a collection or the first element that satisfies a condition. Returns a default value if the index is out of range.
• Last:- Returns the last element of a collection, or the last element that satisfies a condition
• LastOrDefault:- Returns the last element of a collection or the last element that satisfies a condition. Returns a default value if no such element exists.
• Single:- Returns the only element of a collection, or the only element that satisfies a condition.
• SingleOrDefault:- Returns the only element of a collection or the only element that satisfies a condition. Returns a default value if no such element exists or the collection does not contain exactly one element.

ElementAt:
The ElementAt() method returns an element from the specified index from a given collection. If the specified index is out of the range of a collection then it will throw an index out of range exception. Please note that the index is zero-based.

ElementAtOrDefault:
The ElementAtOrDefault() method also returns an element from the specified index from a collection and if the specified index is out of range of a collection then it will return a default value of the data type instead of throwing an error.
It is advisable to use the ElementAtOrDefault extension method to eliminate the possibility of a runtime exception.

First:
The First() method returns the first element of a collection or the first element that satisfies the specified condition using lambda expression or Func delegate. If a given collection is empty or does not include any element that satisfied the condition then it will throw InvalidOperation exception.

FirstOrDefault:
The FirstOrDefault() method does the same thing as the First() method. The only difference is that it returns the default value of the data type of a collection if a collection is empty or doesn’t find any element that satisfies the condition.

Last:
The Last() method returns the last element from a collection or the last element that satisfies the specified condition using lambda expression or Func delegate. If a given collection is empty or does not include any element that satisfied the condition then it will throw InvalidOperation exception.

LastOrDefault:
The LastOrDefault() method does the same thing as the Last() method. The only difference is that it returns the default value of the data type of a collection if a collection is empty or doesn’t find any element that satisfies the condition.

Single:
Single() returns the only element from a collection or the only element that satisfies the specified condition. If a given collection includes no elements or more than one element then Single() throws InvalidOperationException.

SingleOrDefault:
The SingleOrDefault() method does the same thing as the Single() method. The only difference is that it returns the default value of the data type of a collection if a collection is empty, includes more than one element, or finds no element or more than one element for the specified condition.

Equality Operator:

There is only one equality operator: SequenceEqual.
SequenceEqual:- The SequenceEqual method checks whether the number of elements, values of each element, and order of elements in two collections is equal or not.
If the collection contains elements of primitive data types then it compares the values and number of elements, whereas collection with complex type elements, checks the references of the objects. So, if the objects have the same reference then they are considered as equal otherwise they are considered not equal.

Concatenation Operator:

Concat:- The Concat() method appends two sequences of the same type and returns a new sequence (collection).

Generation Operators:

LINQ includes generation operators DefaultIfEmpty, Empty, Range & Repeat. The Empty, Range & Repeat methods are not extension methods for IEnumerable or IQueryable but they are simply static methods defined in a static class Enumerable.

• DefaultIfEmpty:- Returns a new collection with the default value if the given collection on which DefaultIfEmpty() is invoked is empty.
• Empty:- Returns an empty collection
• Range:- Generates collection of IEnumerable<T> type with specified number of elements with sequential values, starting from the first element.
• Repeat:- Generates a collection of IEnumerable<T> type with a specified number of elements and each element contains same specified value.

DefaultIfEmpty:
The DefaultIfEmpty() method returns a new collection with the default value if the given collection on which DefaultIfEmpty() is invoked is empty.
Another overload method of DefaultIfEmpty() takes a value parameter that should be replaced with a default value.

Empty:
The Empty() method is not an extension method of IEnumerable or IQueryable like other LINQ methods. It is a static method included in the Enumerable static classes. So, you can call it the same way as other static methods like Enumerable.Empty<TResult>().

Range:
The Range() method returns a collection of IEnumerable<T> types with a specified number of elements and sequential values starting from the first element.

Repeat:
The Repeat() method generates a collection of IEnumerable<T> types with a specified number of elements and each element contains same specified value.

Set Operator:

The methods don’t return the correct result for the collection of complex types. You need to implement the IEqualityComparer interface to is get the correct result.

• Distinct:- Returns distinct values from a collection.
• Except:- Returns the difference between the two sequences, which means the elements of one collection that do not appear in the second collection.
• Intersect:- Returns the intersection of two sequences, which means elements that appear in both the collections.
• Union:- Returns unique elements from two sequences, which means unique elements that appear in either of the two sequences.

Distinct:
The Distinct extension method returns a new collection of unique elements from the given collection.

Except:
The Except() method requires two collections. It returns a new collection with elements from the first collection which do not exist in the second collection (parameter collection).

Intersect:
The Intersect extension method requires two collections. It returns a new collection that includes common elements that exist in both collections.

Union:
The Union extension method requires two collections and returns a new collection that includes distinct elements from both collections.

Partitioning Operators:

Partitioning operators split the sequence (collection) into two parts and return one of the parts.

• Skip:- Skips elements up to a specified position starting from the first element in a sequence.
• SkipWhile:- Skips elements based on a condition until an element does not satisfy the condition. If the first element itself doesn’t satisfy the condition, it then skips 0 elements and returns all the elements in the sequence.
• Take:- Takes elements up to a specified position starting from the first element in a sequence.
• TakeWhile:- Returns elements from the first element until an element does not satisfy the condition. If the first element itself doesn’t satisfy the condition then returns an empty collection.

Skip:
The Skip() method skips the specified number of elements starting from the first element and returns the rest of the elements.

SkipWhile:
As the name suggests, the SkipWhile() extension method in LINQ skip elements in the collection until the specified condition is true. It returns a new collection that includes all the remaining elements once the specified condition becomes false for any element.

Take:
The Take() extension method returns the specified number of elements starting from the first element.

TakeWhile:
The TakeWhile() extension method returns elements from the given collection until the specified condition is true. If the first element itself doesn’t satisfy the condition then returns an empty collection.

Conversion Operators:

The Conversion operators in LINQ are useful in converting the type of the elements in a sequence (collection). There are three types of conversion operators: As operators (AsEnumerable and AsQueryable), To operators (ToArray, ToDictionary, ToList, and ToLookup), and Casting operators (Cast and OfType).

• AsEnumerable:- Returns the input sequence as IEnumerable<t>
• AsQueryable:- Converts IEnumerable to IQueryable, to simulate a remote query provider
• Cast:- Coverts a non-generic collection to a generic collection (IEnumerable to IEnumerable<T>)
• OfType:- Filters a collection based on a specified type
• ToArray:- Converts a collection to an array
• ToDictionary:- Puts elements into a Dictionary based on key selector function
• ToList:- Converts collection to List
• ToLookup:- Group elements into a Lookup<TKey, TElement>
  AsEnumerable & AsQueryable:
  The AsEnumerable and AsQueryable methods cast or convert a source object to IEnumerable<T> or IQueryable<T> respectively.

Cast:
The cast does the same thing as AsEnumerable<T>. It cast the source object into IEnumerable<T>.

To Operators:
ToArray(), ToList(), ToDictionary()
As the name suggests, ToArray(), ToList(), ToDictionary() method converts a source object into an array, List or Dictionary respectively.
“To” operators force the execution of the query. It forces the remote query provider to execute a query and get the result from the underlying data source

Expression in LINQ:

The Lambda Expression can be assigned to the Func or Action type delegates to process over in-memory collections. The .NET compiler converts the lambda expression assigned to Func or Action type delegate into executable code at compile time.
LINQ introduced a new type called Expression that represents strongly typed lambda expression. It means lambda expression can also be assigned to Expression<TDelegate> type. The .NET compiler converts the lambda expression which is assigned to Expression<TDelegate> into an Expression tree instead of executable code. This expression tree is used by remote LINQ query providers as a data structure to build a runtime query out of it (such as LINQ-to-SQL, EntityFramework, or any other LINQ query provider that implements IQueryable<T> interface).

Define an Expression:
Take the reference of System.Linq.Expressions namespace and use an Expression<TDelegate> class to define an Expression. Expression<TDelegate> requires delegate type Func or Action.

Invoke an Expression:
You can invoke the delegate wrapped by an Expression the same way as a delegate, but first, you need to compile it using the Compile() method. Compile() returns delegate of Func or Action type so that you can invoke it like a delegate.

Expression Tree:

Expression tree as the name suggests is nothing but expressions arranged in a tree-like data structure. Each node in an expression tree is an expression. For example, an expression tree can be used to represent the mathematical formula x < y where x < and y will be represented as an expression and arranged in a tree-like structure.
An expression tree is an in-memory representation of a lambda expression. It holds the actual elements of the query, not the result of the query.
The expression tree makes the structure of the lambda expression transparent and explicit. You can interact with the data in the expression tree just as you can with any other data structure.
The lambda expression assigned to Func<T> compiles into executable code and the lambda expression assigned to Expression<TDelegate> type compiles into an Expression tree.
Executable code executes in the same application domain to process over in-memory collection. Enumerable static classes contain extension methods for in-memory collections that implements IEnumerable<T> interface e.g. List<T>, Dictionary<T>, etc. The Extension methods in an Enumerable class accept a predicate parameter of Func type delegate. For example, the Where extension method accepts Func<TSource, bool> predicate. It then compiles it into IL (Intermediate Language) to process over in-memory collections that are in the same AppDomain.

Deferred Execution of LINQ Query:

Deferred execution means that the evaluation of an expression is delayed until its realized value is required. It greatly improves performance by avoiding unnecessary execution.

Deferred execution is applicable on any in-memory collection as well as remote LINQ providers like LINQ-to-SQL, LINQ-to-Entities, LINQ-to-XML, etc.

Implementing Deferred Execution:
You can implement deferred execution for your custom extension methods for IEnumerable using the yield keyword of C#.

Immediate Execution of LINQ Query:
Immediate execution is the reverse of deferred execution. It forces the LINQ query to execute and gets the result immediately. The ‘To’ conversion operators execute the given query and give the result immediately.

let keyword:
The ‘let’ keyword is useful in query syntax. It projects a new range variable, allows re-use of the expression, and makes the query more readable.

into keyword:
The ‘into’ keyword is used to continue a query after a select clause i.e. nested select queries.
It is also used in the grouping.