Chapter 25. LINQ to DataSets

For many years datasets have been the main data access technology for .NET developers, including Visual Basic programmers. Although the most recent versions of the .NET Framework introduced new object relational mapping technologies such as LINQ to SQL and ADO.NET Entity Framework, datasets are still very diffused especially in older applications. Because of this, Microsoft produced a LINQ standard provider that is specific for querying datasets: LINQ to DataSets. In this chapter you do not find information on manipulating datasets (refer to Chapter 21, “Introducing ADO.NET and DataSets”); instead you learn to query existing datasets using LINQ, and you become familiar with some peculiarities of this provider that are not available in the previous ones.

Querying Datasets with LINQ

LINQ to DataSets is the standard LINQ provider for querying datasets and is offered by the System.DataSet.DataSetExtensions namespace. Querying means that LINQ can only get information for datasets but not for manipulating them. If you need to add, remove, replace, or persist data versus datasets, you need to use old-fashioned techniques. Instead you can improve getting information using LINQ. You use datasets in Windows or web applications (except for Silverlight applications that do not support datasets). This chapter shows you code within a Console application. This is because we need a high level of abstraction so that all the code you see here can be used in both Windows and web applications. To complete the proposed examples, follow these steps:

• Create a new Console application and name the project LinqToDataSets.

• Establish a connection to the Northwind database via the Server Explorer window.

• Add a new dataset including the Customers, Orders, and Order Details tables.

When done, you need to manually write some code that populates the dataset. Usually such tasks are performed by Visual Studio if you generate a dataset within Windows Forms or WPF applications; however, in this case you need to do it. Write the following code that declares three TableAdapter objects and populates them with data coming from tables:

Imports LinqToDataSets.NorthwindDataSetTableAdapters

Module Module1

    Dim NwindDataSet As New NorthwindDataSet

    Dim NorthwindDataSetCustomersTableAdapter As CustomersTableAdapter _
        = New CustomersTableAdapter()
    Dim NorthwindDataSetOrdersTableAdapter As OrdersTableAdapter _
        = New OrdersTableAdapter()
    Dim NorthwindDataSetOrderDetailsTableAdapter As _
        Order_DetailsTableAdapter _
        = New Order_DetailsTableAdapter

    Sub Main()
        NorthwindDataSetCustomersTableAdapter.Fill(NwindDataSet.Customers)
        NorthwindDataSetOrdersTableAdapter.Fill(NwindDataSet.Orders)
        NorthwindDataSetOrderDetailsTableAdapter.
                     Fill(NwindDataSet.Order_Details)
    End Sub
End Module

Now you are ready to query your dataset with LINQ. LINQ syntax is the same as for other providers but with a few exceptions:

• LINQ queries DataTable objects, each representing a table in the database.

• LINQ to DataSets queries return EnumerableRowCollection(Of DataRow) instead of IEnumerable(Of T) (or IQueryable(Of T)), in which DataRow is the base class for strongly typed rows. The only exception is when you create anonymous types within queries. In such situations, queries return IEnumerable(Of Anonymous type).

You can use LINQ to retrieve a list of objects. For example, consider the following code that retrieves the list of orders for the specified customer:

Private Sub QueryOrders(ByVal CustomerID As String)

    Dim query = From ord In NwindDataSet.Orders
              Where ord.CustomerID = CustomerID
              Select ord
End Sub

As you can see, the syntax is the same as other providers. The query variable type is inferred by the compiler as EnumerableRowCollection(Of OrdersRow). There is a particular difference: The query result is not directly usable if you want to provide the ability of editing data. As it is, the query can only be presented; you need first to convert it into a DataView using the AsDataView extension method. The following code rewrites the preceding query, providing the ability of binding data to a control:

Dim query = (From ord In NwindDataSet.Orders
          Where ord.CustomerID = CustomerID
          Select ord).AsDataView

When you invoke AsDataView, you can bind a LINQ query to any user control that supports data binding, such as the Windows Forms BindingSource. Don’t invoke instead AsDataView if you simply need to get information without the need of manipulating data (for example, with a For..Each loop). You can use other query operators to get different information; the following code shows how you can get the number of orders made by the specified customer using the Aggregate clause:

Private Function QueryOrders(ByVal CustomerID As String) As Integer

    Dim ordersByCustomer = Aggregate ord In NwindDataSet.Orders
                           Where ord.CustomerID = CustomerID
                           Into Count()

    Return ordersByCustomer
End Function

Building Complex Queries with Anonymous Types

Same as you would do with other LINQ providers, you can build complex queries taking advantage of anonymous types in LINQ to DataSets. The following code shows how you can join information from the Orders and Order_Details tables retrieving information on order details for each order made by the given customer. Projection is accomplished by generating anonymous types:

Private Sub QueryOrderDetails(ByVal CustomerID As String)

    Dim query = From ord In NwindDataSet.Orders
                Where ord.CustomerID = CustomerID
                Join det In NwindDataSet.Order_Details
                On det.OrderID Equals ord.OrderID
                Select New With {.OrderID = ord.OrderID,
                                  .OrderDate = ord.OrderDate,
                                  .ShippedDate = ord.ShippedDate,
                                  .ShipCity = ord.ShipCity,
                                  .ProductID = det.ProductID,
                                  .Quantity = det.Quantity,
                                  .UnitPrice = det.UnitPrice}
