Appendix L

LINQ

This appendix provides syntax summaries for the most useful LINQ methods. For more detailed information, see Chapter 20, “LINQ.”

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 tells where the data comes from and defines the name by which it is known within the LINQ query.

From var1 In data_source1, var2 In data_source2, ...

Examples:

Dim query1 = From cust As Customer In all_customers
Dim query2 = From stu In students, score In TestScores

Usually, if you select data from multiple sources, you will want to use a Where clause to join the results from the sources.

Where

The Where clause applies filters to the records selected by the From clause. The syntax is:

Where conditions

Use comparison operators (>, <, =), logical operators (Not, Or, AndAlso), object methods (ToString, Length), and functions to build complex conditions.

For example, the following query selects student and test score data, matching students to their test scores:

Dim query = From stu In students, score In TestScores
    Where stu.StudentId = score.StudentId

The following example selects only students with last names starting with S:

Dim query = From stu In students, score In TestScores
    Where stu.StudentId = score.StudentId AndAlso
         stu.LastName.ToUpper.StartsWith("S")

Order By

The Order By clause makes a query sort the selected objects. For example, the following query selects students and their scores and orders the results by student last name followed by first name:

Dim query = From stu In students, score In TestScores
    Where stu.StudentId = score.StudentId
    Order By stu.LastName, stu.FirstName

Add the Descending keyword to sort a field in descending order. The following example orders the results by descending TestAverage value:

Dim query = From stu In students, score In TestScores
    Where stu.StudentId = score.StudentId
    Order By stu.TestAverage Descending

Select

The Select clause lists the fields that the query should select into its result. If this is omitted, the query selects all of the data in the data sources. You can add an alias to the result.

The following query selects customers’ FirstName and LastName values concatenated and gives the result the alias Name. It also selects the customers’ AccountBalance value and gives it the alias Balance.

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

You can pass values from the data sources into functions or constructors. For example, suppose the Person class has a constructor that takes first and last names as parameters. Then the following query returns a group of Person objects created from the selected customer data:

Dim query = From cust In all_customers
    Select New Person(cust.FirstName, cust.LastName)

Distinct

The Distinct keyword makes a query return only one copy of each result. The following example selects the distinct CustId values from the all_orders list:

Dim query = From ord In all_orders
    Select ord.CustId
    Distinct

Join

The Join keyword selects data from multiple data sources matching up corresponding fields. The following pseudo-code shows the Join command’s syntax:

From variable1 In datasource1
Join variable2 In datasource2
On variable1.field1 Equals variable2.field2

For example, the following query selects corresponding objects from the all_customers and all_orders lists:

Dim query = From cust As Customer In all_customers
    Join ord In all_orders
    On cust.CustId Equals ord.CustId

Note that you can get a similar result by using a Where clause. The following query selects a similar set of objects without using the Join keyword:

Dim query = From cust As Customer In all_customers, ord In all_orders
    Where cust.CustId = ord.CustId

Group By

The Group By clause lets a program select data from a flat, relational style format and build a hierarchical arrangement of objects. The following code shows the basic Group By syntax:

Group items By value Into groupname = Group

Here, items is a list of items whose properties you want selected into the group, value tells LINQ on what field to group objects, and groupname gives a name for the group.

The following query selects objects from the all_orders list. The Group By statement makes the query group orders that have the same CustId value.

Dim query1 = From ord In all_orders
    Order By ord.CustId, ord.OrderId
    Group ord By ord.CustId Into CustOrders = Group

The result is an IEnumerable that contains objects with two fields. The first field is the CustId value used to define a group (the value part in the syntax shown earlier). The second field is an IEnumerable named CustOrders that contains the group of order objects for each CustId value.

The following code shows how a program might display the results in a TreeView control:

Dim root1 As TreeNode = trvResults.Nodes.Add("Orders grouped by CustId")
For Each obj In query1
    ' Display the customer id.
    Dim cust_node As TreeNode = root1.Nodes.Add("Cust Id: " & obj.CustId)
 
    ' List this customer's orders.
    For Each ord In obj.CustOrders
        cust_node.Nodes.Add("OrderId: " & ord.OrderId &
            ", Date: " & ord.OrderDate)
    Next ord
Next obj

Another common type of query uses the Group By clause to apply some aggregate function to the items selected in a group. The following query selects order and order item objects, grouping each order’s items and displaying each order’s total price:

Dim query1 = From ord In all_orders, ord_item In all_order_items
    Order By ord.CustId, ord.OrderId
    Where ord.OrderId = ord_item.OrderId
    Group ord_item By ord Into
        TotalPrice = Sum(ord_item.Quantity * ord_item.UnitPrice),
        OrderItems = Group

