Standard Query Operators C# Help

Where, Order By Descending, and Select are only few of the query operators defined by LINQ. The LINQ query defines a declarative syntax for the most common operators. There are many more standard query operators available.

The following table lists the standard query operators defined by LINQ .

Capture

Capture

Following examples of using these operators.

Filtering

Have a look at some examples for a query.

With the where clause, you can combine multiple expressions; for example, get only the racers from Brazil and Austria who won more than 15 races. The result type of the expression passed to the where clause just needs to be of type bool:

Capture

Starting the program with this LlNQ query returns Niki Lauda, Nelson Piquet, and Ayrton Senna as shown:

Niki Lauda, Austria, Starts: 173, Wins: 25
Nelson Piquet, Brazil, Starts: 204, Wins: 23
Ayrton Senna, Brazil, Starts: 161, Wins: 41

Not all queries can be done with the LINQ query. Not all extension methods are mapped to LINQ query clauses. Advanced queries require using extension methods. To better understand complex queries with extension methods it’s good to see how simple queries are mapped. Using the extension methods Where () and Select () produces a query very similar to the LINQ query done before:

var racers = Formulal.GetChampions().
Where(r => r.Wins > 15 &&
(r.Country == ‘Brazil’ I I
r.Country == ‘Austria’)).
Select(r => r);

Filtering with Index

One example where you can’t use the LINQ query is an overload of the ,Where () method. With an overload of the Where () method you can a pass a second parameter that is the index. The index is a
counter for every result returned from the filter. You can use the index within the expression to do Some calculation based on the index. Here the index is used within the code that is called by the Where ( ) extension method to return only racers whose last name starts with A if the index is even:

Capture

All the racers with last names beginning with the letter A are Alberto Ascari, Mario Andretti, and Fernando Alonso. Because Mario Andretti is positioned within an index that is odd, he is not in
the result:

Alberto Ascari, Italy; starts: 32, wins: 10
Fernando Alsonso, Spain; starts: 105, wins: 19

Type Filtering 

For filtering based on a type you can use the Of Type () extension method. Here the array data contains both string and int objects. Using the extension method Of Type (), passing the string class to the generic parameter returns only the strings from the collection:

Capture

Running this code, the strings one, four, and five are displayed:

One
four
five

Compound from

If you need to do a filter based on a member of the object that itself is a sequence, you can use a compound from. The Racer class defines a property Cars where Cars is a string array. For a filter of all racers who were champions with a Ferrari, you can use the LINQ query as shown, The first from clause accesses the Racer objects returned from Formulal. GetChampions ( ) . The second from clause accesses the Cars property of the Racer class to return all cars-of type string. Next the cars are used with the where clause to filter only the racers who were champions with a Ferrari.

var ferrariDrivers = from r in
Formulal.GetChampions()
from c in r .Cars ..
where c == “Ferrari”
orderby r.LastName
select r.FirstName + ” “
+ r,LastName;

If you are curious about the result of this query, all Formula-l champions driving a Ferrari are:

Alberto Ascari
Juan Manuel Fangio
Mike Hawthorn
Phil Hill
Niki Lauda
Jody Scheckter
Michael Schumacher
John Surtees

The C# compiler converts a compound from clause with a LINQ query to the SelectMany () extension’ method. selectMan:( () can be used to iterate a sequence of a sequence. The overload of the SelectMany method-that is used with the example is shown here:

public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult> (
this IEnumerable<TSource> source,
Func<TSource,
IEnumerable<TCollect ion» c.ollectionSelector,
Func<TSource, TCollection, TResult>
resultSelector);

The first parameter is the implicit parameter that receives the sequence of Racer objects from the GetChampions () method. The second parameter is the collectionSelector delegate where ,the inner sequence is defined. With. the Lambda expression r => r. Cars the collection of cars should be returned. The third parameter is a delegate that is now invoked for every car and receives the Racer and Car objects. The Lambda expression creates an anonymous type with a Racer and a Car property. As a result of this SelectMany () method the hierarchy of racers and cars is flattened and a collection of new objects of an anonymous type for every car is returned.

This new collection is passed to the Where () method so that only the racers driving a Ferrari are filtered. Finally, the OrderBy () and Select () methods are invoked.

Capture

Resolving the generic SelectMany () method to the types that are used here, the types are resolved as follows. In this case the source is of type Racer, the filtered collection is a string array, and of course the name of the anonymous type that is returned is not known and shown here as T Result:

