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<TSource> or ToArray<TSource> 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. For more information about deferred and immediate query execution, see Getting Started with LINQ.
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 (C# Programming Guide) and Basic Query Operations (Visual Basic).
Query expression syntax is new in C# 3.0 and Visual Basic 2008. However, 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 LINQ Query Syntax versus Method Syntax (C#). For more information about how to use the standard query operators, see LINQ General Programming Guide.
The following example uses Select to return all the rows from Product table and display the product names.
' 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
// 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"));
}
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 (C# Programming Guide).
This example uses Select to return all the rows from Product and display the product names.
' 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
// 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);
}
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.
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
// 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"));
}
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<TSource>, ToArray<TSource>, ToLookup, and ToDictionary.
In the following example, the first query returns all the products ordered by list price. The ToArray<TSource> method is used to force immediate query execution:
' 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
// 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"));
}
See Also
Concepts
Querying DataSets (LINQ to DataSet)
Other Resources
Programming Guide (LINQ to DataSet)