LINQ

LINQ :-

It stands for Language Integrated Query. LINQ is collection of standard query operators that provides the query facilities into .NET framework language like C# , VB.NET.

class IntroToLINQ

{       

    static void Main()

    {

        // The Three Parts of a LINQ Query:

        //  1. Data source.

        int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };

        // 2. Query creation.

        // numQuery is an IEnumerable<int>

        var numQuery =

            from num in numbers

            where (num % 2) == 0

            select num;

        // 3. Query execution.

        foreach (int num in numQuery)

        {

            Console.Write("{0,1} ", num);

        }

    }

}

How LINQ is beneficial than Stored Procedures :-

There are couple of advantage of LINQ over stored procedures.

1. Debugging - It is really very hard to debug the Stored procedure but as LINQ is part of .NET, you can use visual studio's debugger to debug the queries.

2. Deployment - With stored procedures, we need to provide an additional script for stored procedures but with LINQ everything gets complied into single DLL hence deployment becomes easy.

3. Type Safety - LINQ is type safe, so queries errors are type checked at compile time. It is really good to encounter an error when compiling rather than runtime exception!

 

LINQ extensions are:

Linq to Objects: This provides the ability to query IEnumerable<T>-based information sources which include arrays, collections, list of objects.

Linq to XML: This provides efficient, easy-to-use, in-memory XML manipulation capabilities to provide XPath/XQuery functionality to the programming languages using simple query operators.

Linq to SQL: This gives the ability to access relational data base objects as normal .Net objects. LINQ to SQL integrates SQL based schema definitions to the common language runtime (CLR) type system. This provides strong typing, syntax checking, intellisense over SQL objects, while retaining the expressive power of the relational model and the performance of query evaluation directly in the underlying database.

Linq to DataSet: Provides the ability to query ADO.NET Dataset.

Linq to Entities: Provides the querying capabilities over ADO.NET entities.

A simple Linq query looks like this:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var lowNums = from n in numbers
  where n < 5
  select n;

In the above query from operator used like foreach keyword, where operator used for conditional selection (restriction) and select is used as projection operator to select the fields from the array. These are called Standard Query Operators.

The query returns an IEnumerable<T> which references to an in-memory object whose CLR type contains an array of integers, whose values are less than 5. These CLR types are called anonymous types because they don't have a type name.

The same syntax is used when we are querying over objects.

List<Product> products = GetProductList();
var expensiveInStockProducts =
from p in products
where p.UnitsInStock > 0 && p.UnitPrice > 20.00M
select p ;
ArrayList list = new ArrayList();
foreach (var i in expensiveInStockProducts){
list.Add(i);

}

ProductsGrid.AutoGenerateColumns = true;
ProductsGrid.DataSource = list;
ProductsGrid.DataBind();

Using Select Operator to Project individual fields :-

var expensiveInStockProducts = from p in products
   where p.UnitsInStock > 0 && p.UnitPrice > 20.00M
   select new { p.ProductID, p.ProductName };

In the above query, the variable expensiveInStockProducts is the type of IEnumerable<anonymous type> references to objects of an anonymous type which contains string ProductID, string ProductName as part of the type definition. Because the Select operator projecting only

ProductID, ProductName fields of product object. These two fields are represented as string fields because both are declared as strings as part of the Product type definition.

Some times we can give a new name to the projected field like below:

var productInfos =
from p in products
select new { p.ProductName, p.Category, Price = p.UnitPrice };

In the above query the UnitPrice field is renamed with Price. And the generated type will have Price as the field name instead of UnitPrice as below.

 

Sorting and Ordering with LINQ :-

The sorting functionality is achieved by using the orderby opertaor. The below query outputs the products ordered by the product name field.

var sortedProducts =
from p in products
orderby p.ProductName
select p;

The sorting can be ascending or descending. By default the sorting will be ascending on the field name specified by orderby operator. If we want to order them in descending order we need to explicitly use the descending keyword followed by the field name on which we want to apply the ordering.

Example,

var sortedProducts =
from p in products
orderby p.Category, p.UnitPrice descending
select p;

Grouping in LINQ :-

As like sql we can group the objects using the 'group by' operator in Linq. A simple query which demonstrates grouping is shown below.

var orderGroups =from p in products
                    group p by p.Category;
ArrayList list = new ArrayList();

foreach (var i in orderGroups)
{

  list.Add(i.Key);
  /*
  foreach ( var g in i.Products )
  the above statement is used to iterate through the Product collection in each group g  in the orderGroups
  */
}

The output of the query orderGroups becomes an enumeration of groups (IEnumerable<IGrouping<string, Product>>). Each item of this enumeration defines a group (IGrouping<string, Product>). As in its definition, IGrouping is defined as the combination of the key of the group (p.Category) and the products grouped by this common key.

