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.

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 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(Of TSource) or ToArray(Of 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.

LINQ to DataSet queries can be formulated in two different syntaxes: query expression syntax and method-based query syntax.

==========================================================
Query Expression Syntax

By using query expression syntax, you can perform even complex filtering, ordering, and grouping operations on data sources with minimal code.
Query expression syntax is new in C# 3.0 and Visual Basic 2008.

// 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 method-based query syntax is a sequence of direct method calls to LINQ operator methods,
passing lambda expressions as the parameters

// 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.
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.
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"));
}

ref: http://msdn.microsoft.com/en-us/library/bb397947.aspx