LINQ

Still lots of folks don’t understand what LINQ is doing. Basically LINQ address the current database development model in the context of Object Oriented Programming Model. If some one wants to develop database application on .Net platform the very simple approach he uses ADO.Net. ADO.Net is serving as middle ware in application and provides complete object oriented wrapper around the database SQL. Developing application in C# and VB.Net so developer must have good knowledge of object oriented concept as well as SQL, so it means developer must be familiar with both technologies to develop an application. If here I can say SQL statements are become part of the C# and VB.Net code so it’s not mistaken in form of LINQ. According to Anders Hejlsberg the chief architect of C#.

“Microsoft original motivation behind LINQ was to address the impedance mismatch between programming languages and database.”

LINQ has a great power of querying on any source of data, data source could be the collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable<T> interface. Microsoft basically divides LINQ into three areas and that are give below.

· LINQ to Object {Queries performed against the in-memory data}

· LINQ to ADO.Net

o LINQ to SQL (formerly DLinq) {Queries performed against the relation database only Microsoft SQL Server Supported}

o LINQ to DataSet {Supports queries by using ADO.NET data sets and data tables}

o LINQ to Entities {Microsoft ORM solution}

· LINQ to XML (formerly XLinq) { Queries performed against the XML source}

I hope few above lines increase your concrete knowledge about Microsoft LINQ and now we write some code snippet of LINQ.

Linque

Linque to sql

LINQ to SQL, a component of Visual Studio Code Name "Orcas", provides a run-time infrastructure for managing relational data as objects without losing the ability to query. It does this by translating language-integrated queries into SQL for execution by the database, and then translating the tabular results back into objects you define. Your application is then free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.

LINQ to SQL is designed to be non-intrusive to your application.

It is possible to migrate current ADO.NET solutions to LINQ to SQL in a piecemeal fashion (sharing the same connections and transactions) since LINQ to SQL is simply another component in the ADO.NET family. LINQ to SQL also has extensive support for stored procedures, allowing reuse of the existing enterprise assets.

LINQ to SQL applications are easy to get started.

Objects linked to relational data can be defined just like normal objects, only decorated with attributes to identify how properties correspond to columns. Of course, it is not even necessary to do this by hand. A design-time tool is provided to automate translating pre-existing relational database schemas into object definitions for you.

The DataContext

The DataContext is the main conduit by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select.

Firstly add a linque to sql class and drag your table in to that class than it will create own self it’s entitiy properties and classes

Than it will add datacontext keyword with the name of your class.

// to select data

DataClasses1DataContext ob = new DataClasses1DataContext();

private void button1_Click(object sender, EventArgs e)

