LINQ – Language Integrated Query
The name itself denotes that this is a Query not anything – the Query integrated into the working language. After long research with SQL queries, Microsoft has found a easy way to fetch data from objects or from any data source from .NET framework using any language, let it be VB or C# using the concept of LINQ. SQL Query allows one to fetch certain records from millions of records in a single query effectively. This same procedure is followed in LINQ to obtain data from collections or from data source such as XML or SQL data source. In a couple of lines, we can achieve this retrieval using LINQ concept. To get into LINQ concept a little bit of knowledge in SQL query is sufficient. Most of the keywords are similar to that of SQL keywords. This LINQ plays vital role in collection objects.
A basic sample program below illustrates the usage of LINQ:
e.g:
int[] arrNumbers = { 1,9,2,5, 4, 8, 6, 7, 3, 0 };
var number= from n in arrNumbers
select n;
This is a basic program that shows the usage of LINQ to SQL concept.
Here arrNumbers is an array of numbers.
From n in arrNumbers select n; ==> is the actual query and the syntax.
Similar to select * from arrNumbers as SQL query
from denotes where to fetch
ndenotes the user defined variable
indenotes in which item to fetch
arrNumbers is the collection item
select this keyword is must to select the item
Consider the same sample and from the above collection if we need to fetch the numbers that are greater than 5 then the simple query is as follows.
var number= from n in arrNumbers
where n > 5
select n;
The above query is enough which will fetch only the numbers that are greater than 5.
The var keyword is used to declare the type of the object implicitly and the compiler determines the type of the object. The use of var keyword is used commonly in many aspects where explicit declaration of the variables does not take impact.
The operators used in LINQ to SQL are grouped below.
Restriction Operators
Where
Selects items with some conditions implied.
Projection Operators
Select
Selects the items
SelectMany
Selects the items corresponding to the condition specified.
Partitioning Operators
Take
Selects the sequence of items specified in Take.
Skip
Selects the sequence of items rather than that of specified in Skip(Controversa for Take).
TakeWhile
Selects the sequence of items until the specified condition satisfies in TakeWhile.
SkipWhile
Selects the sequence of items starting from the item satisfying the condition specified in SkipWhile.
Ordering Operators
OrderBy
Sorts the items according to the item specified in order by
OrderByDescending
Sorts the items in descending order according to the item specified in order by
ThenBy
Sorts the items according to the item specified in Then by after order by
ThenByDescending
Sorts the items in descending order w.r.t.the item specified in Then by after order by
Reverse
Reverses the order of items
Grouping Operators
GroupBy
Groups the items.
Set Operators
Distinct
Selects the distinct items from the collections.
Union
Joins the items from two different collections.
Intersect
Intersects the items from two different collections.
Except
Selects the items from two different collections leaving the exception items.
Conversion Operators
To Array
Convert the Ienumerable or Iqueriable items into array.
To List
Convert the Ienumerable or Iqueriable items into list.
To Dictionary
Convert the Ienumerable or Iqueriable items into dictionary.
OfType
Convert the Ienumerable or Iqueriable items into specified type.
Element Operators
First
Returns the first item from the result set.
FirstOrDefault
Returns the first item (if exists or returns null) from the result set.
ElementAt
Returns the items from the position specified.
Quantifiers
Any
Checks whether any items in the collections satisfies specified condition.
All
Checks whether all items in the collections satisfies specified condition.
Aggregate Operators
Count
Returns the count of the items in the collections.
Sum
Returns the sum of the items in the collections.
Min
Returns the minimum of the items in the collections.
Max
Returns the maximum of the items in the collections.
Average
Returns the average of the items in the collections.
Aggregate
Returns the aggregate result set as specified in the collections.
Miscellaneous Operators
Concat
Concatenates the two sequence of items.
EqualAll
Checks whether items of two sequence matches or not.
Join Operators
Cross Join
Joins elements of two sequences
Group Join
Matches all items bundled as a sequence and joins them.
Left Outer Join
Matches all items of the first sequnce with the items of the second sequence and joins them. All the left hand side elements are included even if they do not match with right side element.
Thus all these operators form the basis for LINQ expressions.
Anonymous Types:
There are anonymous types in LINQ where we can create our own class with defined properties using LINQ query. Thereby, the generated class will have its own properties specified in the query. This uses the concept of object initializers for creating anonymous types.
The sample usage for Anonymous types is
Let us consider a Student Class with its properties ID, FirstName, LastName, Branch, Department.
public class Student
{
public int ID { get; set; }
public int FirstName { get; set; }
public int LastName { get; set; }
public int Branch { get; set; }
public int Department { get; set; }
}
IF we need not require all those details of Student and Only ID,FirstName,LastName is enough then we can use the Anonymous types as below.
Var result= from stud in StudentList
select new { ID,FirstName,LastName};
Thus in the result set we can get the student details such as ID,FirstName and LastName alone and the other details such as Branch and Department are not retrieved. This type of usage is very much helpful in performance optimization in the data retrieval.
LINQ to SQL:
The LINQ to SQL is the concept for connecting SQL objects from .NET applications. All the mappings with the SQL Server database tables,views and stored procedures to the classes are done automatically with the help of OR-Designer. All the classes for an LINQ to SQL application is generated easily by drag and drop concept. Once the needed tables,views and stored procedures are selected and dragged the classes are generated automatically. We can use the classes directly there by with no other implementations of code. The performance of LINQ to SQL completely depends upon the object usage and the use cases w.r.t the applications.
Here is the sample code that retrieves details of the employee from EmployeeDetail table using the data context object EmployeeDataContext.
using(EmployeeDataContext objEDC=new EmployeeDataContext())
{
var employeeDetails= objEDC.EmployeeDetails
.where(ed => ed.EmployeeID == EmployeeID)
.ToList<EmployeeDetail>();
}
In the above sample objEDC is the data context object. The details from the EmployeeDetails table are fetched as above and the result set is cast into the list of Employee Details object. Thus the data is retrieved using LINQ to SQL query. The same procedure is followed in case of CRUD operations.