Normally we will use grouping to get the group count or group max, min of the items. We can get the group count as below. But if we want to use Max, Min operators on the grouped objects the grouped objects must implement the IComparable inorder to do comparision.

var categoryCounts = from p in
    (from c in products
    group c by c.Category)
    select new { p.Key, ProductCount = p.Count() };

For simplicity the same query can be written using into operator with out using the nested query as shown below.

var categoryCounts =
from p in products
group p by p.Category into g
select new { Category = g.Key, ProductCount = g.Count() };

The output of the above query looks like below.

Set Operators (Distinct, Union, Intersect, Except)

The Distinct operator is used to eliminate duplicate elements from a sequence.

var categoryNames = (
from p in products
select p.Category)
.Distinct();

The output of the query gives an enumerable object and which can be enumerated to get unique categories.

The Union operator is used to combine the objects of two collections, but the output contains only distinct objects from both the collections.

The Intersect operator is used to find the objects which are common in both the collections and outputs the same.

The Except operator is used to find the objects that are present in the first collection but not present in the second collection. An example which shows these three operators usage is shown below.

var productFirstChars =
from p in products
select p.ProductName[0];
var categoriesFirstChars =
from c in products2
select c.Category[0];

var uniqueFirstChars = productFirstChars.Union(categoriesFirstChars);
var IntersectFirstChars = productFirstChars.Intersect(categoriesFirstChars);
var ExceptFirstChars = productFirstChars.Except(categoriesFirstChars);

Join in LINQ

Joins are the most important function of SQL Operators. Linq supports join operations using the joinoperator. The join operator performs an inner join of two collections based on matching keys extracted from the elements. An equijoin can be implemented as below:

string[] categories = new string[]{
"Beverages",
"Condiments",
"Vegetables",
"Dairy Products",
"Seafood" };

var q =
from c in categories
join p in products on c equals p.Category
select new { Category = c, p.ProductName };

The above query joining the categories array with the products collection and the condition for joining is equals means if any category string is matched with category field of the Product object then that pair will be added to join output. And the select operator projecting only the category string and ProductName field of Product. The output will look like

Lambda expression :-

A Lambda expression is nothing but an Anonymous Function, can contain expressions and statements. Lambda expressions can be used mostly to create delegates or expression tree types. Lambda expression uses lambda operator => and read as 'goes to' operator.

Left side of this operator specifies the input parameters and contains the expression or statement block at the right side.

Example: myExp = myExp/10;

Now, let see how we can assign the above to a delegate and create an expression tree:

delegate int myDel(int intMyNum);

        static void Main(string[] args)
        {
            //assign lambda expression to a delegate:
            myDel myDelegate = myExp => myExp / 10;
            int intRes = myDelegate(110);
            Console.WriteLine("Output {0}", intRes);
            Console.ReadLine();

            //Create an expression tree type
            //This needs System.Linq.Expressions
            Expression<myDel> myExpDel = myExp => myExp /10;                                                                                    

        }

Note: The => operator has the same precedence as assignment (=) and is right-associative.

Lambdas are used in method-based LINQ queries as arguments to standard query operator methods such as Where.

Why Select clause comes after from clause in LINQ :-

The reason is, LINQ is used with C# or other programming languages, which requires all the variables to be declared first. From clause of LINQ query just defines the range or conditions to select records. So that’s why from clause must appear before Select in LINQ.

What is the extension of the file, when LINQ to SQL is used :-

The extension of the file is .dbml

What is the use of System.XML.XLinq.dll :-

System.XML.XLinq.dll contains classes to provide functionality to use LINQ with XML.

What is the use of System.Data.DLinq.dll :-

System.Data.DLinq.dll provides functionality to work with LINQ to SQL.

Benefit of using LINQ on Dataset :-

The main aim of using LINQ to Dataset is to run strongly typed queries on Dataset.

Suppose we want to combine the results from two Datasets, or we want to take a distinct value from the Dataset, then it is advisable to use LINQ.

Normally you can use the SQL queries to run on the database to populate the Dataset, but you are not able to use SQL query on a Dataset to retrieve a particular values. To get this you need to use ADO.NET functionalities. But, in case of LINQ, it provides more dignified way of querying the Dataset and provides some new features as compared to ADO.NET.

Quantifiers :-

They are LINQ Extension methods which return a Boolean value

1)All

2)Any

3)Contains

4)SequenceEqual

example:

int[] arr={10,20,30};

var b=arr.All(a=>a>20);

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

Output:

b will return False since all elements are not > 20.

XElement vs XDocument :-

Both are the classes defined by System.Xml.Linq namespace

