Lesson 13: Language Integrated Query




LINQ

What is Language Integrated Query or LINQ?

LINQ or (Link pronounced) is an integrated part of the recent .Net platforms.The differences between LINQ and normal SQL queries is that; the old command queries are only the statements for the respective databases such as Sql Server.
LINQ is often termed as the native .Net framework query.It has the power to access Datasources such as Sql Server,Collections,XML,Classes etc.
So LINQ has more power than the database specific queries.Moreover the syntax is simpler (if you do not feel terrified or anxious after looking at it).Excellent ground for Lambda Expressions.Table to Class mapping as well as columns to properties.Good for non database programmers

Some Disadvantages

Take special care when you are using LINQ queries for complex data retrieval;It will be your fault,not the LINQ fault if the data retrieval is wrong.
User querying the tradition SQL,sometimes finds it annoying when the LINQ does the same thing but with different logical code and sometimes it becomes more complex.In these cases,the programmer has to learn a lot to become expert in LINQ

Let’s get in action

We will look at three things

1. LINQ to Collections

2. LINQ to SQL

3. LINQ to XML

LINQ to Collections Examples

Example 1

using System;
using System.Linq;

class Program
{
static void Main()
{
SelectLINQ();
SortLINQ();

Console.ReadKey();

}

private static void SelectLINQ()
{

int[] myArray = { 20, 10, 60, 50, 40, 30 };

Console.WriteLine(“Simple array elementsn”);

// Simple select statement for array myArrayElements
var myArrayElements = from element in myArray select element;

foreach (var element in myArrayElements)
{
Console.Write(element);
Console.Write(‘ ‘);
}

}

private static void SortLINQ()
{

int[] myArray = { 20, 10, 60, 50, 40, 30 };
Console.WriteLine(“nnSorted array elementsn”);
// sorting using orderby
var elements= from element in myArray
orderby element ascending
select element;

foreach (var value in elements)
{
Console.Write(value);
Console.Write(‘ ‘);
}

}

}

/* Output

Simple array elements

20 10 60 50 40 30

Sorted array elements

10 20 30 40 50 60

*/

Example 2

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{
static void Main()
{

string[] stringArray = new string[]{“Brilliant”,”Minds”,”Discuss”,”Ideas”};

// First argument is the key, second the value.
Dictionary dictionary = stringArray.ToDictionary(a => a, a => “val”);

//Show keys and values

foreach (KeyValuePair myPair in dictionary)
{
Console.WriteLine(“[Key:{0};Value:{1}]n”,myPair.Key,myPair.Value);
}

Console.ReadKey();
}
}

/*Output

[Key:Brilliant;Value:val]
[Key:Minds;Value:val]
[Key:Discuss;Value:val]
[Key:Ideas;Value:val]

*/

This is an excellent example about LINQ to object with collections for practical scenarios and I am taking it as a reference from MSDN so it belongs to them.I will explain it in my words

Output

The following students and teachers live in Seattle:

Omelchenko

Beebe
Press any key to exit.

Explanation

It is not as difficult as it looks.We have all the OOP concepts cleared till now.Just look at these snippets


class Student
{
public string First { get; set; }
public string Last { get; set; }
public int ID { get; set; }
public string Street { get; set; }
public string City { get; set; }
public List Scores;
}

The above class is public by default and its name is Student.The student must have some attributes which are represented by the public properties.For example:

public string First { get; set; }

This is the short form of that one

/*
private string _first;

public string First
{
get { return _first; }
set { _first = value; }
}

*/

Since we are not applying any logic to the properties,we are just using the short forms to preserve time in writing large code.There is a public field named as Scores of type List (Don’t think about it right now 🙂 ).

There is another class named as Teacher and it also have some properties and it is public by default.

class Teacher
{
public string First { get; set; }
public string Last { get; set; }
public int ID { get; set; }
public string City { get; set; }

}

The DataTransformation class performs the actual operations.

Oh I am terrified……what the hell is that? I haven’t seen such type of syntax before….I am getting anxious!!!
Wait…nothing wrong with that.
We are creating the students object from class Students using the List holding the class Student as an object:
List<Student> students=new List<Student>();

Recall how List was created

using System;
using System.Collections.Generic;