{

listBox1.Items.Clear();

var v = from m in ob.Employees

select m;

foreach(var v1 in v)

{

listBox1.Items.Add(v1.e_ID + " " + v1.e_Name + " " + v1.e_Family + " " + v1.e_PhoneNumber);

}

private void button2_Click(object sender, EventArgs e)

{

}

//to insert a record in to the database

Employee obj;

private void button3_Click(object sender, EventArgs e)

{

obj = new Employee();

obj.e_ID = Convert.ToInt32(textBox1.Text);

obj.e_Name = textBox2.Text;

obj.e_Family = textBox3.Text;

obj.e_PhoneNumber = textBox4.Text;

ob.Employees.InsertOnSubmit(obj);

ob.SubmitChanges();

}

/// to delete a record from a database

private void button4_Click(object sender, EventArgs e)

{

var v = ob.Employees.Single(r => r.e_ID == int.Parse(textBox1.Text));

ob.Employees.DeleteOnSubmit(v);

ob.SubmitChanges();

}

//This is to update a record

private void button5_Click(object sender, EventArgs e)

{

Employee ob1=ob.Employees.Single(r => r.e_ID == int.Parse(textBox1.Text));

ob1.e_Name = textBox2.Text;

ob1.e_PhoneNumber = textBox3.Text;

ob1.e_Family = textBox4.Text;

ob.SubmitChanges();

}

// this is to access a first row of a selected records

private void button6_Click(object sender, EventArgs e)

{

listBox1.Items.Clear();

var v=ob.Employees.First(r=>r.e_ID>503);

listBox1.Items.Add(v.e_ID + "" + v.e_Name + " " + v.e_PhoneNumber + " " + v.e_Family);

}

first

var q = from o in db.Orders

        where o.Products.ProductName.StartsWith("Asset") &&

              o.PaymentApproved == true

        select new { name   = o.Contacts.FirstName + " " +

                              o.Contacts.LastName,

                     product = o.Products.ProductName,

                     version = o.Products.Version +

                              (o.Products.SubVersion * 0.1)

};

foreach(var x in q)

    Console.WriteLine("{0} - {1} v{2}",

x.name, x.product, x.version)

second

var q = from call in db.CallLogs

        join contact in db.Contacts on call.Number equals contact.Phone

        select new {contact.FirstName, contact.LastName,

                    call.When, call.Duration};

foreach(var call in q)

    Console.WriteLine("{0} - {1} {2} ({3}min)",

        call.When.ToString("ddMMM HH:mm"),

        call.FirstName.Trim(), call.LastName.Trim(), call.Duration);

third

Linque to object

LINQ to Objects means that we can use LINQ to query objects in a collection. We can access the in-memory data structures using LINQ. We can query any type of object that implements the IEnumerable interface or IEnumerable, which is of generic type. Lists, arrays, and dictionaries are some collection objects that can be queried using LINQ

1. Code Snippet

int[] nums = new int[] {0,1,2};
var res = from a in nums
             where a < 3
             orderby a
             select a;
foreach(int i in res)
    Console.WriteLine(i);

Output:
0
1
2

All SQL Operates are available in LINQ to Object like Sum Operator in the following code.

2. Code Snippet

int[] nums = new int[] {2,4,6,2};
int result = nums.Sum();
Console.WriteLine(result);

Output:
1
2

One thing that I want to share with you guys is LINQ to Object support querying against any object that inherits from IEnumerable (all .Net collection inherits from IEnumerable interface). LINQ to Object provided main types of Operator Type that are give below.

Operator Types	Operator Name
Aggregation	Aggregate Average Count LongCount, Max, Min, Sum
Conversion	Cast, OfType, ToArray, ToDictionary, ToList, ToLookup, ToSequence
Element	DefaultIfEmpty, ElementAt, ElementAtOrDefault, First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault
Equality	EqualAll
Generation	Empty, Range, Repeat
Grouping	GroupBy
Joining	GroupJoin, Join
Ordering	OrderBy, ThenBy, OrderByDescending, ThenByDescending, Reverse
Partitioning	Skip, SkipWhile, Take, TakeWhile
Quantifiers	All, Any, Contains
Restriction	Where
Selection	Select, SelectMany
Set	Concat, Distinct, Except, Intersect, Union

Array of integer

private void button1_Click(object sender, EventArgs e)

{

int[] integers = { 1, 6, 2, 27, 10, 33, 12, 8, 14, 5 };

IEnumerable<int> twoDigits = from numbers in integers

where numbers >= 10

select numbers;

foreach (var number in twoDigits)

{

listBox1.Items.Add(number);

}

First four numbers from a number

private void button2_Click(object sender, EventArgs e)

{

int[] integers = { 1, 6, 2, 27, 10, 33, 12, 8, 14, 5 };

IEnumerable<int> firstFourNumbers = integers.Take(4);

foreach (var number in firstFourNumbers)

{

listBox1.Items.Add(number);

}

Skip first four numbers

private void button3_Click(object sender, EventArgs e)

{

int[] integers = { 1, 6, 2, 27, 10, 33, 12, 8, 14, 5 };

IEnumerable<int> firstFourNumbers = integers.Skip(4);

foreach (var number in firstFourNumbers)

{

listBox1.Items.Add(number);

}

Take while a number found from a array

private void button4_Click(object sender, EventArgs e)

{

int[] integers = { 1, 9, 5, 3, 7, 2, 11, 23, 50, 41, 6, 8 };

IEnumerable<int> takeWhileNumber =integers.TakeWhile(num => num.CompareTo(50) != 0);

foreach (int num in takeWhileNumber)

{

listBox1.Items.Add(num);

}

Skip while a number found from a array

private void button5_Click(object sender, EventArgs e)

{

int[] integers = { 1, 9, 5, 3, 7, 2, 11, 23, 50, 41, 6, 8 };

IEnumerable<int> takeWhileNumber = integers.SkipWhile(num => num.CompareTo(50) != 0);

foreach (int num in takeWhileNumber)

{

listBox1.Items.Add(num);

}

Reading from a string

Read upper character from a string

private void button6_Click(object sender, EventArgs e)

{

string aString = "Satheesh Kumar";

IEnumerable<char> query = from ch in aString

where Char.IsUpper(ch)

select ch;

foreach (char c in query)

{

listBox1.Items.Add(c);

}

Read lower character from a string

private void button6_Click(object sender, EventArgs e)

{

string aString = "Satheesh Kumar";

IEnumerable<char> query = from ch in aString

where Char.IsLower(ch)

select ch;

foreach (char c in query)

{

listBox1.Items.Add(c);

}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace WindowsFormsApplication4

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

List<Student> li;

private void button1_Click(object sender, EventArgs e)

{

//Object Initializer-----------------------

//Student obj = new Student() { Rollno = 12, Name = "Amit", City = "Noida" };

//MessageBox.Show(obj.Name);

// Collection Initializer----------------------------

li = new List<Student>(){

new Student(){Rollno=23,Name="Amit",City="Noida"},

new Student(){Rollno=45,Name="mohit",City="Noida"},

new Student(){Rollno=2,Name="Rohit",City="Noida"},

new Student(){Rollno=12,Name="Akash",City="Noida"},

new Student(){Rollno=9,Name="Raj",City="Jiapur"},

new Student(){Rollno=21,Name="Rakesh",City="Delhi"},

new Student(){Rollno=67,Name="Alok",City="Noida"},

};

//foreach (Student s in li)

//{

// MessageBox.Show(s.Name+", "+s.Rollno+", "+s.City);

//}

//Linq query using lambda expression------------------------

//var ss = (from m in li where m.City == "Noida" orderby m.Name descending select m).First(x => x.Rollno > 20);

// var ss = li.Where(x => x.City == "Noida").OrderByDescending(x => x.Name).First(x => x.Rollno > 20);

//var ss = from m in li where m.City == "Noida" orderby m.Name descending select m

// var v = new { Id = 1, City = "Noida" };

// var v = from m in li where m.City == "Noida"n orderby m.Name descending select new { STudid = m.Rollno, Fname = m.Name };

//foreach (var s in v)

//{

// listBox1.Items.Add(s.STudid+", "+s.Fname);

//}

//var ss = (from m in li group m by m.City into g select g).Sum(xn;

//var ss = li.GroupBy(x => x.City);

//foreach (var s in ss)

//{

// listBox1.Items.Add("Group by :" + s.Key + ",No. of items in group " + s.Count());

// listBox1.Items.Add("---------------------------------------------");

// foreach (var k in s)

// {

// listBox1.Items.Add(k.Rollno + "," + k.Name + ", " + k.City);

// }

//}

//Example from internet--------------------------------------for distinct()

string[] famousQuotes =

{

"Advertising is legalized lying",

"Advertising is the greatest art form of the twentieth century"

};

string[] cities = new string[] { "Ducat", "Noida", "Ducat", "No", "Delhi" };

var d= cities.Distinct();

var query = (from sentence in famousQuotes from word in sentence.Split(' ') select word).Distinct();

}

To the good use of above operator types I need samle patient class so here it

using System;

public class Patient

{

// Fields

private string _name;

private int _age;

private string _gender;

private string _area;

// Properties

public string PatientName

{

get { return _name; }

set { _name = value; }

}

public string Area

{

get { return _area; }

set { _area = value; }

}

public String Gender

{

get { return _gender; }

set { _gender = value; }

}

public int Age

{

get { return _age; }

set { _age = value; }

}

Here is my code that intiliaze patients object with following data.

List<Patient> patients = new List<Patient> {

new Patient { PatientName="Ali Khan", Age=20, Gender="Male" , Area = "Gulshan"},

new Patient { PatientName="Ahmed Siddiqui", Age=25 ,Gender="Male", Area = "NorthKarachi" },

new Patient { PatientName="Nida Ali", Age=20, Gender="Female", Area = "NorthNazimabad"},

new Patient { PatientName="Sana Khan", Age=18, Gender="Female", Area = "NorthNazimabad"},

new Patient { PatientName="Shahbaz Khan", Age=19, Gender="Male", Area = "Gulshan"},

new Patient { PatientName="Noman Altaf", Age=19, Gender="Male", Area = "Gulshan"},

new Patient { PatientName="Uzma Shah", Age=23, Gender="Female", Area = "NorthKarachi"}};

Patient p = new Patient();

p.Age =33; p.Gender = "male";

p.PatientName = "Hammad Ali";

p.Area = "Defence";

patients.Add(p);

I have been written a blog on the new way of initilaztion.

This code snippet fetch those records whose gender is equal to “Male”.

gdView.DataSource = from pa in patients

where pa.Gender == "Male"

orderby pa.PatientName, pa.Gender, pa.Age

select pa;

gdView.DataBind();

The following code snippet uses the selection operator type, which brings all those records whose age is more than 20 years.

var mypatient = from pa in patients

where pa.Age > 20

orderby pa.PatientName, pa.Gender, pa.Age

select pa;

foreach(var pp in mypatient)

{

Debug.WriteLine(pp.PatientName + " "+ pp.Age + " " + pp.Gender);

}

The following code snippet uses the grouping operator type that group patient data on the bases area.

var op = from pa in patients

group pa by pa.Area into g
select new {area = g.Key, count = g.Count(), allpatient = g};

foreach(var g in op)

{

Debug.WriteLine(g.count+ "," + g.area);

foreach(var l in g.allpatient)

{

Debug.WriteLine("\t"+l.PatientName);

}

This code snippet determine the count of those records, which lay in above 20 years.

int patientCount = (from pa in patients

where pa.Age > 20

orderby pa.PatientName, pa.Gender, pa.Age

select pa).Count();

All the above codes are few example of LINQ to Object technique of LINQ. In my up coming post you will see both LINQ to SQL and LINQ to XML code snippets

Linque to xml

For the purposes of this paper let's establish a simple XML contact list sample that we can use throughout our discussion.

Copy

<name>Patrick Hines</name>

<city>Mercer Island</city>

</address>

</contact>

<name>Gretchen Rivas</name>

<city>Mercer Island</city>

</address>

</contact>

<name>Scott MacDonald</name>

<street1>345 Stewart St</street1>

<city>Chatsworth</city>

</address>

</contact>

</contacts>

private void button1_Click(object sender, EventArgs e)

{

XDocument xd=new XDocument

(

new XDeclaration("1.0","utf-8","no"),

new XComment("Document Created at "+DateTime.Now.ToString()),

new XElement("Employees",

new XElement("Employee",new XElement("Id","1"),new XElement("Name","Amit"),new XElement("City","Noida")),

new XElement("Employee",new XElement("Id","4"),new XElement("Name","Aaa"),new XElement("City","Delhi")),

new XElement("Employee",new XElement("Id","5"),new XElement("Name","bbb"),new XElement("City","Jaipur")),

new XElement("Employee",new XElement("Id","7"),new XElement("Name","ccc"),new XElement("City","Noida")),

new XElement("Employee",new XElement("Id","8"),new XElement("Name","ddd"),new XElement("City","Noida"))

));

xd.Save("C:\\myfile.xml");

}

private void button2_Click(object sender, EventArgs e)

{

XDocument doc= XDocument.Load("C:\\myfile.xml");

var v = from m in doc.Descendants("Employee") where m.Element("City").Value == "Noida" select m;

foreach(var a in v)

{

MessageBox.Show(a.Element("Name").Value.ToString());

}

Home | Articles | Resources | Humor | Feedback

.NET

The Standard LINQ Operators

Posted by scott on 25 October 2008

This article will cover the standard LINQ operators provided by LINQ for filtering, grouping, joining, converting, projecting, and more.

The standard LINQ operators are a collection of 50 (give or take a few) methods that form the heart and soul of Language Integrated Query. The beauty of these operators is how they can execute against diverse data sources. Not only can you use the operators locally to query object in memory, but you can use the same operators when working with objects stored in a relational database, in XML, behind a web service, and more. In this article we will take a tour of the query operators and drill into specific topics of interest.

What Is A Standard Query Operator?

The standard query operators are defined as extension methods on the static Enumerable and Queryable classes from the System.Linqnamespace. Extension methods on the Enumerable class extend the IEnumerable<T> interface and generally work locally (in-memory). There are many “data sources” that implement the IEnumerable<T> interface, including arrays, lists, and generally any collection of objects.
Extension methods on the Queryable class extend the IQueryable<T> interface. Objects implementing IQueryable<T> are backed by a LINQ provider that can translate the operators into the native query language of some remote data source. When working with LINQ to SQL, for example, the LINQ to SQL provider translates query operators into T-SQL commands that execute against SQL Server.
The operators themselves fall into one of two categories. There are lazy operators that offer deferred execution of a query. When you write a LINQ query that is only using the lazy operators you are only defining the query, and the query will not execute until you actually try to iterate or consume the results of the query. Other operators are greedy operators that will execute a query immediately. We’ll be pointing out the greedy operators in this article, otherwise you can assume an operator is lazy.
Lazy operators can further be categorized as streaming or non-streaming operators. When a streaming operator executes it does not need to look at all of the data a query is producing. Instead, a streaming operator can work on the sequence one element at a time. A non-streaming operator will need to evaluate all the data in a sequence before it can produce a result. A good example is the OrderBy operator. Before OrderBycan produce its first result, it needs to traverse all the data to calculate which element should be first in the result.
Some of the standard operators have an associated C# keyword assigned for use in the LINQ comprehension query syntax (the query syntax that purposefully resembles SQL). This include the Where, OrderBy, Select, Join, and GroupBy operators, among others. Most of the standard operators, however, do not have an associated keyword in the C# language.
We’ll be breaking the operators down in to the following area of functionality.

Filtering
Projecting
Joining
Ordering
Grouping
Conversions
Sets
Aggregation
Quantifiers
Generation
Elements

Filtering Operators

The two filtering operators in LINQ are the Where and OfType operators. An example follows.

ArrayList list = new ArrayList();

list.Add("Dash");

list.Add(new object());

list.Add("Skitty");

list.Add(new object());

var query =

     from name in list.OfType<string>()

     where name == "Dash"

     select name;

The Where operator generally translates to a WHERE clause when working with a relational database and has an associated keyword in C# as shown in the code above. The OfType operator can be used to coax a non-generic collection (like an ArrayList) into a LINQ query. SinceArrayList does not implement IEnumerable<T>, the OfType operator is the only LINQ operator we can apply to the list.OfType is also useful if you are working with an inheritance hierarchy and only want to select objects of a specific subtype from a collection. This includes scenarios where LINQ to SQL or the Entity Framework are used to model inheritance in the database. Both operators are deferred.

Sorting Operators

The sorting operators are OrderBy, OrderByDescending, ThenBy, ThenByDescending, and Reverse. When working with a comprehension query the C# orderby keyword will map to a corresponding OrderBy method. For example, the following code:

var query =

    from name in names

    orderby name, name.Length

    select name;

… would translate to …

var query =

    names.OrderBy(s => s)

         .ThenBy(s => s.Length);

The return value of the OrderBy operator is an IOrderedEnumerable<T>. This special interface inherits from IEnumreable<T> and allows theThenBy and ThenByDescending operators to work as they extend IOrderedEnumerable<T> instead of IEnumerable<T> like the other LINQ operators. This can sometimes create unexpected errors when using the var keyword and type inference. The query variable in the last code snippet above will be typed as IOrderdEnumerable<T>. If you try to add to your query definition later in the same method …

query = names.Where(s => s.Length > 3);

… you’ll find a compiler error when you build that “IEnumerable<string> cannot be assigned to IOrderEnumerable<string>”. In a case like this you might want to forgo the var keyword and explicitly declare query as an IEnumerable<T>.

Set Operations

The set operators in LINQ include Distinct (to remove duplicate values), Except (returns the difference of two sequences), Intersect (returns the intersection of two sequences), and Union (returns the unique elements from two sequences).

int[] twos = { 2, 4, 6, 8, 10 };

int[] threes = { 3, 6, 9, 12, 15 };

// 6

var intersection = twos.Intersect(threes);

// 2, 4, 8, 10

var except = twos.Except(threes);

// 2, 4, 7, 8, 10, 3, 9, 12, 15

var union = twos.Union(threes);

It’s important to understand how the LINQ operators test for equality. Consider the following in-memory collection of employees.

var employees =

        new List<Employee> {

    new Employee() { ID=1, Name="Scott" },

    new Employee() { ID=2, Name="Poonam" },

    new Employee() { ID=3, Name="Scott"}

};

You might think a query of this collection using the Distinct operator would remove the “duplicate” employee named Scott, but it doesn’t.

// yields a sequence of 3 employees

var query =

      (from employee in employees

       select employee).Distinct();

This version of the Distinct operator uses the default equality comparer which, for non-value types will test object references. The Distinct operator then will see three distinct object references. Most of the LINQ operators that use an equality test (including Distinct) provide an overloaded version of the method that accepts an IEqualityComparer you can pass to perform custom equality tests.
Another issue to be aware of with LINQ and equality is how the C# compiler generates anonymous types. Anonymous types are built to test if the properties of two objects have the same values (see And Equality For All Anonymous Types). Thus, the following query on the same collection would only return two objects.

// yields a sequence of 2 employees

var query = (from employee in employees

             select new { employee.ID, employee.Name })

             .Distinct();

Quantification Operators

Quantifiers are the All, Any, and Contains operators.

int[] twos = { 2, 4, 6, 8, 10 };

// true

bool areAllevenNumbers = twos.All(i => i % 2 == 0);

// true

bool containsMultipleOfThree = twos.Any(i => i % 3 == 0);

// false

bool hasSeven = twos.Contains(7);

Personally, I’ve found these operators invaluable when working with collections of business rules. Here is a simple example:

Employee employee =

        new Employee { ID = 1, Name =

            "Poonam", DepartmentID = 1 };

Func<Employee, bool>[] validEmployeeRules =

    e => e.DepartmentID > 0,

    e => !String.IsNullOrEmpty(e.Name),

    e => e.ID > 0

};

bool isValidEmployee =

      validEmployeeRules.All(rule => rule(employee));

Projection Operators

The Select operators return one output element for each input element. The Select operator has the opportunity to project a new type of element, however, and its often useful to think of the Select operator as performing a transformation or mapping.
The SelectMany operator is useful when working with a sequence of sequences. This operator will “flatten” the sub-sequences into a single output sequence – you can think of SelectMany as something like a nested iterator or nested foreach loop that digs objects out of a sequence inside a sequence. SelectMany is used when there are multiple from keywords in a comprehension query.
Here is an example:

string[] famousQuotes =

    "Advertising is legalized lying",

    "Advertising is the greatest art form of the twentieth

        century"

};

var query =

        (from sentence in famousQuotesfrom word in sentence.Split(' ')

         select word).Distinct();

If we iterate through the result we will see the query produce the following sequence of strings: “Advertising” “is” “legalized” “lying” “the” “greatest” “Art” “form” “of” “twentieth” “century”. The second from keyword will introduce a SelectMany operator into the query and a translation of the above code into extension method syntax will look like the following.

query =

    famousQuotes.SelectMany(s => s.Split(' '))

                .Distinct();

The above code will produce the same sequence of strings. If we used a Select operator instead of SelectMany …

var query2 =

    famousQuotes.Select(s => s.Split(' '))

                .Distinct();

… then the result would be a sequence that contains two string arrays: { “Advertising”, “is”, “legalized”, “lying” } and { “Advertising”, “is”, “the”, “greatest”, “art”, “form”, “of”, “the” “twentieth”, “century” }. We started with an array of two strings and the Select operator projected one output object for each input. The SelectMany operator, however, iterated inside of the string arrays produced by Split to give us back one sequence of many strings.

Partition Operators

The Skip and Take operators in LINQ are the primary partitioning operators. These operators are commonly used to produced paged result-sets for UI data binding. In order to get the third page of results for a UI that shows 10 records per page, you could apply a Skip(20) operator followed by a Take(10).
There are also SkipUntil and TakeUntil operators that accept a predicate that you can use to dynamically express how many items you need.

int[] numbers = { 1, 3, 5, 7, 9 };

// yields 5, 7, 9

var query = numbers.SkipWhile(n => n < 5)

                   .TakeWhile(n => n < 10);

Join Operators

In LINQ there is a Join operator and also a GroupJoin operator. The Join operator is similar to a SQL INNER JOIN in the sense that it only outputs results when it finds a match between two sequences and the result itself is still a flat sequence. You typically need to perform some projection into a new object to see information from both sides of the join.

var employees = new List<Employee> {

    new Employee { ID=1, Name="Scott", DepartmentID=1 },

    new Employee { ID=2, Name="Poonam", DepartmentID=1 },

    new Employee { ID=3, Name="Andy", DepartmentID=2}

};

var departments = new List<Department> {

    new Department { ID=1, Name="Engineering" },

    new Department { ID=2, Name="Sales" },

    new Department { ID=3, Name="Skunkworks" }

};

var query =

     from employee in employees

     join department in departments

       on employee.DepartmentID

           equals department.ID

     select new {

         EmployeeName = employee.Name,

         DepartmentName = department.Name

};

Notice the Join operator has a keyword for use in comprehension query syntax, but you must use the equals keyword and not the equality operator (==) to express the join condition. If you need to join on a “compound” key (multiple properties), you’ll need to create new anonymous types for the left and right hand side of the equals condition with all the properties you want to compare.
Here is the same query written using extension method syntax (no Select operator is needed since the Join operator is overloaded to accept a projector expression).

var query2 = employees.Join(

        departments,         // inner sequence

        e => e.DepartmentID, // outer key selector

        d => d.ID,           // inner key selector

        (e, d) => new {      // result projector

          EmployeeName = e.Name,

          DeparmentName = d.Name

});

Note that neither of the last two queries will have the “Skunkworks” department appear in any output because the Skunkworks department never joins to any employees. This is why the Join operator is similar to the INNER JOIN of SQL. The Join operator is most useful when you are joining on a 1:1 basis.
To see Skunkworks appear in some output we can use the GroupJoin operator. GroupJoin is similar to a LEFT OUTER JOIN in SQL but produces a hierarchical result set, whereas SQL is still a flat result.
Specifically, the GroupJoin operator will always produce one output for each input from the “outer” sequence. Any matching elements from the inner sequence are grouped into a collection that can be associated with the outer element.

var query3 = departments.GroupJoin(

        employees,           // inner sequence d =>

        d.ID,                // outer key selector

        e => e.DepartmentID, // inner key selector

        (d, g) => new        // result projector {

            DepartmentName = d.Name,

            Employees = g

});

In the above query the departments collection is our outer sequence – we will always see all the available departments. The difference is in the result projector. With a regular Join operator our projector sees two arguments – a employee object and it’s matching department object. WithGroupJoin, the two parameters are: a Department object(the d in our result projector lambda expression) and an IEnumerable<Employee> object(the g in our result projector lambda expression). The second parameter represents all the employees that matched the join criteria for this department (and it could be empty, as in the case of Skunkworks). A GroupJoin can be triggered in comprehension query syntax by using a joinfollowed by an into operator.

Grouping Operators

The grouping operators are the GroupBy and ToLookup operators.Both operators return a sequence of IGrouping<K,V> objects. This interface specifies that the object exposes a Key property, and this Key property will represent the grouping value.

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

var query = numbers.ToLookup(i => i % 2);

foreach (IGrouping<int, int> group in query)

    Console.WriteLine("Key:

        {0}", group.Key);

    foreach (int number in group)

        Console.WriteLine(number);

This above query should produced grouped Key values of 0 and 1 (the two possible answers to the modulo of 2). Inside of each group object will be an enumeration of the objects from the original sequence that fell into that grouping. Essentially the above query will have a group of odd numbers (Key value of 1) and a grouping of even numbers (Key value of 2).
The primary difference between GroupBy and ToLookup is that GroupBy is lazy and offers deferred execution. ToLookup is greedy and will execute immediately.

Generational Operators

The Empty operator will create an empty sequence for IEnumerable<T>. Note that we can use this operator using static method invocation syntax since we typically won’t have an existing sequence to work with.

var empty = Enumerable.Empty<Employee>();

The Range operator can generate a sequence of numbers, while Repeat can generate a sequence of any value.

var empty = Enumerable.Empty<Employee>();

int start = 1;

int count = 10;

IEnumerable<int> numbers = Enumerable.Range(start, count);

var tenTerminators = Enumerable.Repeat(new Employee { Name ="Arnold" }, 10);

Finally, the DefaultIfEmpty operator will generate an empty collection using the default value of a type when it is applied to an empty sequence.

string[] names = { }; //empty array

IEnumerable<string>

        oneNullString = names.DefaultIfEmpty();

Equality

The one equality operator in LINQ is the SequenceEquals operator. This operator will walk through two sequences and compare the objects inside for equality. This is another operator where you can override the default equality test using an IEqualityComparer object as a parameter to a second overload of the operator. The test below will return false because the first employee objects in each sequence are not the same.

Employee e1 =

        new Employee() { ID = 1 };

Employee e2 = new Employee() { ID = 2 };

Employee e3 = new Employee() { ID = 3 };

var employees1 = new List<Employee>() { e1, e2, e3 };

var employees2 = new List<Employee>() { e3, e2, e1 };

bool result = employees1.SequenceEqual(employees2);

Element Operators

Element operators include the ElementAt, First, Last, and Single operators. For each of these operators there is a corresponding “or default” operator that you can use in to avoid exceptions when an element does not exist (ElementAtOrDefault, FirstOrDefault, LastOrDefault, SingleOrDefault). Thier behavior is demonstrated in the following code.

string[] empty = { };

string[] notEmpty = { "Hello", "World" };

var result = empty.FirstOrDefault(); // null

result = notEmpty.Last();            // World

result = notEmpty.ElementAt(1);      // World

result = empty.First();              // InvalidOperationException

result = notEmpty.Single();         // InvalidOperationException

result = notEmpty.First(s => s.StartsWith("W"));

The primary difference between First and Single is that the Single operator will throw an exception if a sequence does not contain a single element, whereas First is happy to take just the first element from a sequence of 10. You can use Single when you want to guarantee that a query returns just a single item and generate an exception if the query returns 0 or more than 1 item.

Conversions

The first two conversion operators are AsEnumerable and AsQueryable. The AsEnumerable operator is useful when you want to make a queryable sequence (a sequence backed by a LINQ provider and typically a remote datasource, like a database) into an in-memory collection where all operators appearing after AsEnumerable will work in-memory with LINQ to Object. For example, when working with the queryable properties of a LINQ to SQL data context class, you can return a query to the upper layers of your application with AsEnumerable on the end, meaning the higher layers will not be able to compose operators into the query that change the SQL that LINQ to SQL will generate.
The AsQueryable operator works in the opposite direction – it makes an in-memory collection appear as if it is backed by a remote LINQ provider. This can be useful in unit tests where you want to “fake” a LINQ to SQL queryable collection with in-memory data.
The OfType and Cast operators both coerce types in a sequence. The OfType operator we also listed as a filtering operator – it will only return objects that can be type cast to a specific type, while Cast will fail if it cannot cast all the objects in a sequence to a specific type.

object[] data = { "Foo", 1, "Bar" };

// will return a sequence of 2 strings

var query1 = data.OfType<string>();

// will create an exception when executed

var query2 = data.Cast<string>();

The last four conversion operators are ToList, ToDictionary, ToList, and ToLookup. These are all greedy operators that will execute a query immediately and construct an in-memory data structure. For more on ToList and greediness, see Lazy LINQand Enumerable Objects.

Concatenation

The Concat operator concatenates two sequences and uses deferred execution. Concat is similar to the Union operator, but it will not remove duplicates.

string[] firstNames = { "Scott", "James", "Allen", "Greg" };

string[] lastNames = { "James", "Allen", "Scott", "Smith" };

var concatNames = firstNames.Concat(lastNames).OrderBy(s => s);

var unionNames = firstNames.Union(lastNames).OrderBy(s => s);

The first query will produce the sequence: “Allen”, “Allen”, “Greg”, “James”, “James”, “Scott”, “Scott”, “Smith”.
The second query will produce the sequence: “Allen”, “Greg”, “James”, “Scott”, “Smith”.

Aggregation Operators

No query technology is complete without aggregation, and LINQ includes the usual Average, Count, LongCount (for big results), Max, Min, andSum. For example, here are some aggregation operations in a query that produces statistics about the running processes on a machine:

Process[] runningProcesses = Process.GetProcesses();

var summary = new {

    ProcessCount = runningProcesses.Count(),

    WorkerProcessCount = runningProcesses.Count(p => p.ProcessName == "w3wp"),

    TotalThreads = runningProcesses.Sum(p => p.Threads.Count),

    MinThreads = runningProcesses.Min(p => p.Threads.Count),

    MaxThreads = runningProcesses.Max(p => p.Threads.Count),

    AvgThreads = runningProcesses.Average(p => p.Threads.Count)

};

The most interesting operator in LINQ is the Aggregate operator, which can perform just about any type of aggregation you need (you could also implement mapping, filtering, grouping, and projection with aggregation if you wish). I’ll pass you to my blog entry “Custom Aggregations In LINQ” for more information.

Summary

I hope you’ve enjoyed this tour of the standard LINQ operators. Knowing about all the options available in the LINQ tool belt will empower you to write better LINQ queries with less code. Don’t forget you can also implement custom operators in LINQ if none of these built-in operators fit your solution – all you need to do is write extension methods for IEnumerable<T> or IQueryable<T> (although be careful extending IQueryable<T> as remote LINQ providers will not understand your custom operator). Happy LINQing!

The Blogs
Subscribe to the OdeToCodeblogs for the latest news, downloads, new articles, and quirky commentary.

New Articles
A Software Developer's Guide to HTTP Part II - Messages
A look at request and response messages in HTTP, including HTTP methods, status codes, and headers.

A Software Developer's Guide to HTTP Part I - Resources
A look at HTTP resources and resource locators.

Databinding in Silverlight
This article will cover data binding features in Silverlight, including binding expressions, validation, converters, and binding modes.

Most Popular Articles
Table Variables In T-SQL
Table variables allow you to store a resultset in SQL Server without the overhead of declaring and cleaning up a temporary table. In this article, we will highlight the features and advantages of the table variable data type.

AppSettings In web.config
In this article we will review a couple of pratices to keep your runtime configuration information flexible.

ASP.Net 2.0 - Master Pages: Tips, Tricks, and Traps
MasterPages are a great addition to the ASP.NET 2.0 feature set, but are not without their quirks. This article will highlight the common problems developers face with master pages, and provide tips and tricks to use master pages to their fullest potential.

Contribute Code
Privacy
Consultancy

	LINQ Operators

Letting a Sub-List

We saw that you can get the result of a LINQ statement from the selectsection. In reality, the select statement simply indicates that the result is ready and it hands it to the other parts of the program. Instead of getting the result directly from the select statement, you can first store it in a local LINQ variable. This allows you to treat the result as a variable that you can then manipulate before getting the final result.

To create a local variable in the LINQ statement, you can use the let operator. You must use it before the select statement to hold the result. Here is an example:

var fullNames = from empls

                in employees

                let FullName =  empls.LastName + ", " + empls.FirstName

                select FullName;

foreach (var empl in fullNames)

    Console.WriteLine(empl);

If you need a where condition but your let variable would be used only to hold the final result, you can declare that let variable after the where statement. Here is an example:

var fullNames = from empls

                in employees

                where empls.LastName == "Mukoko"

                let FullName =  empls.LastName + ", " + empls.FirstName

                select FullName;

foreach (var empl in fullNames)

    Console.WriteLine(empl);

You can create the let variable before the where statement and you would get the same result:

var fullNames = from empls

                in employees

                let FullName =  empls.LastName + ", " + empls.FirstName

                where empls.LastName == "Mukoko"

                select FullName;

foreach (var empl in fullNames)

    Console.WriteLine(empl);

Creating a New List

To get the final result of a query, you may want to combine a few fields or properties of the class. For example, as we have seen so far, you may want to combine the last and the first name of each result to create a full name. Besides, or instead of, the let operator, you can use the new operator to create such a combination.

To use the new operator, after the select keyword, type new followed by an opening "{" and a closing curly "}" brackets. Inside the brackets, create an expression as you see fit and assign it to a local variable in the curly brackets. When accessing the result in your foreach loop, apply the period operator on the foreach variable to access the new local variable(s). Here is an example:

var fullNames = from empls

                in employees

                select new { FullName =  empls.LastName + ", " + empls.FirstName };

foreach (var empl in fullNames)

    Console.WriteLine(empl.FullName);

To make the statement easier to read, you can span it on various lines:

var fullNames = from empls

                in employees

                select new

                    FullName =  empls.LastName + ", " + empls.FirstName

};

foreach (var empl in fullNames)

    Console.WriteLine(empl.FullName);

One of the most valuable features of the new operator is that it allows you to create a selected query of fields of the members of the class. For example, you cannot use a select statement to select more than one member of the class that holds the value. On the other hand, you can create a new body in which you list the desired members of the class, each member must be qualified using the period operator. Here are examples:

var fullNames = from empls

                in employees

                select new

                    empls.EmployeeNumber,

                    empls.LastName

};

If you want one of the new fields to be a combination of the members of the class, you must create a name for it and assign the expression to it. Here is an example:

using System;

using System.Linq;

using System.Collections.Generic;

public class Exercise

    static Employee[] employees;

    public static int Main()

        employees = new Employee[]

            new Employee(971974, "Patricia", "Katts", 24.68M),

            new Employee(208411, "Raymond", "Kouma", 20.15M),

            new Employee(279374, "Hélène", "Mukoko", 15.55M),

            new Employee(707912, "Bertrand", "Yamaguchi", 24.68M),

            new Employee(971394, "Gertrude", "Monay", 20.55M)

};

        var staffMembers = from empls

                           in employees

                           select new

                               empls.EmployeeNumber,

                               FullName = empls.LastName + ", " + empls.FirstName,

                               empls.HourlySalary

};

        Console.WriteLine("+========+=====================+========+");

        Console.WriteLine("| Empl # | Full Name           | Salary |");

        foreach (var staff in staffMembers)

            Console.WriteLine("+--------+---------------------+--------+");

            Console.WriteLine("| {0,6} | {1,-19} | {2,6} |", staff.EmployeeNumber,

                staff.FullName, staff.HourlySalary);

        Console.WriteLine("+========+=====================+========+");

        Console.WriteLine();

        return 0;

public class Employee

    public int EmployeeNumber;

    public string FirstName;

    public string LastName;

    public decimal HourlySalary;

    public Employee(int number = 0,

                       string firstName = "John",

                       string lastName = "Doe",

                       decimal salary = 0M)

        EmployeeNumber = number;

        FirstName = firstName;

        LastName = lastName;

        HourlySalary = salary;

    internal string GetFullName()

        return LastName + ", " + FirstName;

This would produce:

In the same way, if you want, in the new body, you can create a name for each field and assign it the member of the class that holds the actual fields. Here are examples:

public class Exercise

    static Employee[] employees;

    public static int Main()

        employees = new Employee[]

            new Employee(971974, "Patricia", "Katts", 24.68M),

            new Employee(208411, "Raymond", "Kouma", 20.15M),

            new Employee(279374, "Hélène", "Mukoko", 15.55M),

            new Employee(707912, "Bertrand", "Yamaguchi", 24.68M),

            new Employee(971394, "Gertrude", "Monay", 20.55M)

};

        var staffMembers = from empls

                           in employees

                           select new

                               Number = empls.EmployeeNumber,

                               FName = empls.FirstName,

                               LName = empls.LastName,

                               Wage = empls.HourlySalary

};

        Console.WriteLine("+========+============+===========+========+");

        Console.WriteLine("| Empl # | First Name | Last Name | Salary |");

        foreach (var staff in staffMembers)

            Console.WriteLine("+--------+------------+-----------+--------+");

            Console.WriteLine("| {0,6} | {1,-10} | {2,-9} | {3,6} |", staff.Number,

                staff.FName, staff.LName, staff.Wage);

        Console.WriteLine("+=======+============+===========+=========+");

        Console.WriteLine();

        return 0;


Home	Copyright © 2010-2011 C# Key

DOT NET SOLUTIONS

Saturday 27 July 2013

LINQ IN ASP.NET

What Is A Standard Query Operator?

Filtering Operators

Sorting Operators

Set Operations

Quantification Operators

Projection Operators

Partition Operators

Join Operators

Grouping Operators

Generational Operators

Equality

Element Operators

Conversions

Concatenation

Aggregation Operators

Summary

No comments:

Post a Comment