Edit

Share via

Single-Table Queries (LINQ to DataSet)

  • Article

Language-Integrated Query (LINQ) queries work on data sources that implement the IEnumerable<T> interface or the IQueryable<T> interface. The DataTable class does not implement either interface, so you must call the AsEnumerable method if you want to use the DataTable as a source in the From clause of a LINQ query.

The following example gets all the online orders from the SalesOrderHeader table and outputs the order ID, order date, and order number to the console.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from order in orders.AsEnumerable()
    where order.Field<bool>("OnlineOrderFlag") == true
    select new
    {
        SalesOrderID = order.Field<int>("SalesOrderID"),
        OrderDate = order.Field<DateTime>("OrderDate"),
        SalesOrderNumber = order.Field<string>("SalesOrderNumber")
    };

foreach (var onlineOrder in query)
{
    Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
        onlineOrder.SalesOrderID,
        onlineOrder.OrderDate,
        onlineOrder.SalesOrderNumber);
}

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of Boolean)("OnlineOrderFlag") = True _
    Select New With { _
        .SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
        .OrderDate = order.Field(Of DateTime)("OrderDate"), _
        .SalesOrderNumber = order.Field(Of String)("SalesOrderNumber") _
     }

For Each onlineOrder In query
    Console.Write("Order ID: " & onlineOrder.SalesOrderID)
    Console.Write(" Order date: " & onlineOrder.OrderDate)
    Console.WriteLine(" Order number: " & onlineOrder.SalesOrderNumber)
Next

The local variable query is initialized with a query expression, which operates on one or more information sources by applying one or more query operators from either the standard query operators or, in the case of LINQ to DataSet, operators specific to the DataSet class. The query expression in the previous example uses two of the standard query operators: Where and Select.

The Where clause filters the sequence based on a condition, in this case that the OnlineOrderFlag is set to true. The Select operator allocates and returns an enumerable object that captures the arguments passed to the operator. In this above example, an anonymous type is created with three properties: SalesOrderID, OrderDate, and SalesOrderNumber. The values of these three properties are set to the values of the SalesOrderID, OrderDate, and SalesOrderNumber columns from the SalesOrderHeader table.

The foreach loop then enumerates the enumerable object returned by Select and yields the query results. Because query is an Enumerable type, which implements IEnumerable<T>, the evaluation of the query is deferred until the query variable is iterated over using the foreach loop. Deferred query evaluation allows queries to be kept as values that can be evaluated multiple times, each time yielding potentially different results.

The Field method provides access to the column values of a DataRow and the SetField (not shown in the previous example) sets column values in a DataRow. Both the Field method and SetField method handle nullable value types, so you do not have to explicitly check for null values. Both methods are generic methods, also, which means you do not have to cast the return type. You could use the pre-existing column accessor in DataRow (for example, o["OrderDate"]), but doing so would require you to cast the return object to the appropriate type. If the column is a nullable value type you have to check if the value is null by using the IsNull method. For more information, see Generic Field and SetField Methods.

Note that the data type specified in the generic parameter T of the Field method and SetField method must match the type of the underlying value or an InvalidCastException will be thrown. The specified column name must also match the name of a column in the DataSet or an ArgumentException will be thrown. In both cases, the exception is thrown at run time data enumeration when the query is executed.

See also