LINQ (Language Integrated Query) - Part 6

This is sixth part of the ‘LINQ’ series posts that I have started from here. In the last post we explored selecting records using LINQ and its internals. Now in this post we will be looking at filtering, ordering, grouping and joining using LINQ.

Previous Posts:

Filter

Filter is the most common query operation is to apply a filter in the form of a Boolean expression. The filter causes the query to return only those elements for which the expression is true. The result is produced by using the where clause. The filter in effect specifies which elements to exclude from the source sequence. Let’s look at sample query:

DataNorthwindDataContext NDC = new DataNorthwindDataContext();

var custQuery = from cust in NDC.Customers
                where cust.Country == "France"
                select cust;

foreach (var e in custQuery)
{
    Console.WriteLine("Country: " + e.Country + " || Address: " + e.Address + " || Phone: " + e.Phone);
}

Console.ReadKey();

In above query, I’m asking for only those records who’s ‘Country’ is ‘France’. And in the foreach loop, ‘Country’, ‘Address’ and ‘Phone’ separated by ‘||’ and the same in output.


In the same way, if you want to select records where ‘Country’ is ‘Framce’ and ‘ContactName’ starts with ‘A’, then use

var custQuery = from cust in NDC.Customers
                where cust.Country == "France" && cust.ContactName.StartsWith("a")
                select cust;

And, if you want to select records where ‘Country’ is ‘Framce’ or ‘ContactName’ starts with ‘A’, then use

var custQuery = from cust in NDC.Customers
                where cust.Country == "France" || cust.ContactName.StartsWith("a")
                select cust;

So, in both query, ‘&&’ is being used for ‘And’ and ‘||’ is being used for ‘Or’.

Now, ‘StartsWith’ is a LINQ level key that is equivalent to LIKE operator in SQL. You can see it in generated query here.


We will look more only such available ‘keys’ in coming post.

Order

The orderby clause will cause the elements in the returned sequence to be sorted according to the default comparer for the type being sorted. For example the following query can be extended to sort the results based on the ContactName property. Because ContactName is a string, the default comparer performs an alphabetical sort from A to Z.

var custQuery = from cust in NDC.Customers
                orderby cust.ContactName descending //orderby cust.ContactName ascending
                select cust;

Group

The group clause enables you to group your results based on a key that you specify. For example you could specify that the results should be grouped by the City.

var custQuery = from cust in NDC.Customers
                where cust.ContactName.StartsWith("a")
                group cust by cust.City;

When you end a query with a group clause, your results take the form of a list of lists. Each element in the list is an object that has a Key member and a list of elements that are grouped under that key. When you iterate over a query that produces a sequence of groups, you must use a nested foreach loop. The outer loop iterates over each group, and the inner loop iterates over each group's members.

foreach (var e in custQuery)
{
    int x = e.Key.Length;
    Console.WriteLine('\n');
    Console.WriteLine(e.Key);
    Console.WriteLine(Repeat('-', x));
               
    foreach (Customer c in e)
    {
        Console.WriteLine("Contact Name : " + c.ContactName);
    }
}

And the output will be organized as


If you must refer to the results of a group operation, you can use the ‘into’ keyword to create an identifier that can be queried further. The following query returns only those groups that contain more than two customers:

var custQuery = from cust in NDC.Customers
                group cust by cust.City into custGroup
                where custGroup.Count() > 2
                select custGroup;

And the foreach loop will be same.

foreach (var e in custQuery)
{
    int x = e.Key.Length;
    Console.WriteLine('\n');
    Console.WriteLine(e.Key);
    Console.WriteLine(Repeat('-', x));
               
    foreach (Customer c in e)
    {
        Console.WriteLine("Contact Name : " + c.ContactName);
    }
}

You will get following output.


Join

Join operations create associations between sequences that are not explicitly modeled in the data sources. For example you can perform a join to find all the customers and distributors who have the same location. In LINQ the join clause always works against object collections instead of database tables directly.

Question: What query we should write to select names from two different tables ‘Customer’ and ‘Employee’ depending upon matching city.

Answer: Query will be:

var custQuery = from cust in NDC.Customers
                join emp in NDC.Employees on cust.City equals emp.City
                select new { CityName = cust.City, CustomerName = cust.ContactName, EmployeeName = emp.FirstName };

And in foreach loop, will write

foreach (var e in custQuery)
{
    Console.WriteLine(e.CityName + " : " + e.CustomerName + ", " + e.EmployeeName);
}

Output:


We can use ‘Group by’ here to group the output.

I hope you will find it useful. Thanks for reading.

Comments

Popular posts from this blog