class Program
{
static void Main()
{
List<char> list = new List<char>(){‘a’,’e’,’i’,’o’,’u’};

foreach (char c in list)
{

Console.WriteLine(c);

}

Console.ReadKey();

}
}

Let me remind you that Lists have variety of initialization approaches.Just look at our example.We have made a Generic List of Student class and initialized its instance.

LINQ to SQL Example

LINQ to SQL provides us the facility to fetch/manipulate the data from the database(s) by writing our CLR based LINQ queries.Examples will be demonstrated in our last lesson “GUI with C#”

LINQ to XML:
LINQ has shown great flexibility when dealing with XML documents.It provides clear and easily understandable syntax
for retrieving as well as manipulating the XML document.It is the technology to look for those where a huge amount of work is done with XML data files.
The .Net technology was introduced with the built in classes for accessing XML documents quite easily.XMLElement and XMLDocument are very well know in this regard.With the introduction of .Net Framework 3.5 and the introduction of LINQ;the default classes for LINQ to operate on XML based document(s) are also introduced.These are XElement and XDocument.Both XElement and XDocument can load XML file.XDocument organizes the XML document in an organized order
which satisfies DTD and has at least one root XElement.

Let’s get in action by taking three deep breaths and making the mind positive from any worries 🙂

using System;
using System.Text;
using System.IO;
using System.Linq;
using System.Collections.Generic;
using System.Xml.Linq;

/*<summary>
* LINQ to XML Examples….Howz Dat?
</summary>
*/

class Program
{
static void Main()
{
/*<summary>
Same for both
LoadByXElement();
LoadByXDocument();

<Physician Title=” Specialist”>
<Name> Dr.George</Name>
<Speciality> Neuro Psychiatry</Speciality>
<Room> 101</Room>
</Physician>
<Physician Title=” Specialist”>
<Name> Dr.John</Name>
<Speciality> Oncologist</Speciality>
<Room> 102</Room>
</Physician>
<Physician Title=” General”>
<Name> Dr. William</Name>
<Speciality> Family Medicine</Speciality>
<Room> 103</Room>
</Physician>
</summary>*/

LoadByXElement();

LoadByXDocument();

/*<summary>
Searched by XElement:

Output

<Physician Title=” Specialist”>
<Name> Dr.George</Name>
<Speciality> Neuro Psychiatry</Speciality>
<Room> 101</Room>
</Physician>
<Physician Title=” Specialist”>
<Name> Dr.John</Name>
<Speciality> Oncologist</Speciality>
<Room> 102</Room>
</Physician>

</summary> */
SearchByXElement();

Console.ReadKey();

}

private static void LoadByXElement()
{
try
{
Console.WriteLine(“Loaded by XElement:n”);
XElement x = XElement.Load(“E:hospital.xml”);

/*IEnumerator is useed to iterate through the Elements of the document.
Super easy coding hmmmm… */
IEnumerable<XElement> physicians = x.Elements();

/* Run the loop like a horse and see the results*/
foreach (var dr in physicians)
{
Console.WriteLine(dr);
}

}

catch (Exception ex)
{

Console.WriteLine(ex.Message);

}

}

private static void LoadByXDocument()
{
try
{
Console.WriteLine(“nnLoaded by XDocument:n”);
XDocument x = XDocument.Load(“E:hospital.xml”);

/*IEnumerator is used to iterate through the Elements of the document.
Super easy coding hmmmm… */
IEnumerable<XElement> physicians = x.Elements();

/* Run the loop like a horse and see the results*/
foreach (var dr in physicians)
{
Console.WriteLine(dr);
}

}

catch (Exception ex)
{

Console.WriteLine(ex.Message);

}

}

private static void SearchByXElement()
{
try
{
Console.WriteLine(“nnSearched by XElement:n”);

XElement Xelement = XElement.Load(“E:hospital.xml”);

IEnumerable<XElement> x =
from element in Xelement.Elements(“Physician”)
where (string)element.Attribute(“Title”) == ” Specialist”
select element;
foreach (XElement el in x)
Console.WriteLine(el);

}

catch (Exception ex)
{

Console.WriteLine(ex.Message);

}

}

}

Lesson 13 completed…….

Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.
Syed Adeel Ahmed on FacebookSyed Adeel Ahmed on GoogleSyed Adeel Ahmed on InstagramSyed Adeel Ahmed on Twitter

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.