Linq

LINQ :Language-Integrated Query is a powerful query language introduced with .Net 3.5 & Visual Studio 2008.
LINQ (Language Integrated Query) is uniform query syntax in C# and VB.NET to retrieve data from different sources and formats such as collections, ADO.Net DataSet, XML Docs, web service and MS SQL Server and other databases.




LINQ queries return results as objects. 

Refrences:https://www.tutorialsteacher.com/linq/why-linq

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.
• 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.
• IntelliSense Support: LINQ provides IntelliSense for generic collections.
• Shaping data: You can retrieve data in different shapes.

LINQ API : System.Linq

We can write LINQ queries for the classes that implement IEnumerable<T> or IQueryable<T> interface. 

LINQ queries uses extension methods for classes that implement IEnumerable or IQueryable interface. The Enumerable and Queryable are two static classes that contain extension methods to write LINQ queries.

Enumerable :

IEnumerable is an interface defining a single method GetEnumerator() that returns an IEnumerator interface. It is the base interface for all non-generic collections that can be enumerated. This works for read-only access to a collection that implements that IEnumerable can be used with a foreach statement.

1. IEnumerable<T> type of collections are in-memory collection like List, Dictionary, SortedList, Queue, HashSet, LinkedList.

The Enumerable class includes extension methods for the classes that implement IEnumerable<T> interface, for example 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.

Extension Methods:

I expect foreach works on lists because they implement IEnumerable.

IEnumerable is an interface defining a single method GetEnumerator() that returns an IEnumerator interface. It is the base interface for all non-generic collections that can be enumerated. This works for read-only access to a collection that implements that IEnumerable can be used with a foreach statement.

Queryable

The Queryable class includes extension methods for classes that implement IQueryable<t> interface. The IQueryable<T> interface is used to provide querying capabilities against a specific data source where the type of the data is known.

Reference:

https://www.dotnettricks.com/learn/linq/ienumerable-vs-iqueryable

IEnumerable  VS IQueryable

IEnumerable 

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array, etc.
4. While query data from a database, IEnumerable execute a select query on the server side, load data in-memory on a client-side and then filter data.
5. IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
6. IEnumerable supports deferred execution.
7. IEnumerable doesn’t support custom query.
8. IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios.
9. Extension methods support by IEnumerable takes functional objects.

IEnumerable Example

 MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of the above query will be :

 SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is missing since IEnumerable filters records on the client side

IQueryable

1. IQueryable exists in System. Linq Namespace.
2. IQueryable can move forward only over a collection, it can’t move backward and between the items.
3. IQueryable is best to query data from out-memory (like remote database, service) collections.
4. While query data from a database, IQueryable execute the select query on the server side with all filters.
5. IQueryable is suitable for LINQ to SQL queries.
6. IQueryable supports deferred execution.
7. IQueryable supports custom query using CreateQuery and Execute methods.
8. IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
9. Extension methods support by IQueryable takes expression objects means expression tree.

IQueryable Example

 MyDataContext dc = new MyDataContext ();

IQueryable<Employee>list=dc.Employees.Where(p=> p.Name.StartsWith("S"));

list = list.Take<Employee>(10); 

Generated SQL statements of the above query will be :

 SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]

WHERE [t0].[EmpName] LIKE @p0

LINQ Query Syntax :

1. Query Syntax or Query Expression Syntax
2. Method Syntax or Method Extension Syntax or Fluent.         

Query Syntax:

The LINQ query syntax starts with from keyword and ends with select keyword.

LINQ Method Syntax:

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

the signature of the Where extension method, you will find the Where method accepts a predicate delegate as Func<Student, bool>. 

A lambda expression is a block of code (an expression or a statement block) that is treated as an object. It can be passed as an argument to methods, and it can also be returned by method calls. 

Lambda expressions are code that can be represented either as a delegate, or as an expression tree that compiles to a delegate. The specific delegate type of a lambda expression depends on its parameters and return value. 

Standard Query Operators

Standard Query Operators in LINQ are actually extension methods for the IEnumerable<T> and IQueryable<T> types. They are defined in the System.Linq.Enumerable and System.Linq.Queryable classes. There are over 50 standard query operators available in LINQ that provide different functionalities like filtering, sorting, grouping, aggregation, concatenation, etc.

Filtering Operators	Description
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 typ

We can use where clause or .where extension method and can use multiple where in both.
The OfType operator filters the collection based on the ability to cast an element in a collection to a specified type.

var stringResult = mixedList.OfType<string>();

1. The Where operator filters the collection based on a predicate function.
2. The OfType operator filters the collection based on a given type
3. Where and OfType extension methods can be called multiple times in a single LINQ query.

Sorting Operator	Description
OrderBy	Sorts the elements in the collection based on specified fields in ascending or decending 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.

