Query Operators
From The Oxygene Language Wiki
This is a Language topic
Feel free to add your notes to this topic below.
This topic applies to using the Oxygene Language on the .NET/Mono Platform only.
Query Operators are largely based on the Standard Query Operators introduced by the Microsoft .NET Framework 3.5 and the types defined in the "System.Query" namespace. However, Delphi Prism's implementation of the language feature is only loosely coupled to these classes, and can work with class libraries that follow the same principles.
Query expressions use a syntax that is similar to the Structured Query Language (SQL) commonly used in database systems.
Restrictions
Where
The where operator filters a sequence based on a predicate.
Let MyCustomers be an existing sequence of customer objects. To filter the list to customers in a specific city, you can write:
var x := from c in MyCustomers where c.City = 'London';
The type of "c" is inferred from the context – since the compiler knows that "MyCustomers" is a "sequence of Customer", it can infer that "c" must be a "Customer". After executing this statement, variable "x" would now contain a newly created sequence, containing only those customers living in London.
Ordering
Order by
The order by operator orders a sequence according to one or more keys.
Let's order the customers in our list for further processing. The original list or database might contain customers in seemingly random order, or in the order they were added. You might want to print an alphabetical list of the customers, so expanding on the statement above, you now write:
var x := from c in MyCustomers where c.City = 'London' order by c.Name; for each cust in x do Console.WriteLine(cust.Name);
Of course you could have also dropped the "where" clause left over from before, and thus run the sorting over the entire list of customers:
var x := from c in MyCustomers order by c.Name;
The sort order can be adjusted by appending either the asc or desc keyword to the clause, with asc being implied by default. You can also order by multiple fields at the same time, by providing a list of expressions, such as
... order by c.Name asc, c.DateOfBirth desc;
Reverse
The reverse operator reverses the order of a sequence. This operator takes no additional parameters, and can be applied anywhere within a query. If it is followed by additional operators, these will be applied backwards to the reversed sequence.
The following code will return a list of all customers, in the opposite order of the original collection:
var list := from c in MyCustomers reverse;
Grouping
Group by
The group by operator groups the elements of a sequence. For example, you might want to print a list of customers grouped by city.
var list := from c in MyCustomers group by c.City; for each group in list do begin Console.WriteLine('City: '+ group.Key); for each cust in group do Console.WriteLine(cust.Name); end;
The group by returns a sequence of groupings. Each of these groupings represents one partition of data – customers living in London, customers living in Des Moines, and so on.
Every grouping contains a Key – the original value grouped by, in this case the city name – and also is a sequence of customers itself.
Group by can be followed by the into keyword to give a name to the new sequence of grouping, so it can be referenced further down in the query, such as:
var list := from c in MyCustomers group by c.City into g ...;
Projection
Select
The select operator allows you to specify a new set of data to be returned for each element in your sequence.
For example,
var list := from c in MyCustomers select (c.Name, c.City, DateTime.Now-c.DateOfBirth as Age);
This would return a sequence of a new (anonymous) class type containing only the Name, City and Age fields. The last field would be calculated by subtracting the date of birth from the current date, to obtain the age.
The above select statement will automatically create an anonymous class with the appropriate fields, and is basically a shorthand for "select new class (c.Name, c.City, Age := DateTime.Now-c.DateOfBirth)".
In addition to selecting a list of values, you can also select a single value to obtain a sequence of that type.
Drilling
From
Query Expressions allow you to drill into nested sequences, basically flattening out the hierarchy, by using nested from clauses. For example, the following query would return all orders placed by customers in Sydney:
var list := from c in MyCustomers where c.City = 'Sydney' from o in c.Orders order by o.OrderDate desc;
This query will first filter the customer list, using the already familiar "where" clause. From all the customers left, it will now create a new sequence combining all the orders (you will remember that Customer.Order is a nested sequence inside the Customer class, a detail table, in database terms). Finally, the entire list of all orders will be ordered by date.
Merging Data
Join
The most powerful and most complex query operator is join, which allows you to combine sources from two separate sequences into one result sequence.
For example, let's assume that you have a sequence called Countries that contains details about all the cities your customers are located in. You could use the following query to generate the appropriate greeting message for all your customers:
var greet := from cust in MyCustomers join cntry in Countries on cust.Country equals cntry.Name select cntry.Greeting + ' ' + cust.Name;
This query would then return a sequence of strings such as "Hello Paul", "Bonjour, Jean-Pierre", and so on.
If the join is not followed by a "select", its result will return anonymous types containing both of the joined sequences, similar to having specified "select cust, cntry". Join can be followed by the "into" keyword to give a name to the new sequence, so it can be referenced further down in the query, such as:
var l := from cust in MyCustomers join cntry in Countries on cust.Country equals cntry.Name into j where j.cust.City = 'Berlin' and j.cntry.Continent = 'Europe';
This query would return a list of all Customer/Country entries where the customer lives in a city named Berlin located in Europe (it will not return customers in Berlin, Wisconsin).
Intermediate Variables
With
The with operator can be used to introduce intermediate variables that can be reused later in the same query. It basically provides a shorthand for repeating expressions, but can also be used to avoid having a certain expression calculated more often than needed.
var list: from c in MyCustomers with Address := c.City+', '+c.Street where Address.Contains('Paris') order by Address;
The above query, for example, introduces a local variable Address that can later be referred to from both the "order" or the "where" clause (or any other clauses your query might have). The query will return customers living in Paris, and on Paris Street.
By using the with clause, the address will only be calculated once per customer (which might be relevant if the calculations involved were more expensive or time-consuming than in this example).
Partitioning
Take and Skip
The take and skip query operators allow you to work on a subset of a sequence by either taking or skipping a given amount of elements, respectively. The following query will return a sequence with the first 10 customers in the list (or less, if the original list is shorter):
var list := from c in MyCustomers take 10;
In contrast, the next query will skip the first ten customers, returning a query that contains customer 11 and following. If the original list contains only 10 customers or less, the resulting sequence will be empty, of course:
var list := from c in MyCustomers skip 10;
Eliminating Duplicates
Distinct
The distinct operator will eliminate any duplicates from a sequence, returning only unique elements. Distinct will keep the first occurrence of each unique element, and otherwise not change the sorting order of the sequence. For example, the following code will return 1, 3, 5, 7 and 9:
var list := from i in [1,3,5,5,7,9,5,1] distinct;
See Also
Area: Oxygene Language
Compiler version: Oxygene 5
Language Glossary — Keywords — Types — FAQ — How To
