Get up to 80 % extra points for free! More info:

Lesson 8 - LINQ providers, anonymous types, grouping and sorting in C#

Previous
Next

In the previous lesson, LINQ in C# - Revolution in querying, we introduced the LINQ technology and created our first simple query. In today's tutorial, we're going to continue working with LINQ.

Providers

We mentioned that LINQ works using providers. These are the implementations of the LINQ methods for different platforms so that we're able to call LINQ queries on them. Let's go over a few basic LINQ providers and talk a little bit about each one of them.

Providers in .NET:

Some providers are delivered by Microsoft directly from the .NET framework. We'll mainly be interested in the following:

  • LINQ to Objects - A provider that allows us to run queries on core collections. We used it last time for querying on an array, we can also execute queries on lists and other .NET collections. LINQ to Objects works with data in computer memory.
  • LINQ to SQL/LINQ to Entities - A provider mapping LINQ statements on SQL queries, which allows us to work with the MS-SQL database. They also give us ORM (Object-relation mapping, more on that in future lessons). Working with the database is then fully object-oriented. We'll work with Linq to Entities a lot in future courses, LINQ to SQL is more so its predecessor. LINQ to Entities is a part of the Entity framework which is a rather extensive library for working with relational (database) data.
  • LINQ to XML - A provider that enables queries over XML files. As with LINQ to Entities, we'll be able to use the object-oriented approach.

Third-party providers

Since we're able to create our own providers, it would only make sense that there are many third-party solutions out there. However, some of them might not be well supported and it's a very good idea to stick with Microsoft's technologies instead.

  • DbLinq - The most widely used third-party provider which allows us to use LINQ technology on MySQL, SQLite, Oracle, PostgreSQL, Firebird and other heavily used database platforms.
  • LINQ to Excel - This provider enables us to work with Excel spreadsheets (Excel document) just like a database.
  • LINQ to JSON - A provider for querying JSON files.
  • LINQ to Amazo - LINQ to Amazon is often cited as an example of a third-party provider. Using LINQ queries, we can search through the books which are offered by this online store.

As you can see, there are a lot of providers. Once we know how to use LINQ, we'll be able to use it on almost anything. For the time being, we'll focus on LINQ to XML and LINQ to Entities.

Anonymous types

To be able to create more complex queries, more precisely, to only get the part of the result we're interested in, we'll use anonymous types. An anonymous type behaves like an instance of a class, but without having to declare a class. Let's try it out:

var anonym = new { Name="Anonymous", Surname="Very anonymous", Age="18" };
Console.WriteLine(anonym.Name);
Console.WriteLine(anonym.Surname);
Console.WriteLine(anonym.Age);

The output is as follows:

Console application
Anonymous
Very anonymous
18

We created an anonymous data type that has three attributes. We put values into them and put the entire structure in a variable of the var type. The resulting data type is very strange and we would barely be able to declare it without the var keyword.

What is the advantage to having such data types? In our queries, we can map anything we want using the select clause. Let's make a quick collection of cars and drivers. We'll make it so we always pick the license plates for red cars along with the full names of the drivers. The drivers have FirstName and LastName properties. Independently from that, the car properties are Color, Driver, and LicencePlate. The query would look something like this:

var query = from c in cars
            where (c.Color == "red")
            select new { c.LicencePlate, DriverName = c.Driver.FirstName + " " + c.Driver.LastName };

The query could return a complex result such as this one, so we had to create an anonymous data type for it. The query result will be a collection of elements, where each element will have the LicencePlate and DriverName properties. Notice how we only wrote c.LicencePlace once and DriverName = ... once. If we wanted to add a pre-existing property with the same name, we would simply name it as such. If we wanted a property with a different name, we would have to use PropertyName = value.