public static IEnumerable<TResult> SelectMany<Racer, string, TResult>
this IEnumerable<Racer> source,
Func<Racer, IEnumerable<string» collectionSelector,
Func<Racer, string, TResult> resultSelector);

Because the query was just converted from a LINQ query to extension methods, the result is the same as before.

Sorting

For sorting a sequence, the order by clause was used already. Let’s review the example from before with the order by descending clause. Here the racers are sorted based on the number of wins as specified by the key selector in a descending order:

var racers = from r in Formulal.GetChampions()
where r.Country == ‘Brazil’
orderby r.Wins descending
select r;

The orderby clause is resolved to the OrderBy () method, and the orderby descending clause is resolved to the OrderBy Descending () method:

var racers = Formulal.GetChampions().
Where(r => r.Country == ‘Brazil’).
OrderByDescending(r => r.Wins) .
Select(r => r);

The OrderBy () and OrderByDescending () methods return IOrderedEnumerable<TSource>. This interface derives from the interface IEnumerable<TSource> but contains an additional method
CreateOrderedEnumerable<TSource> (). This method is used for further ordering of the sequence. If two items are the same based on the key selector, ordering can continue with the ThenBy () and ThenByDescending () methods. These methods require an IOrderedEnumerable<TSource> to work on, but return this interface as well. So, you can add any number of ThenBy () and ThenByDe.s.cending () to sort the collection .

Using the LINQ query you just have to add all the different keys (with commas) for sorting to the orderby clause. Here the sort of all racers is done first based on the country, next on the last name, and
finally on the first name. The Take () extension method that is added to the result of the LINQ query is used to take just the first 10 results.

var racers (from r in
Formulal.GetChampions()
orderby r.Country, r.LastName,
r.FirstName
select r).Take(lO);

The sorted result is shown here:

Argentina: Fangio, Juan Manuel
Australia: Brabham, Jack
Australia: Jones, Alan
Austria: Lauda. Niki
Austria: Rindt, Jochen
Brazil: Fittipaldi, Emerson
Brazil: Piquet, Nelson
Brazil: Senna, Ayrton
Canada: Villeneuve, Jacques
Finland: Hakkinen, Mika

Doing the same with extension methods makes use of the OrderBy () and ThenBy.( ) methods:

var racers = Formulal.GetChampions().
OrderBy(r => r.Country).
ThenBy(r => r.LastName).
ThenBy(r => r.FirstName).
Take(lO) ;

Grouping

To group query results based on a key value, the group clause can be used. Now the Formula-l champions should be grouped by-the country, and the number of champions within a country should be listed. The clause group r by r. Country into g groups all the racers based on the Country property and defines a new identifier g that can be used later to access the group result information. The result from the group clause is ordered based on the extension method Count () that is applied on the group result, and if the count is the same the ordering is done based on the key, which is the country because this was the key used for grouping. The where clause filters the results based on groups that have at least two items, and the select clause creates an anonymous type with country and Count properties.

Capture

The result displays the collection of objects with the country and Count property:

UK 9
Brazil 3
Australia 2
Austria 2
Finland 2
Italy 2
USA 2

Doing the same with extension methods, the groupby clause is resolved to the GroupBy () method. What’s interesting with the declaration of the GroupBy () method is that it returns an enumeration of objects implementing the IGrouping interface. The IGrouping interface defines the Key property, so you can access the key of the group after defining the call to this method:

public static IEnumerable<IGrouping<TKey, TSource» GroupBy<TSource, TKey>(
this IEnumerable<TSource~ source,
Func<TSource, TKey> keySelector);

Thegroup r by r.Country into gclauseisresolvedtoGroupBy(r => r.Country) and returns the group sequence. The group sequence is first ordered by the OrderByDecending () method, then by the ThenBy () method. Next the Where () and Select () methods that you already know are invoked.

