LINQ – Language Integrated Query

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.

, , ,