BASIC LINQ QUERY SYNTAX

The following text shows the typical syntax for a LINQ query:

From ... Where ... Order By ... Select ...

The following sections describe these four basic clauses. The sections after those describe some of the other most useful LINQ clauses.

From

The From clause is the only one that is required. It tells where the data comes from and defines the name by which it is known within the LINQ query. Its basic form is:

From query_variable In data_source

Here query_variable is a variable that you are declaring to manipulate the items selected from the data_source. This is similar to declaring a looping variable in a For or For Each statement.

You can supply a data type for query_variable if you know its type, although because of the anonymous types used by LINQ, it’s often easiest to let LINQ infer the data type automatically. For example, the following query explicitly indicates that the query variable cust is from the Customer class:

Dim query = From cust As Customer In all_customers

The From clause can include more than one query variable and data source. In that case, the query selects data from all of the data sources. For example, the following query selects objects from the all_customers and all_orders lists:

Dim query = From cust In all_customers, ord In all_orders

This query returns the cross-product of the objects in the two lists. In other words, for every object in the all_customers list, the query returns that object paired with every object in the all_orders list. If all_customers contains Ann, Bob, and Cindy, and all_orders contains orders numbered 1, 2, 3, then the following text shows the results returned by this query:

Ann         Order 1
Ann         Order 2
Ann         Order 3
Bob         Order 1
Bob         Order 2
Bob         Order 3
Cindy       Order 1
Cindy       Order 2
Cindy       Order 3

Usually, you will want to use a Where clause to join the objects selected from the two lists. For example, if customers and orders are related by a common CustomerId property, you might use the following query to select customers together with their corresponding orders rather than all orders:

Dim query = From cust In all_customers, ord In all_orders
    Where cut.CustomerId = ord.CustomerId

If Ann, Bob, and Cindy have CustomerId values 1, 2, 3, and the three orders have the corresponding CustomerId values, the preceding query would return the following results:

Ann         Order 1
Bob         Order 2
Cindy       Order 3

Where

The Where clause applies filters to the records selected by the From clause. It can include tests involving the objects selected and properties of those objects. The last example in the preceding section shows a particularly useful kind of query that joins objects from two data sources that are related by common property values. Although the Where clause is often used for simple joins, it can also execute functions on the selected objects and their properties.

For example, suppose the GoodCustomer class inherits from Customer, a class that has AccountBalance and PaymentIsLate properties. Also suppose the all_customers list contains Customer and GoodCustomer objects.

The OwesALot function defined in the following code returns True if a Customer owes more than $50. The query that follows selects objects from all_customers where the object is not a GoodCustomer and has a PaymentIsLate property of True and for which function OwesALot returns True.

Private Function OwesALot(ByVal cust As Customer) As Boolean
    Return cust.AccountBalance < -50
End Function
 
Dim query = From cust In all_customers
    Where Not (TypeOf cust Is GoodCustomer)
        AndAlso cust.PaymentIsLate _
        AndAlso OwesALot(cust)

The Where clause can include just about any Boolean expression, usually involving the selected objects and their properties. As the preceding example shows, it can include Not, Is, AndAlso, and function calls. It can also include And, Or, OrElse, Mod, and Like.

Expressions can use any of the arithmetic, date, string, or other comparison operators. The following query selects Order objects from all_orderitems where the OrderDate property is after April 5, 2012:

Dim query = From ord In all_orders
    Where ord.OrderDate > #4/5/2012#

Order By

The Order By clause makes a query sort the objects selected according to one or more values. Usually the values are properties of the objects selected. For example, the following query selects Customer objects from the all_customers list and sorts them by their LastName and FirstName properties:

Dim query = From cust In all_customers
    Order By cust.LastName, cust.FirstName

In this example, customers are sorted first by last name. If two customers have the same last name, they are sorted by first name.

