Query Dataset using LINQ

LINQ to DataSet makes it easier and faster to the query on the data cached in a DataSet. Specifically, LINQ to DataSet simplifies the query to allow developers to write queries in the programming language itself, rather than using a separate query language. This is especially useful for Visual Studio developers who can now take advantage of syntax checking at compile time, static typing, and IntelliSense support provided by Visual Studio in their offices.

LINQ to DataSet can also be used to query about the data that has been consolidated in one or more data sources. This enables many scenarios that require flexibility in the amount of data is represented and handled as consulting locally aggregated data caching and mid-level Web applications. In particular, generic reporting, analysis and business intelligence applications require this method of handling.

The LINQ to DataSet functionality is exposed primarily through extension methods and classes in DataRowExtensions DataTableExtensions. LINQ to DataSet is based and uses the existing ADO.NET 2.0 architecture and is not intended to replace ADO.NET 2.0 in the application code. Effective ADO.NET 2.0 code will still work on an application of LINQ to DataSet. The relationship of LINQ to DataSet in ADO.NET 2.0 and the data store is illustrated in the diagram below.

Querying DataSets

Formulating queries with LINQ to DataSet is similar to using Language-Integrated Query (LINQ) against other LINQ-enabled data sources. Remember, however, that when you use LINQ queries over a DataSet object you are querying an enumeration of DataRow objects, instead of an enumeration of a custom type. This means that you can use any of the members of the DataRow class in your LINQ queries. This lets you to create rich, complex queries.

As with other implementations of LINQ, you can create LINQ to DataSet queries in two different forms: query expression syntax and method-based query syntax. For more information about these two forms, see Getting Started with LINQ on MSDN. You can use query expression syntax or method-based query syntax to perform queries against single tables in a DataSet, against multiple tables in a DataSet, or against tables in a typed DataSet.

Single-Table Queries

 Language-Integrated Query (LINQ) queries work on data sources that implement the IEnumerable<T> interface or the IQueryable 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);

}

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.

Cross-Table Queries
This is done by using a join. A join is the association of objects in one data source with objects that share a common attribute in another data source, such as a product or contact ID. In object-oriented programming, relationships between objects are relatively easy to navigate because each object has a member that references another object. In external database tables, however, navigating relationships is not as straightforward. Database tables do not contain built-in relationships. In these cases, the join operation can be used to match elements from each source. For example, given two tables that contain product information and sales information, you could use a join operation to match sales information and products for the same sales order.

The Language-Integrated Query (LINQ) framework provides two join operators, Join 
and GroupJoin. These operators perform equi-joins: that is, joins that match two 
data sources only when their keys are equal. (By contrast, Transact-SQL supports 
join operators other than equals, such as the less than operator.) 



// Fill the DataSet.

DataSet ds = new DataSet();

ds.Locale = CultureInfo.InvariantCulture;

FillDataSet(ds);

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

DataTable details = ds.Tables["SalesOrderDetail"];

var query =

    from order in orders.AsEnumerable()

    join detail in details.AsEnumerable()

    on order.Field<int>("SalesOrderID") equals

        detail.Field<int>("SalesOrderID")

    where order.Field<bool>("OnlineOrderFlag") == true

    && order.Field<DateTime>("OrderDate").Month == 8

    select new

    {

        SalesOrderID =

            order.Field<int>("SalesOrderID"),

        SalesOrderDetailID =

            detail.Field<int>("SalesOrderDetailID"),

        OrderDate =

            order.Field<DateTime>("OrderDate"),

        ProductID =

            detail.Field<int>("ProductID")

    };

foreach (var order in query)

{

    Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",

        order.SalesOrderID,

        order.SalesOrderDetailID,

        order.OrderDate,

        order.ProductID);

}

I found great resources about this topic on MSDN here : LINQ to DataSet