var countries = Formula1.GetChampions().
GroupBy(r => r.Country).
OrderByDescending(g => g.Count(».
ThenBy(g => g.Key) .
Where(g => g.Count() >= 2).
Select(g => new (. Country g.Key,
Count = g.Count() ));

Grouping with Nested Objects

If the grouped objects should contain nested sequences, you can do that by changing the anonymous type created by the select clause. With this example the returned countries should contain not only the properties for the name of the country and the number of racers, but also a sequence of the names of the racers. This sequence is assigned by using an inner from/in clause assigned to the Racers property. The inner from clause is using the group g to get all racers from the group, order them by the last name, and create a new string based on the first and last name.

Capture

The output now lists all champions from the specified countries;

UK 9
Jim Clark; Lewis Hamilton; Mike Hawthorn; Graham Hill; DamonHill; James Hunt;
Nigel Mansell; Jackie Stewart; John Surtees;
Brazil 3
Emerson Fittipaldi; Nelson Piquet; Ayrton Senna;
Australia 2

Jack_Brabham; Alan Jones;
Austria 2
Niki Lauda; Jochen Rindt;
Finland 2
Mika Hakkinen; Keke Rosberg;
Italy 2
Alberto Ascari; Nino Farina;
USA -2
Mario Andretti; Phil Hill;

Join

You can use the join clause to combine two sources based on specific criteria. But first, let’s get two lists that should be joined. With Formula-I there’s a drivers and a constructors championship. The drivers are returned from the method GetChampions ( ), and ‘the constructors are returned from the method GetConstructorChampions ( ) . Now it would be interesting to get a list by the year where every year lists the driver and the constructor champion.

For doing this, first two queries for the racers and the tearns are defined:

Capture

Using these two queries, a join is done based on the year of the driver champion and the year of the team champion with the clause join t in teams on r. Year equals t. Year. The select clause-defines a new anonymous type containing Year, Racer, and Team properties.

Capture

Of course you can also combine this to one LINQ query..but that’s a matter of taste:

Capture

The ootput displays data from the anonymous type:

Year                             Champion                            Constructor Title
2004                       Michael schumacher                      Ferrari
2005                        Fernando Alonso                          Renault
2006                        Fernando Alonso                          Renault
2007                        Kimi RiiikkonC!n                           Ferrari

Set Operations

The extension methods Distinct (), Union (), Intersect (), and Except () are set operations. Let’s create a sequence of Formula-l champions driving a Ferrari and another sequence of Formula-l champions driving a McLaren, and then let’s find out if any driver has been a champion driving both of these cars: Of course, that’s where the Intersect () extension method can help.

First get all champions driving a Ferrari. This is just using a simple LINQ query with a compound from to access the property Cars that’s returning a sequence of string objects.

var ferrariDrivers = from r in
Formulal.GetChampions()
from c in r.Cars
where c == ·Ferrari·
orderby r.LastName
select r;

Now the same query with a different parameter of the where clause would be needed to get all Mclaren racers. It’s not a good idea to write the same query another time. You have one option to create a method where you can pass the parameter car:

Capture

However, because the method wouldn’t be needed in other places, defining a variable of a delegate type to hold the LINQ query is a good approach. The variable racersByCar needs to be of a delegate type that requires a string parameter and returns IEnumerable<Racer>, similar to the method that Waf implemented before. For doing this several generic Func<> delegates are defined, so you do not need to declare your own delegate. A Lambda expression is assigned to the variable racersByCar. 1he left side of the Lambda expression defines a car variable of the type that is the first generic parameter of the Func delegate (a string). The right side defines the LINQ query that uses the parameter with the where clause.

Func<string. IEnumerable<Racer» racersByCar =
Car => from r in Formulal.GetChampions()
from c in r.Cars
where c == car
orderby r.LastName
select r;

Now you seen use the Intersect () extension method to get all racers that won the championship with a Ferrari and a Mclaren:

Console.WriteLine(“World champion with” +
“Ferrari and McLaren”);
foreach (vax racer in racersByCar(“Ferrari”):’
Intersect (racersByCar( “McLaren”)) )

{
Console.WriteLine(racer);
}

The result is just one racer, Niki Lauda:

World champion with Ferrari and McLaren
.Niki Lauda

Partitioning

Partitioning operations such as the extension methods Take () and Skip () can be used for easily paging, for example, to display 5 by 5 racers.

With the LINQ query shown here, the extension methods Skip () and Take () are added to the end of the query. The Skip () method first ignores a number of items calculated based on the page size and the actual page number; the Take () method then takes a number of items based on the page size:

Capture

Here is the output of the first three pages:

Page 0
Fernando Alonso
Mario Andretti
Alberto Ascari
Jack Brabham .
Jim Clark

Page 1
Juan Manuel Fangio
Nino Farina
Emerson Fittipaldi
Mika Hakkinen
Mike Hawthorn

Page 2
Phil Hill
Graham Hill
DamonHill
Denny Hulme
James Hunt

Paging can be extremely useful with Windows or Web applications showing the user only a part of the data.

An important behavior of this paging mechanism that you will notice: because the query is done with every page, changing the underlying data affects the results. New objects are shown as paging continues. Depending on your scenario this can be advantageous to your application. If this behavior is not what you need you can do the paging not over the original data source, but by using a cache that maps to the original data.

With the TakeWhile () and SkipWhile () extension methods you can also pass a predicate to take 01′ skip items based on the result of the predicate.

Aggregate Operators

The aggregate operators such as Count ( ), Sum( ), Min ( ), Max(), Average ( ), and Aggregate () do not return a sequence but a single value instead.

The Count () extension method returns the number of items in the collection. Here the Count () method is applied to the Years property of a Racer to filter the racers and return only the ones who won more than three championships:

Capture

The result is-shown here:

Michael Schumacher 7
Juan Manuel Fangio 5
Alain Prost 4

The Sum() method summarizes all numbers of a sequence and returns the result. Here, Sum() is used to calculate the sum of all race wins for a country. First the racers are grouped based on the country, then with the new anonymous type created the wins property is assigned to the sum of all wins from a single country:

var countries
(from c in from r in Formulal.GetChampions()
group r by r.Country into c select new Country = c.Key,
Wins = (from rl in c select rl.Wins) .Sum()
}
orderby c.Wins descending, c.Country select c) .Take(5);

foreach (var country in countries)
(
Console.WriteLine(” (0) (1)”, country. Country, country.Wins);

}

The most successful countries based on the race wins by the Formula-l champions are:

UK 138
Gennany 91
Brazil 78
France 51
Finland 40

The methods Min (), Max(), Average (), and Aggregate () are used in the same way as Count () and Sum( ) . Min() returns the minimum number of the values in the collection, and Max() returns the maximum number. Average () calculates the average number. With the Aggregate () method you can pass a Lambda expression that should do an aggregation with all the values.

Conversion

you’ve already seen that the query execution is deferred until the items are accessed. Using the query within an iteration, the query is executed. With conversion operator the query is executed immediately and you get the result in an array,  list, or a dictionary.

In this example the ToList () extension method is invoked to immediately execute the query and get the result into-a List<T>:

List<Racer> racers =
(from r in Fonnula1.GetChampions()
where r.Starts > 150
orderby r.Starts descending
select r) .ToList();
foreach (var racer in racers)
( .
Console.WriteLine(” (0) (O:S)”, racer);

}

It’s not that simple to just get the returned objects to the list. For example, for a fast access from a car to a racer within a collection class, you can use the new class LOOKup<TKey,TElement>.

The Dictionary<TKey, TValue> supports only a single value for a key. With the class tooKup<TKey TElement> from the namespace System. Linq you can have multiple values for a single key.

Using the compound from query, the sequence of racers and cars is flattened, and an anonymous type with the properties Car and Racer gets created. With the lookup that is returned, the,key should be of type string referencing the car, and the value should be of type Racer. To make this selection, you can pass a key and an element selector to one overload of the ToLookUp() method. The key selector references the Car property, and.the element selector references the Racer property.

Capture

The result of all “Williams” champions that are accessed using the indexer of the Lookup class is shown here:

Alan Jones
Keke Rosberg
Nigel Mansell
Alain Prost
Damon Hill
Jacques Villeneuve

In case you need to use a LINQ query over an untyped collection, for example the ArrayList, you can use the Cast () method. With the following sample an ArrayList collection that is based on the Object type is filled with Racer objects. To make it possible to define a strongly typed query, you can use the Cast () method:

Capture

Generation Operators

The generation operators Range (),Empty (),and Repeat () are not extension methods but normal static methods that return sequences. With LlNQ to objects, these methods are available with the Enumerable class.

Have you ever needed a range of numbers filled? Nothing is easier than with the Range () method. This, method receives-the start value with the first parameter and the number of items with the second . parameter:

Capture

Of course the result now looks like-this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

The Range ( ) method does not return a collection filled with the values as defined. This method does a defined query execution similar to the other methods. The method returns a Range Enumerator that just does a yield return with the values incremented.

You can combine the result with other extension methods to get a different result, for example using the Select () extension method:

var values = Enumerable. Range(1, 20).
Select(n => n * 3);

The Empty () method returns an iterator that does not return values. This can be used for parameters that require a collection where you can pass an empty collection.

The Repeat () method returns an iterates that returns the same value a specific number of times.

Posted on October 29, 2015 in Language Integrated Query

Share the Story

Back to Top