LINQ Data Table Extensions

Legacy applications, which are to be migrated to a new platform (WPF, Silverlight, etc.) require leveraging the underlying business layers too. We have an IQueryable interface implemented for DataTable in System.Data.DataSetExtensions. There are a couple of extension providers present in the .NET Framework itself:

• DataTableExtensions
• EnumerableRowCollectionExtensions

Data Table Extensions

This provides a list of functions to query with the DataTable. Most of them are casting functions:

• AsDataView<T> — Accepts an EnumerableRowCollection and returns a DataView.
• AsDataView — Accepts a DataTable and returns a DataView.
• AsEnumerable — Accepts a DataTable and returns an EnumerableRowCollection, which is used for querying with the DataRow collection.

Enumerable Row Collection Extensions

The LINQ provider is implemented for the DataRowCollection that is present in the DataTable.Rows property. The following are the LINQ extensions you can work with:

• Select
• OrderBy
• OrderByDescending
• ThenBy
• ThenByDescending
• Where
• Cast

Select

Use the Select extension to provide the query with the required fields from the DataTable.

private static void SelectDataTable()
        {
            var dt = GetOrdersDataTable();
            var orders = dt.AsEnumerable().Select(o =>
                new
                {
                    OrderID = o.Field("OrderID"),
                    CustomerID = o.Field("CustomerID"),
                    EmployeeID = o.Field("EmployeeID"),
                    OrderDate = o.Field("OrderDate"),
                    ShipCountry = o.Field("ShipCountry")
                });
            foreach (var order in orders)
            {
                Console.WriteLine(string.Format("OrderID : {0} / CustomerID : {1} 
/ EmployeeID : {2} / OrderDate : {3} / ShipCountry : {4}", order.OrderID, order.CustomerID, 
order.EmployeeID, order.OrderDate, order.ShipCountry));
            }
        }

The Field<T> returns a strongly typed value from the underlying DataTable. You can also use SetField<T>

to set the field value thru a strongly typed object.

OrderBy/OrderByDescending/ThenBy /ThenByDescending

Sort operations can be performed by using the above functions. Check out the code below:

private static void SortDataTable()
{
    var dt = GetOrdersDataTable();
    var orders = dt.AsEnumerable().OrderBy(r => r.Field("ShipCountry"));
    var result = orders.ThenBy(r => r.Field("CustomerID")).Select(o =>
        new
        {
            OrderID = o.Field("OrderID"),
            CustomerID = o.Field("CustomerID"),
            EmployeeID = o.Field("EmployeeID"),
            OrderDate = o.Field("OrderDate"),
            ShipCountry = o.Field("ShipCountry")
        });
    foreach (var order in result)
    {
        Console.WriteLine(string.Format("OrderID : {0} / CustomerID : {1} / EmployeeID : {2} 
/ OrderDate : {3} / ShipCountry : {4}", order.OrderID, order.CustomerID, order.EmployeeID, 
order.OrderDate, order.ShipCountry));
    }
}

Filter operations require a predicate match to be passed. See the code below:

private static void WhereOperation()
{
    var dt = GetOrdersDataTable();
    var filteredOrders = dt.AsEnumerable().Where(o => o.Field("ShipCountry") 
== "Brazil")
.Select(o =>
        new
        {
            OrderID = o.Field("OrderID"),
            CustomerID = o.Field("CustomerID"),
            EmployeeID = o.Field("EmployeeID"),
            OrderDate = o.Field("OrderDate"),
            ShipCountry = o.Field("ShipCountry")
        });
    foreach (var order in filteredOrders)
    {
        Console.WriteLine(string.Format("OrderID : {0} / CustomerID : {1} / EmployeeID : {2} 
/ OrderDate : {3} / ShipCountry : {4}", order.OrderID, order.CustomerID, 
order.EmployeeID, order.OrderDate, order.ShipCountry));
    }
}

With these functions we can easily get expressions to work with legacy DataTable objects.

Note: The API documentation suggests that these APIs are used internally in the .NET Framework and are not intended to be used in our code directly :).

Hope this helps.