LINQ Tutorial

LINQ consists of a set of query operators.These query operators transforms a sequence .Sequence is just a collection of objects.Sequences in LINQ are used by the LINQ operators in queries.
There are two sequences which can be used in LINQ queries

• IEnumerable
• IQueryable

LINQ operators can be applied to any collection which implements any of these interfaces.There are lots of collections which implements these types.Some of the common collections which implements these types are

• Array
• Generic list ,List<T>
• LinkedList<T>
• HashSet<T>
• Collection<T>
• Dictionary<TKey,TValue>

The query operators are defined in the System.Linq namespace and are defined as extension methods.So to apply these operators we need to import the System.Linq namespace.

In the following example we are querying an array which contains different weather conditions.We are selecting all the weather conditions which are not equal to “Hot”

string[] weather = { "Hot", "Cool", "Moderate" };
var result=weather.Where(x => x != "Hot").Select(x=>x.ToString());
foreach(var item in result)
Console.WriteLine(item);

The collection weather will contain the values “Cool” and “Moderate”

There are few important things to understand about the above code.Where is a query operator which filters the items in a collection based on a condition

• Select is a query operator which is used for projecting the items in the result collection
• Select returns an object of type IEnumerable<TResult> where TResult represents the type of items in the result collection

So in the above example instead of the following line

var result=weather.Where(x => x != "Hot").Select(x=>x.ToString());

we can use the below

IEnumerable<string> result = weather.Where(x => x != "Hot").Select(x => x.ToString());

IQueryable<T>

Another Sequence in LINQ is IQueryable<T>.Like IEnumerable<T> it is used in LINQ queries. IQueryable<T> implements the IEnumerable<T> interface.
The difference is that IQueryable<T> is used to execute queries remotely.It is used with query providers such as LINQ to SQL.Different query providers can provide their own implementations of IQueryable<T>.

IQueryable<T> constructs queries using expression tree unlike IEnumerable<T> which constructs queries using delegates.Expression tree is used to create an actual query on the remote data source.

AsEnumerable()

The AsEnumerable() method can be called on collections which implements IEnumerable<T>.For example DataTable type in ADO.NET has its own implementation of query operator.By calling AsEnumerable() method on the table we can use the Standard query operators.If we don’t use AsEnumerable() on the DataTable then the custom implementation of the DataTable is used instead.

DataTable table = new DataTable();
table.AsEnumerable().Select(x => x);

 

Follow on:

Let keyword introduces a new range variable in a LINQ query.This range variable stores the result of the sub expression.This variable can be used anywhere in the query ,after it is declared.

This helps keep many complex queries simple.

Using Let keyword in LINQ

Its used as:

sub expression1
let letVariable = query expression which returns a value
sub expression2 which uses the letVariable

In the following example we have declared a generic list of Students.Every student can have one or many subjects.

List<Student> students = new List<Student>
{
new Student{Name="John",Subjects=new List<Subject>{
new Subject{Name="Maths",Marks=90 },
new Subject{Name="English",Marks=70 }
}
},
new Student{Name="Mark",Subjects=new List<Subject>{
new Subject{Name="Maths",Marks=66 },
new Subject{Name="English",Marks=55 }
}
},
new Student{Name="Amit",Subjects=new List<Subject>{
new Subject{Name="Maths",Marks=71 },
new Subject{Name="English",Marks=87 }
}
},
new Student{Name="Laxman",Subjects=new List<Subject>{
new Subject{Name="Maths",Marks=54 },
new Subject{Name="English",Marks=67 }
}
},
};

Now to find the list of all students who scored more than average score in Maths we will have to write complex linq query or use multiple queries.By using the Let clause we can use a single query as:

var result = from student in students
let total = students.Select(x =>
x.Subjects.Where(y => y.Name == "Maths").Select(y => y.Marks).FirstOrDefault()
).Cast<int>().Sum()
let average = total / students.Count()
let markInMaths = (from subject in student.Subjects
where subject.Name == "Maths"
select subject.Marks).FirstOrDefault()
where markInMaths > average
select new { student.Name, markInMaths, average };

In the above query 2 LINQ let clauses are used markInMaths and average.These variables stores the marks in maths for a specific student and average of marks in Maths respectively.

We are using these 2 let variables in the where clause of the outermost query to filter the students

where markInMaths > average

the where clause will filter the list of students who have scored more than average in Maths.

Finally we can print the list of students:

foreach(var student in result)
{
Console.WriteLine("{0},{1},{2}", student.Name, student.markInMaths, student.average);
}

The above statement will print the values for students John and Amit as they have above average marks in Maths.