Attention: Do not misuse anonymous types, just like one shouldn't misuse the var keyword. Again, it's a functionality created for specific purposes, mainly for LINQ queries.** Do not use anonymous types instead of classes in your regular programs, it would reduce the quality of both the source code and the resulting application. Attributes of anonymous types are read-only, more precisely, they're **properties. We usually avoid anonymous types and design the application layer in a way that doesn't require us to write queries that are too complex. By using anonymous types, we get rid of relationships between entities, which may result in a more complex application and in many other problems. The best solution for the situation shown above would be to create a DriverName property in the car class and then select an entire car simply using select a. We'd extract the driver's name from it later easily as with the anonymous type. We'd also be working with the entire car, including all of its relations and not with an unidentified anonymous entity. However, anonymous types have their place in complex queries. Especially, single-purpose queries, and will often appear in the sample queries in our courses.

We now know everything needed to get into the syntax for advanced queries.

Sorting and grouping

Last time, we mentioned the from, where and select keywords. Let's go over two more basic operators, before going into LINQ syntax details.

orderby

In order to sort query results, we use the orderby operator:

var query = from u in users
            where (u.Age > 15)
            orderby u.FirstName
            select u.Age;

The output is as follows:

Console application
73
41
33
16
48
32
42
38
36
22
22
17
21
56
39
19
25
90
29
24

As you already know, C# will translate this query into methods:

var query = users.Where(u => u.Age > 15).OrderBy(u => u.FirstName).Select(u => u.Age);

All LINQ clauses have their own methods, I just included it as a reminder, I won't attach the "method" version of the query in any of the examples later on.

The default sorting direction is from the smallest values to the largest ones. Here, we're sorting alphabetically based on the users' names. If we wanted to sort in the opposite direction, we'd simply use the descending keyword:

var query = from u in users
            where (u.Age > 15)
            orderby u.FirstName descending
            select u.Age;

The output is as follows:

Console application
24
29
90
25
19
39
56
21
17
22
22
36
38
42
48
32
16
33
41
73

In the original example, we could have used the ascending keyword, but not necessarily. If we were to use a method, we'd use OrderByDescen­ding().

We are even able to sort by multiple criteria, separated by commas. The first one is the most important:

var query = from u in users
            where (u.Age > 15)
            orderby u.FirstName, u.LastName
            select u.Age;

The output is as follows:

Console application
73
41
33
16
32
48
42
38
36
22
22
17
21
56
39
19
25
90
29
24

This query will be translated by C# to:

var query = users.Where(u => u.Age > 15).OrderBy(u => u.FirstName).ThenBy(u => u.LastName).Select(u => u.Age);

Group (grouping)

We often use grouping in our queries. We simply group the elements according to certain criteria. Here's an example of how we would put users into age, groups:

var query = from u in users
            group u by u.Age into ageGroup
            select new { Age = ageGroup.Key, Users = ageGroup };

What have we done? We've grouped users by their age into ageGroup, which is a collection containing users of the same age. Meaning that we now have groups such as group 15, group 16, 17, etc. Later on, we'll be able to select what the group will look like. We'll take the age from the group's keys which include the age since we're grouping based on this criteria. The second property for each group will be a user collection, where we'll store the current group of users for a given age.

Let's move on to printing:

foreach (var group in query)
{
    Console.WriteLine(group.Age);
    foreach (var user in group.Users)
        Console.WriteLine(user.FirstName);
}

The output is as follows:

Console application
25
Sophie
48
James
12
Omar
24
Timothy
73
Bill
29
Sylvia
38
Jeffrey
21
Michael
13
Nora
10
Charles
56
Pedro
19
Ruby
39
Robert
42
Jasmin
2
Delpha
90
Stacey
22
Julian
Maggie
36
John
17
Maxwell
33
David
41
Cedric
32
James
16
Ella

We iterate over all of the groups, print the age, and then print out the users for each group.

In the next lesson, LINQ operators in C# .NET, we'll continue learning about LINQ syntax and run queries on multiple collections.


 

Previous article
LINQ in C# - Revolution in querying
All articles in this section
Collections and LINQ in C# .NET
Skip article
(not recommended)
LINQ operators in C# .NET
Article has been written for you by David Capka Hartinger
Avatar
User rating:
2 votes
The author is a programmer, who likes web technologies and being the lead/chief article writer at ICT.social. He shares his knowledge with the community and is always looking to improve. He believes that anyone can do what they set their mind to.
Unicorn university David learned IT at the Unicorn University - a prestigious college providing education on IT and economics.
Activities