The following code shows how a program might display the results in a TreeView control named trvResults:

Dim root1 As TreeNode = trvResults.Nodes.Add("Orders")
For Each obj In query1
    ' Display the customer id.
    Dim cust_node As TreeNode =
        root1.Nodes.Add("Order Id: " & obj.ord.OrderId &
            ", Total Price: " & FormatCurrency(obj.TotalPrice))
 
    ' List this customer's orders.
    For Each ord_item In obj.OrderItems
        cust_node.Nodes.Add(ord_item.Description & ": " &
            ord_item.Quantity & " @ " &
            FormatCurrency(ord_item.UnitPrice))
    Next ord_item
Next obj

Limiting Results

LINQ includes several keywords for limiting the results returned by a query.

The Take statement makes the query keep a specified number of results and discard the rest.

The Take While statement makes the query keep selected results as long as some condition holds and then discard the rest.

The Skip statement makes the query discard a specified number of results and keep the rest.

The Skip While statement makes the query discard selected results as long as some condition holds and then keep the rest.

The following code demonstrates each of these commands:

Dim q1 = From cust In all_customers Take 5
Dim q2 = From cust In all_customers Take While cust.FirstName.Contains("n")
Dim q3 = From cust In all_customers Skip 3
Dim q4 = From cust In all_customers Skip While cust.FirstName.Contains("n")

USING QUERY 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.

If the selected data has a well-understood data type, such as strings or objects from a known class, you can iterate through the result by using an explicitly typed looping variable. The following example selects customer names and then displays them. The looping variable is explicitly typed as a string.

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

If the returned data type is less well understood, you can use a looping variable with an inferred data type. The following code selects customers and their orders. It then loops through the results displaying order dates and numbers, together with the names of the customers who placed the orders. The looping variable obj has an inferred type.

Dim query = From cust In all_customers, ord In all_orders
    Where cust.CustId = ord.CustId
    Order By ord.OrderDate
 
For Each obj In query
    Debug.WriteLine(obj.ord.OrderDate & vbTab & obj.ord.OrderId &
        vbTab & obj.cust.Name)
Next obj

LINQ FUNCTIONS

The following table summarizes LINQ extension methods that are not available from Visual Basic LINQ query syntax.

FUNCTION	PURPOSE
Aggregate	Uses a function specified by the code to calculate a custom aggregate.
Concat	Concatenates two sequences into a new sequence.
Contains	Returns True if the result contains a specific value.
DefaultIfEmpty	Returns the query’s result or a default value if the query returns an empty result.
ElementAt	Returns an element at a specific position in the query’s result.
ElementAtOrDefault	Returns an element at a specific position in the query’s result or a default value if there is no such position.
Empty	Creates an empty IEnumerable.
Except	Returns the items in one IEnumerable that are not in a second IEnumerable.
First	Returns the first item in the query’s result.
FirstOrDefault	Returns the first item in the query’s result or a default value if the query contains no results.
Intersection	Returns the intersection of two IEnumerable objects.
Last	Returns the last item in the query’s result.
LastOrDefault	Returns the last item in the query’s result or a default value if the query contains no results.
Range	Creates an IEnumerable containing a range of integer values.
Repeat	Creates an IEnumerable containing a value repeated a specific number of times.
SequenceEqual	Returns True if two sequences are identical.
Single	Returns the single item selected by the query.
SingleOrDefault	Returns the single item selected by the query or a default value if the query contains no results.
Union	Returns the union of two IEnumerable objects.

The following table summarizes LINQ data type conversion functions.

FUNCTION	PURPOSE
AsEnumerable	Converts the result to IEnumerable(Of T).
AsQueryable	Converts an IEnumerable to IQueryable.
OfType	Removes items that cannot be cast into a specific type.
ToArray	Places the results in an array.
ToDictionary	Places the results in a Dictionary.
ToList	Converts the result to List(Of T).
ToLookup	Places the results in a Lookup (one-to-many dictionary).

LINQ TO XML

LINQ provides methods to move data in and out of XML.

LINQ into XML

To select data into XML objects, use the special characters <%= and %> to indicate a “hole” within the XML literal. Inside the hole, place a LINQ query.

For example, the following code builds an XElement object that contains Customer XML elements for objects in the all_customers list:

Dim x_all As XElement = 
    <AllCustomers>
        <%= From cust In all_customers 
            Select New XElement("Customer", 
            New XAttribute("FirstName", cust.FirstName), 
            New XAttribute("LastName", cust.LastName), 
            New XText(cust.Balance.ToString("0.00"))) 
        %>
    </AllCustomers>

LINQ out of XML

XML classes such as XElement provide LINQ functions that allow you to use LINQ queries on them just as you can select data from IEnumerable objects.