An Order By clause can also sort objects based on calculated values. For example, suppose some customers’ names are surrounded by parentheses. Because “(” comes alphabetically before letters, those customers would normally end up at the beginning of the sorted list. The following query uses a String class’s Replace method to remove parentheses from the values used in sorting so all names are positioned in the list as if they did not contain parentheses:

Dim query = From cust In all_customers
    Order By cust.LastName.Replace("(", "").Replace(")", ""),
            cust.FirstName.Replace("(", "").Replace(")", "")

Note that the values used for ordering results are not the values selected by the query. The two preceding queries do not specify what results they select so LINQ takes its default action and selects the Customer objects in the all_customers list. See the next section, “Select,” for information on determining the values that the query selects.

To arrange items in descending order, simply add the keyword Descending after an ordering expression. Each expression can have its own Descending keyword so you can arrange them independently.

Select

The Select clause lists the fields that the query should select into its result. This can be an entire record taken from a data source or it can be one or more fields taken from the data sources. It can include the results of functions and calculations on the fields. It can even include more complicated results such as the results of nested queries.

You can add an alias to any of the items that the query selects. This is particularly useful for calculated results.

The following query selects objects from all_customers. It gives the first selected field the alias Name. That field’s value is the customer’s first and last name separated by a space. The query also selects the customer’s AccountBalance property, giving it the alias Balance.

Dim query = From cust In all_customers
    Select Name = cust.FirstName & " " & cust.LastName,
        Balance = Cust.AccountBalance

The result of the query is an IEnumerable that contains objects of an anonymous type that holds two fields: Name and Balance. The following code shows how you might display the results:

For obj In query
    Debug.WriteLine(obj.Name & " " & FormatCurrency(obj.Balance))
Next obj

You can also use the New keyword to create objects of an anonymous type. The following query builds a result similar to the earlier query but uses New:

Dim query = From cust In all_customers
    Select New With
    {
        .Name = cust.FirstName & " " & cust.LastName,
        .Balance = Cust.AccountBalance
    }

This version emphasizes that you are creating new objects, but it is more verbose.

The earlier queries return objects of an anonymous type. If you like, you can define a type to hold the results and then create new objects of that type in the Select clause. For example, suppose the CustInfo class has Name and Balance properties. The following query selects the same data as the preceding query but this time saves the results in a new CustInfo object:

Dim query = From cust In all_customers
    Select New CustInfo With
    {
        .Name = cust.FirstName & " " & cust.LastName,
        .Balance = Cust.AccountBalance
    }

The result of this query contains CustInfo objects, not objects of an anonymous type. The following code shows how a program can use an explicitly typed looping variable to display these results:

For ci As CustInfo In query
    Debug.WriteLine(ci.Name & " " & FormatCurrency(ci.Balance))
Next ci

If the CustInfo class provides a constructor that takes a name and account balance as parameters, you can achieve a similar result by using the constructor instead of the With keyword. The following query provides a result similar to the preceding one:

Dim query = From cust In all_customers
    Select New CustInfo(
        cust.FirstName & " " & cust.LastName,
        cust.AccountBalance)

From all of these different kinds of examples, you can see the power of LINQ. You can also see the potential for confusion. The Select clause in particular can take a number of different forms and can return a complicated set of results.

The following example shows one of the more complicated queries that uses only basic LINQ syntax. It selects data from multiple sources, uses a common field to join them, adds a Where filter, uses multiple values to order the results, and returns the Customer and Order objects that meet its criteria.

Dim query = From cust In all_customers, ord In all_orders
    Where cust.CustId = ord.CustId AndAlso
        cust.AccountBalance < 0
    Order By cust.CustId, ord.OrderDate
    Select cust, ord

Note that the Select clause changes the scope of the variables involved in the query. Statements that come after the Select clause can only refer to items in that clause.

For example, the following query selects customer first and last names. The Order By clause comes after the Select clause so it can only refer to items included in the Select clause. This example orders the results by the LastName and FirstName fields picked by the Select clause.

Dim query = From cust In all_customers
    Select cust.FirstName, cust.LastName
    Order By LastName, FirstName

Because the original cust variable is not chosen by the Select clause, the Order By clause cannot refer to it.

Note also that if the Select clause gives a result an alias, then any later clause must refer to the alias. For example, the following query selects the customers’ last and first names concatenated into a field known by the alias FullName so the Order By clause must use the alias FullName:

Dim query = From cust In all_customers
    Select FullName = cust.LastName & ", " & cust.FirstName
    Order By FullName

Usually, it is easiest to place Order By and other clauses before the Select clause to avoid confusion.

Using LINQ Results

A LINQ query expression returns an IEnumerable containing the query’s results. A program can iterate through this result and process the items that it contains.

To determine what objects are contained in the IEnumerable result, you need to look carefully at the Select clause. If this clause chooses a simple value such as a string or integer, then the result contains those simple values.

For example, the following query selects customer first and last names concatenated into a single string. The result is a string, so the query’s IEnumerable result contains strings and the For Each loop treats them as strings.

Dim query = From cust In all_customers
    Select cust.FirstName & " " & cust.LastName
 
For Each cust_name As String In query
    Debug.WriteLine(cust_name)
Next cust_name

Often the Select clause chooses some sort of object. The following query selects the Customer objects contained in the all_customers list. The result contains Customer objects, so the code can explicitly type its looping variable and treat it as a Customer.

Dim query = From cust In all_customers
    Select cust
 
For Each cust As Customer In query
    Debug.WriteLine(cust.LastName & " owes " & cust.AccountBalance)
Next cust