extension method:

OrderByDescending:

var studentsInDescOrder = studentList.OrderByDescending(s => s.StudentName);

Multiple Sorting

You can sort the collection on multiple fields seperated by comma.
var orderByResult = from s in studentList
                   orderby s.StudentName, s.Age 

Sorting Operators: ThenBy & ThenByDescending :


OrderBy and ThenBy sorts collections in ascending order by default.
ThenBy or ThenByDescending is used for second level sorting in method syntax.
ThenByDescending method sorts the collection in decending order on another field.
ThenBy or ThenByDescending is NOT applicable in Query syntax.
Apply secondary sorting in query syntax by separating fields using comma.




Grouping Operators
Description

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

var groupedResult = from s in studentList
                    group s by s.Age;

var groupedResult = studentList.GroupBy(s => s.Age);

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 satisfies specified expression. It is the same as inner join of SQL.

Method Syntax:

var innerJoin = studentList.Join(// outer sequence 
                      standardList,  // inner sequence 
                      student => student.StudentID,    // outerKeySelector
                      standard => standard.StandardID,  // innerKeySelector
                      (student, standard) => new  // result selector
                                    {
                                        StudentName = student.StudentName,
                                        StandardName = standard.StandardName
                                    });

Query Syntax:

var innerJoin = from s in studentList // outer sequence
                      join st in standardList //inner sequence 
                      on s.StandardID equals st.StandardID // key selector 
                      select new { // result selector 
                                    StudentName = s.StudentName, 
                                    StandardName = st.StandardName 
                                };

The GroupJoin operator performs the same task as Join operator except that GroupJoin returns a result in group based on specified group key. 

Method Syntax:

var groupJoin = standardList.GroupJoin(studentList,  //inner sequence
                                std => std.StandardID, //outerKeySelector 
                                s => s.StandardID,     //innerKeySelector
                                (std, studentsGroup) => new // resultSelector 
                                {
                                    Students = studentsGroup,
                                    StandarFulldName = std.StandardName
                                });

//Query Syntax

var groupJoin = from std in standardList 
                    join s in studentList 
                    on std.StandardID equals s.StandardID
                    into studentGroup
                    select new { 
                              Students = studentGroup , 
                              StandardName = std.StandardName 
                          };

Select : The Select operator always returns an IEnumerable collection which contains elements based on a transformation function. 

var selectResult = from s in studentList
                   select new { Name = "Mr. " + s.StudentName, Age = s.Age }; 

var selectResult = studentList.Select(s => new { Name = s.StudentName , 
                                                 Age = s.Age  });

Quantifier Operators:


Operator
Description

All
Checks if all the elements in a sequence satisfies 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

Aggregation Operators: Aggregate:

Method	Description
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 sum of the values in the collection.

Aggregate method that returns comma seperated elements of the string list.

The Aggregate method performs an accumulate operation. 

var commaSeperatedString = strList.Aggregate((s1, s2) => s1 + ", " + s2);

Average extension method calculates the average of the numeric items in the

 collection.

The Count operator returns the number of elements in the collection or 

number of elements that have satisfied the given condition. 

The Max operator returns the largest numeric element from a collection.

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

lement Operators (Methods)
Description

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 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.

If your result set returns many records:


SingleOrDefault throws an exception
FirstOrDefault returns the first record



For LINQ -> SQL:


SingleOrDefault


will generate query like "select * from users where userid = 1"
Select matching record, Throws exception if more than one records found
Use if you are fetching data based on primary/unique key column



FirstOrDefault


will generate query like "select top 1 * from users where userid = 1"
Select first matching rows
Use if you are fetching data based on non primary/unique key column






Equality Operator: SequenceEqual

There is only one equality operator: SequenceEqual. The SequenceEqual method checks whether the number of elements, value of each element and order of elements in two collections are equal or not.

To compare the values of two collection of complex type (reference type or object), 




you need to implement IEqualityComperar<T> interface 

The Concat() method appends two sequences of the same type 

and returns a new sequence (collection).



The DefaultIfEmpty() method returns a new collection with the default value 
if the given collection on which DefaultIfEmpty() is invoked is empty.


Method
Description

Empty
Returns an empty collection
Range
Generates collection of IEnumerable<T> type with specified number of elements with sequential values, starting from first element.
Repeat
Generates a collection of IEnumerable<T> type with specified number of elements and each element contains same specified value.

et Operators
Usage

Distinct
Returns distinct values from a collection.
Except
Returns the difference between 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.

Method
Description

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.

Method
Description

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
Groups elements into an Lookup<TKey,TElement>

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 e.g. SQL Server database.

Read below link data
References:https://www.tutorialsteacher.com/linq/linq-deferred-execution