Using Let keyword in LINQ helps manage complexity of Linq queries and allows to implement complex scenarios.

Follow on:

LINQ allows us to query data in a application using a .NET language such as C# similar to how SQL is used for querying databases.

Introduction to LINQ queries

Linq queries are composed using the query operators.There are many query operators defined in the System.Linq namespace as extension methods.These query operators are defined as extension methods on the Enumerable and Queryable classes.
Query operators defined on the Queryable class are used to execute queries on a remote data source such as SQL Server database whereas the query operators in the Enumerable class are used for executing queries for in memory collections.

The Standard Query Operators are divided into two groups one which operates on a sequence of type  IEnumerable<T> and other which operates on sequence of type  IQueryable<T>.

Some of the most useful query operators are:

from   this clause begins the query and must precede all other clauses such as select and where.the from clause specifies two important aspects of a query.It specifies the source and a range variable which represents each item in the source.It is used as:

var variable=from rangeVariable in Source

The type of the range variable is automatically inferred from the type of elements in the source.So if the source is of type List<Student> then the range variable will be of type Student.

Where   used for filtering a collection based on a condition.The condition which is specified as a predicate is evaluated  against each item in the collection.If the condition returns true then the item is included in the result collection.
The function passed to the Where operator is of the form Func<TSource, bool>.This is a predicate function as it returns a boolean value.A value of true or false is returned which depends on the item in the collection.It is used as:

where boolean condition

The boolean condition above is a predicate which is applied to each element in the data source.If the condition returns true then the element is included in the result.

Select   used for projecting a collection depending on the function passed in to each item of the collection.The function passed to the select operator is of the form Func<TSource, TResult> .This function accepts an input of a specific type and returns the transformed type.It is used as:

var result= from rangeVariable in DataSource
where boolean condition
select rangeVariable.Property

Example of LINQ query

We have declared a list of countries and each country has an associated continent.

List<Country> countries =new List<Country>(){
new Country{Name="Japan",Continent="Asia"},
new Country{Name="Singapore",Continent="Asia"},
new Country{Name="Australia",Continent="Australia"},
new Country{Name="USA",Continent="America"},
new Country{Name="New Zealand",Continent="Australia"}
};

To filter the country which belongs to Asia we can use the either of the two queries.

1)Following query uses the query operators to filter the list of asian countries

IEnumerable<string> asianCountries = countries.Where(item => item.Continent == "Asia").Select(item => item.Name);

2)We can write the above query using query expression syntax as:

IEnumerable<string> asianCountries= from country in countries
where country.Continent == "Asia"
select country.Name;

We have both the options available when writing a Linq query.If we write a Linq query using a query expression syntax then it gets translated to query operator syntax in the IL compiled code.
Once we have declared the query we can display the result by enumerating over the collection as:

foreach (var item in asianCountries)
{
Console.WriteLine(item);
}

This was just an introduction to LINQ.There are many useful operators which we can use when writing a LINQ query.Some of the useful operators are:

• GroupBy For grouping elements
• Join For joining two data sources
• SingleOrDefault Returns a single element satisfying a condition
• Average Returns average value of a collection of values
• Count Returns the count of elements in a collection

These operators simplifies otherwise complex data access operations.

Follow on:

LINQ extension methods are used to perform queries on the lists and collections.We can use C# for the LINQ queries rather using any other language such as SQL.

We can Compare List elements in C# using LINQ ,by  using the extension methods.

To compare lists using LINQ there is a SequenceEqual extension method provided by the framework.If we have two arrays arr1 and arr2 declared as:

string[] arr1 = { "A", "B", "C" };
string[] arr2 = { "A", "B", "C" };

then we can compare the above arrays using the SequenceEqual() method as:

bool result = arr3.SequenceEqual(arr2);

The result variable will contain the value true as arr1 and arr2 contains the same elements.

Instead of arrays we can compare two lists using the SequenceEqual() method.So if there are two lists of strings declared as:

List<string> arr4 = new List<string>(){ "A", "B", "C" };
List<string> arr5 = new List<string>() { "A", "B", "C" };

then we can compare them as:

bool result = arr4.SequenceEqual(arr5);

result will still have the value true since the two lists are identical.Above we are comparing two generic lists of strings.

But there is an important point to consider:

When using SequenceEqual to compare two lists or arrays ,the items in the two collections should be the same in the same ordinal.For example if the first list contains the value “A” in the first position then the second list should also contain the value “A” in the first position.

Though to compare List elements in C# using LINQ the framework provides the SequenceEqual method.This method compares elements based on the ordinal.We can implement logic to compare elements independent of the ordinals.

