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
Post a Comment