The following code extracts the descendants of the x_all XElement object that have negative balances. It selects each XML element’s FirstName and LastName attributes, and balance (saved in the element’s value).

Dim select_all = From cust In x_all.Descendants("Customer")
    Where CDec(cust.Value) < 0
    Select FName = cust.Attribute("FirstName").Value,
           LName = cust.Attribute("LastName").Value,
           Balance = cust.Value

The following table summarizes LINQ methods supported by XElement.

FUNCTION	RETURNS
Ancestors	IEnumerable containing all ancestors of the element.
AncestorsAndSelf	IEnumerable containing this element followed by all ancestors of the element.
Attribute	The element’s attribute with a specific name.
Attributes	IEnumerable containing the element’s attributes.
Descendants	IEnumerable containing all descendants of the element.
DescendantsAndSelf	IEnumerable containing this element followed by all descendants of the element.
DescendantNodes	IEnumerable containing all descendant nodes of the element. These include all nodes such as XElement and XText.
DescendantNodesAndSelf	IEnumerable containing this element followed by all descendant nodes of the element. These include all nodes such as XElement and XText.
Element	The first child element with a specific name.
Elements	IEnumerable containing the immediate children of the element.
ElementsAfterSelf	IEnumerable containing the siblings of the element that come after this element.
ElementsBeforeSelf	IEnumerable containing the siblings of the element that come before this element.
Nodes	IEnumerable containing the nodes that are immediate children of the element. These include all nodes such as XElement and XText.
NodesAfterSelf	IEnumerable containing the sibling nodes of the element that come after this element.
NodesBeforeSelf	IEnumerable containing the sibling nodes of the element that come before this element.

The following table gives examples of shorthand expressions for node axes and their functional equivalents.

SHORTHAND	MEANING	EQUIVALENT
x...<Customer>	Descendants named Customer.	x.Descendants(“Customer”)
x.<Child>	An element named Child that is a child of this node.	x.Attributes(“Child”)
x.@<FirstName> Or: x.@FirstName	The value of the FirstName attribute.	x.Attributes(“FirstName”).Value

LINQ TO DATASET

LINQ to DataSet refers to methods provided by database objects that support LINQ queries.

The DataSet class itself doesn’t provide many LINQ features, but the DataTable objects that it holds do. The DataTable has an AsEnumerable method that converts the DataTable into an IEnumerable, which supports LINQ.

The following list summarizes the key differences between a DataTable query and a normal LINQ to Objects query:

• The code must use the DataTable object’s AsEnumerable method to make the object queryable.
• The code can access the fields in a DataRow as in stu!LastName or as in stu.Field(Of String)(“LastName”).
• If you want to display the results in a DataGrid control, use the query’s ToList method.

The following example shows a query that selects student data from the dtStudents DataTable where the LastName comes before D. It selects the students’ FirstName and LastName fields, and displays the result in a DataGrid control.

Dim before_d =
    From stu In dtStudents.AsEnumerable()
    Where stu!LastName < "D"
    Order By stu.Field(Of String)("LastName")
    Select First = stu!FirstName, Last = stu!LastName
 
dgStudentsBeforeD.DataSource = before_d.ToList

Method-Based Queries

LINQ query keywords including Where, Order By, and Select actually correspond to methods that take parameters giving the functions they should use to perform their tasks. For example, the Where method takes as a parameter the address of a function that returns True if an item should be selected in the query result.

In addition to using standard LINQ query syntax, you can use method-based queries to select data. The following example selects data from all_customers where the OwesMoney function returns True. The OrderByAmount function returns values that can be used to order the results and SelectFields returns an object that contains selected fields for a selected item.

Dim q2 = all_customers.
    Where(AddressOf OwesMoney).
    OrderBy(AddressOf OrderByAmount).
    Select(AddressOf SelectFields)

Instead of passing the address of a function to these methods, you can pass lambda functions. The following code returns a result similar to the preceding query but using lambda functions instead of addresses of functions:

Dim q3 = all_customers.
    Where(Function(c As Customer) c.AccountBalance < 0).
    OrderBy(Of Decimal)(Function(c As Customer) c.AccountBalance).
    Select(Of CustInfo)(
        Function(c As Customer, index As Integer)
            Return New CustInfo() With
                {.CustName = c.Name, .Balance = c.AccountBalance}
    )

PLINQ

Adding parallelism to LINQ is remarkably simple. First, add a reference to the System.Threading library to your program. Then add a call to the AsParallel to the enumerable object that you’re searching. For example, the following code uses AsParallel to select the even numbers from the array numbers:

Dim evens =
    From num In numbers.AsParallel()
    Where num Mod 2 = 0