So if we now have two arrays with the same values but in different positions then SequenceEqual will return the value false.

string[] arr2 = { "A", "B", "C" };
string[] arr3 = { "B", "C", "A"};
bool result = arr2.SequenceEqual(arr3);

The result of the above comparison will return false.

So as evident we need to use some different approach to compare the List elements in C# using LINQ.

To compare collections which may contain same values but in different positions we can use custom logic.Here we will use the LINQ extension methods for comparing the two collections.

If two collections contains the same elements then the following two points will be true:

• The size of two collections will be same
• Each collection will contain the element of the other collection

We can implement logic to verify these using LINQ as:

private static bool ComapreLists(string[] arr2, string[] arr3)
{
bool _areEqual = false;
if (arr3.Length == arr2.Length)
{
var filteredSequence = arr3.Where(x => arr2.Contains(x));
if (filteredSequence.Count() == arr3.Length)
{
_areEqual = true;
}
}
return _areEqual;
}

Here we are checking if arr2 contains every element of arr3 and if the size of two collections is same.We can call the above method as:

string[] arr2 = { "A", "B", "C" };
string[] arr3 = { "B", "C", "A"};
bool result=ComapreLists(arr2, arr3);

CompareLists method above will return true if the two passed array arguments contains the same elements.

So we can easily implement the method to Compare List elements in C# using LINQ.If we don’t use LINQ then our logic will be more complicated.

Follow on:

LINQ is a powerful language feature that allows us to query different data sources using the programming language syntax. We can use C# to query data sources whether that is Oracle,SQL Server or XML.The query we use will be same irrespective of the data source.

While working with ADO.NET a common requirement is to query the data tables for matching data. Without LINQ we need to write complex logic to iterate over the entire table and retrieve the required data. Using LINQ we can easily query the table for the appropriate data.

We can use LINQ for querying data table ,  Query DataTable using LINQ in C#.

One common requirement when working with data is to join two tables. For example, one table may contain a list of the products while another table may contain details about the products. Though we can implement this logic very easily in SQL in the case of data tables this could mean writing lots of code.

We can use LINQ queries to implement this scenario.We can easily join two tables and retrieve the data from them using a relatively simple LINQ query.

We are creating two tables a product table and a details table.Product table contains the list of products while the details table contains the details about the products.

We are using the Products class to create and populate the tables.Though the following code populates the table with some random data ,mostly we will use the data returned from the database to populate the tables.
class Products
{
//table of products
private DataTable _productsTable;
public DataTable ProductsTable
{
get
{
_productsTable = new DataTable();
_productsTable.Columns.Add("Product", typeof(string));
_productsTable.Columns.Add("Id", typeof(int));
PopulateProductsTable(_productsTable);
return _productsTable;
}
}
//table of categories
private DataTable _detailsTable;
public DataTable DetailsTable
{
get
{
_detailsTable = new DataTable();
_detailsTable.Columns.Add("Price", typeof(decimal));
_detailsTable.Columns.Add("Availability", typeof(bool));
_detailsTable.Columns.Add("Id", typeof(int));
PopulateDetailsTable(_detailsTable);
return _detailsTable;
}
}
public static DataTable PopulateProductsTable(DataTable datatable)
{
datatable.Rows.Add("laptop", 1);
datatable.Rows.Add("desktop", 2);
datatable.Rows.Add("mobile phone", 3);
datatable.Rows.Add("mp3 player", 4);
return datatable;
}
public static DataTable PopulateDetailsTable(DataTable datatable)
{
datatable.Rows.Add(500, true, 1);
datatable.Rows.Add(300, true, 2);
datatable.Rows.Add(100, false, 3);
datatable.Rows.Add(50, true, 4);
return datatable;
}
}

We can join the above table using the following LINQ query


Products obj = new Products();
DataTable products = obj.ProductsTable;
DataTable details = obj.DetailsTable;
var items= (from p in products.AsEnumerable()
join t in details.AsEnumerable()
on p.Field<int>("Id") equals t.Field<int>("Id")
where p.Field<int>("Id")==1
select new
{
ProductName = p.Field<string>("Product"),
}).ToList();
/ var dataRow=datatable.AsEnumerable().Where(x => x.Field<int>("Id") == 2).FirstOrDefault();
foreach(var item in items)
{
var name=item.ProductName;
}
The above query will return the product with id 1 as the tables are joined with the id column value “1”.As the product with id “1” is laptop so the items collection contains a single product.

Similar to SQL join the two tables should have a common column such as the id column in the above query.If you have used SQL before you will realize that the join query resembles the sql join statement.

Follow on: