Queries in LINQ to DataSet
A query is an expression that retrieves data from a data source. Queries are usually expressed in a specialized query language, such as SQL for relational databases and XQuery for XML. Therefore, developers have had to learn a new query language for each type of data source or data format that they query. Language-Integrated Query (LINQ) offers a simpler, consistent model for working with data across various kinds of data sources and formats. In a LINQ query, you always work with programming objects.
A LINQ query operation consists of three actions: obtain the data source or sources, create the query, and execute the query.
Data sources that implement the IEnumerable<T> generic interface can be queried through LINQ. Calling AsEnumerable on a DataTable returns an object which implements the generic IEnumerable<T> interface, which serves as the data source for LINQ to DataSet queries.
In the query, you specify exactly the information that you want to retrieve from the data source. A query can also specify how that information should be sorted, grouped, and shaped before it is returned. In LINQ, a query is stored in a variable. If the query is designed to return a sequence of values, the query variable itself must be a enumerable type. This query variable takes no action and returns no data; it only stores the query information. After you create a query you must execute that query to retrieve any data.
In a query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is called deferred execution; that is, query execution occurs some time after the query is constructed. This means that you can execute a query as often as you want to. This is useful when, for example, you have a database that is being updated by other applications. In your application, you can create a query to retrieve the latest information and repeatedly execute the query, returning the updated information every time.
In contrast to deferred queries, which return a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Count, Max, Average, and First. These execute immediately because the query results are required to calculate the singleton result. For example, in order to find the average of the query results the query must be executed so that the averaging function has input data to work with. You can also use the ToList or ToArray methods on a query to force immediate execution of a query that does not produce a singleton value. These techniques to force immediate execution can be useful when you want to cache the results of a query.
Queries
LINQ to DataSet queries can be formulated in two different syntaxes: query expression syntax and method-based query syntax.
Query Expression Syntax
Query expressions are a declarative query syntax. This syntax enables a developer to write queries in C# or Visual Basic in a format similar to SQL. By using query expression syntax, you can perform even complex filtering, ordering, and grouping operations on data sources with minimal code. For more information, see LINQ Query Expressions and Basic Query Operations (Visual Basic).
The .NET Framework common language runtime (CLR) cannot read the query expression syntax itself. Therefore, at compile time, query expressions are translated to something that the CLR does understand: method calls. These methods are referred to as the standard query operators. As a developer, you have the option of calling them directly by using method syntax, instead of using query syntax. For more information, see Query Syntax and Method Syntax in LINQ. For more information about the standard query operators, see Standard Query Operators Overview.
The following example uses Select to return all the rows from Product table and display the product names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable<DataRow> query =
from product in products.AsEnumerable()
select product;
Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field<string>("Name"));
}
' 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 products As DataTable = ds.Tables("Product")
Dim query = From product In products.AsEnumerable() _
Select product
Console.WriteLine("Product Names:")
For Each p In query
Console.WriteLine(p.Field(Of String)("Name"))
Next
Method-Based Query Syntax
The other way to formulate LINQ to DataSet queries is by using method-based queries. The method-based query syntax is a sequence of direct method calls to LINQ operator methods, passing lambda expressions as the parameters. For more information, see Lambda Expressions.
This example uses Select to return all the rows from Product and display the product names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query = products.AsEnumerable().
Select(product => new
{
ProductName = product.Field<string>("Name"),
ProductNumber = product.Field<string>("ProductNumber"),
Price = product.Field<decimal>("ListPrice")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}
' 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 products As DataTable = ds.Tables("Product")
Dim query = products.AsEnumerable() _
.Select(Function(product As DataRow) New With _
{ _
.ProductName = product.Field(Of String)("Name"), _
.ProductNumber = product.Field(Of String)("ProductNumber"), _
.Price = product.Field(Of Decimal)("ListPrice") _
})
Console.WriteLine("Product Info:")
For Each product In query
Console.Write("Product name: " & product.ProductName)
Console.Write("Product number: " & product.ProductNumber)
Console.WriteLine("List price: $ " & product.Price)
Next
Composing Queries
As mentioned earlier in this topic, the query variable itself only stores the query commands when the query is designed to return a sequence of values. If the query does not contain a method that will cause immediate execution, the actual execution of the query is deferred until you iterate over the query variable in a foreach or For Each loop. Deferred execution enables multiple queries to be combined or a query to be extended. When a query is extended, it is modified to include the new operations, and the eventual execution will reflect the changes. In the following example, the first query returns all the products. The second query extends the first by using Where to return all the products of size "L":
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable<DataRow> productsQuery =
from product in products.AsEnumerable()
select product;
IEnumerable<DataRow> largeProducts =
productsQuery.Where(p => p.Field<string>("Size") == "L");
Console.WriteLine("Products of size 'L':");
foreach (DataRow product in largeProducts)
{
Console.WriteLine(product.Field<string>("Name"));
}
' 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 products As DataTable = ds.Tables("Product")
Dim productsQuery = From product In products.AsEnumerable() _
Select product
Dim largeProducts = _
productsQuery.Where(Function(p) p.Field(Of String)("Size") = "L")
Console.WriteLine("Products of size 'L':")
For Each product In largeProducts
Console.WriteLine(product.Field(Of String)("Name"))
Next
After a query has been executed, no additional queries can be composed, and all subsequent queries will use the in-memory LINQ operators. Query execution will occur when you iterate over the query variable in a foreach or For Each statement, or by a call to one of the LINQ conversion operators that cause immediate execution. These operators include the following: ToList, ToArray, ToLookup, and ToDictionary.
In the following example, the first query returns all the products ordered by list price. The ToArray method is used to force immediate query execution:
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable<DataRow> query =
from product in products.AsEnumerable()
orderby product.Field<Decimal>("ListPrice") descending
select product;
// Force immediate execution of the query.
IEnumerable<DataRow> productsArray = query.ToArray();
Console.WriteLine("Every price from highest to lowest:");
foreach (DataRow prod in productsArray)
{
Console.WriteLine(prod.Field<Decimal>("ListPrice"));
}
' 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 products As DataTable = ds.Tables("Product")
Dim query = _
From product In products.AsEnumerable() _
Order By product.Field(Of Decimal)("ListPrice") Descending _
Select product
' Force immediate execution of the query.
Dim productsArray = query.ToArray()
Console.WriteLine("Every price From highest to lowest:")
For Each prod In productsArray
Console.WriteLine(prod.Field(Of Decimal)("ListPrice"))
Next