End Sub

The query variable is of type IEnumerable(Of Anonymous type), which is different from normal queries. Remember that IEnumerable results cannot be edited; therefore, you are limited to presenting data through specific controls such as BindingSource. In LINQ to DataSets IEnumerable(Of Anonymous type), queries do not support AsDataView; therefore, you should consider creating a new DataTable, which is shown in the first example of the next section.

LINQ to DataSets’ Extension Methods

As a specific provider for datasets, LINQ to DataSets exposes some special extension methods generally required when converting from data rows collections into other objects. In this section you get an overview of methods and their usage.

Understanding CopyToDataTable

Tables from databases are represented within datasets via DataTable objects. You can create custom tables in code using a special extension method named CopyToDataTable, which can convert from EnumerableRowCollection (Of T) into a new DataTable. Imagine you want to create a subset of orders from the Orders table and that you want to create a new table with this subset of information. The following code accomplishes this:

Dim query = (From ord In NwindDataSet.Orders
            Where String.IsNullOrEmpty(ord.ShipCountry) = False
            Select ord).CopyToDataTable
query.TableName = "FilteredOrders"
NwindDataSet.Tables.Add(query)

The query retrieves only the orders where the ShipCountry property contains something and creates a new DataTable with this piece of information. The query variable’s type is DataTable; therefore, you can treat this new object as you would versus a classical table as demonstrated by assigning the TableName property and by the addition of the new table to the dataset. You can also create custom tables with more granularities by taking advantage of anonymous types. For example, imagine you want to create a table that wraps information from both the Orders and Order_Details tables. You need to manually create a new table, add columns, perform the query, and then add rows. The following code demonstrates this:

Private Function CreateCustomTable() As DataTable

    'Create a new table
    Dim customTable As New DataTable("Custom_orders")

    'Add columns
    With customTable
        With .Columns
            .Add("OrderID", GetType(Integer))
            .Add("Quantity", GetType(Short))
            .Add("UnitPrice", GetType(Decimal))
        End With
    End With

    'Retrieve data from different sources
    Dim query2 = From ord In NwindDataSet.Orders,
                      det In NwindDataSet.Order_Details
                 Where det.Quantity > 50
                 Select New With {.OrderID = ord.OrderID,
                                  .Quantity = det.Quantity,
                                  .UnitPrice = det.UnitPrice}

    'Add rows
    For Each item In query2
        customTable.Rows.Add(New Object() {item.OrderID,
                                            item.Quantity,
                                            item.UnitPrice})
    Next

    Return customTable
End Function

Notice how the new table is created in code and how columns are added. The Columns.Add method allows specifying the type (via the GetType keyword) for each column. We just want to retrieve the OrderID, Quantity, and UnitPrice information only for those products whose quantity is greater than 50. The LINQ query returns an IEnumerable(Of Anonymous types). Because of this, you need to iterate the collection and instantiate a new array of Object for each row, containing the specified information. When you have the new table populated, you can add it to the dataset and use it as any other table.

Understanding Field(Of T) and SetField(Of T)

The Field generic extension method allows retrieving a strongly typed form for all values from a given column within a table. Field receives as an argument the column name or the column index and then tries to convert values in a column into the specified type. Because of this, when using Field you should also predict some exceptions, such as InvalidCastException that can occur if the conversion fails, NullReferenceException if Field attempts to access a non-Nullable null value, and IndexOutOfRangeException if you pass an invalid index for the column. The following code retrieves all strongly typed versions of orders’ data:

Private Sub FieldDemo()

    Try
        Dim query = From ord In NwindDataSet.Orders
                    Where ord.Field(Of Date)("ShippedDate") < Date.Today
                    Select New With {
                               .OrderID = ord.
                                          Field(Of Integer)("OrderID"),
                               .OrderDate = ord.
                                          Field(Of Date)("OrderDate"),
                               .ShipCountry = ord.
                                          Field(Of String) _
                                          ("ShipCountry")
                                }
    Catch ex As InvalidCastException
        'Conversion failed

    Catch ex As NullReferenceException
        'Attempt to access to a non nullable
        'null object

    Catch ex As IndexOutOfRangeException
        'Wrong index

    Catch ex As Exception

    End Try
End Sub

In addition, a SetField method enables you to put a strongly typed value into the specified field, and that works like this:

ord.SetField(Of Date)("OrderDate",Date.Today)

Summary

Although Microsoft is making lots of investments in much more modern technologies such as ADO.NET Entity Framework, datasets are a data source that you can find in tons of applications. Because of this, the .NET Framework provides the LINQ to DataSets provider to enable the querying of datasets via the LINQ syntax. Datasets are particular; therefore, there are specific extension methods that you can use versus datasets, such as CopyToDataTable that generates a new DataTable from a LINQ query and Field that allows getting strongly typed information from columns. In this chapter you got an overview of how LINQ works over datasets, and how you can use retrieved information in your applications.