XElement class
represents an XML fragment

XDocument class represents an entire XML document with all associated meta-data.

example:

XDocument d = new XDocument(
new XComment("hello"),
new XElement("book",
new XElement("bookname", "ASP.NET"),
new XElement("authorname", "techmedia"),
)
);

List the important language extensions made in C# to make LINQ a reality :-
1. Implicitly Typed Variables
2. Anonymous Types
3. Object Initializers
4. Lambda Expressions

Four LINQ Providers that .NET Framework ships :-
1. LINQ to Objects - Executes a LINQ query against a collection of objects
2. LINQ to XML - Executes an XPATH query against XML documents
3. LINQ to SQL - Executes LINQ queries against Microsoft SQL Server.
4. LINQ to DataSets - Executes LINQ queries against ADO.NET DataSets.

OrderBy() vs Sort() method over IList :-

OrderBy() sorts and gives the view IEnumerable(). But underlying list is sorted or not changed.

Sort() modifies the underlying list.

Select() vs SelectMany() :-

Select() converts one type to another and returns enumerable.

SelectMany() flattens the elements and gives a combined list of elements.

Skip() vs SkipWhile() extension methods :-

Skip() will take an integer argument and skips the top n numbers from the given IEnumerable

SkipWhile() continues to skip the elements as long as the input condition is true. Once condition turns false it will return all remaining elements.

Create a XML,

XElement lastName = new XElement ("LastName", "Haldar");
lastName.Add (new XComment ("Nice Name"));

XElement customer = new XElement ("Customer");
customer.Add (new XAttribute ("Id", 123));
customer.Add (new XElement ("FirstName", "Sujit"));
customer.Add (lastName);

customer.Dump();

Read data from XML,

XElement books = XElement.Parse(
@"<books>
    <book no='1'>
        <title>Pro LINQ: Language Integrated Query in C# 2008</title>
        <author>Joe Rattz</author>
    </book>
    <book no='2'>
        <title>Pro WF: Windows Workflow in .NET 3.0</title>
        <author>Bruce Bukovics</author>
    </book>
    <book no='3'>
        <title>Pro C# 2005 and the .NET 2.0 Platform, Third Edition</title>
        <author>Andrew Troelsen</author>
    </book>
</books>");

var titles = from book in books.Elements("book")
    where (string) book.Element("author") == "Joe Rattz"
    select  book.Element("title");

foreach(var title in titles)
    Console.WriteLine(title.Value);

Select Query with And, OR condition and Distinct

var qry = (from e in Employees
          where e.EmpCode == "E-0001" && (e.Address == "Chicago" || e.DeptId == 2)
          select new {e.EmpId, Code = e.EmpCode, EmpName = (e.EmpFName +" "+e.EmpLName), Phone = e.Phone }).Distinct();

qry.Dump();

Order By

var itm = from e in Employees
        where e.EmpCode == "E-0001" && e.DeptId > 0 && e.DeptId < 4 && e.Address.Contains("Chi")
        orderby e.EmpCode,e.EmpLName
        select new {e.EmpId, Code = e.EmpCode, LastName = e.EmpLName, Phone = e.Phone};

itm.Dump();


Group By

var q = from e in Employees
        group e by e.DeptId into Group
//            select Group;
        select new
        {
            DeptId = Group.Key,
            No_of_Employee = Group.Count()
        };
       
q.Dump();

Another way to grouping

var categoryCounts = from p in
    (from e in Employees
    group e by e.DeptId)
    select new { DeptId = p.Key, EmpCount = p.Count() };
   
categoryCounts.Dump();


Inner Join example

var jn = from t1 in Employees
        join t2 in Departments on t1.DeptId equals t2.DeptId
        select new { t1.EmpCode, t2.DeptCode};
jn.Dump();

Inner Join with multiple conditions

var jnm = from t1 in Employees
        join t2 in Departments on new {K1 = t1.DeptId, K2 = t1.EmpId} equals new {K1 = t2.DeptId, K2 = 1}
        where t1.EmpLName == "Haldar"
        select new { t1.EmpCode, t2.DeptCode};
jnm.Dump();

Left Join

var LeftJoin = from t1 in Departments
            join t2 in Employees on t1.DeptId equals t2.DeptId into JoinedEmpDept
            from t22 in JoinedEmpDept.DefaultIfEmpty()
//            where t1.EmpLName == "Haldar"
            select new { EmpCode = t22.EmpCode == null ? "Empty" : t22.EmpCode, DeptCode = t1.DeptCode };

LeftJoin.Dump();

Skip, Take, Distinct

var pagedTitles = itm.Skip(2);
var titlesToShow = itm.Take(2);
var distinctItem = itm.Distinct();