LINQ Data Table Extensions

Legacy applications which is to be migrated to a new platform (WPF, Silverlight etc.,) requires leveraging the underlying business layer too. We have IQueryable interface implemented for DataTable in System.Data.DataSetExtensions. There are couple of extension providers present,

  • 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 a EnumerableRowCollection and returns a DataView.
  • AsDataView
    • Accepts a DataTable and returns a DataView.
  • AsEnumerable
    • Accepts a DataTable and returns a EnumerableRowCollection, which is used for querying with 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 get the query the required fields from the DataTable.

private static void SelectDataTable()
        {
            var dt = GetOrdersDataTable();
            var orders = dt.AsEnumerable().Select(o =>
                new
                {
                    OrderID = o.Field<int>("OrderID"),
                    CustomerID = o.Field<string>("CustomerID"),
                    EmployeeID = o.Field<int>("EmployeeID"),
                    OrderDate = o.Field<DateTime>("OrderDate"),
                    ShipCountry = o.Field<string>("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 mentioned functions. Check out the code below,

 

private static void SortDataTable()
{
    var dt = GetOrdersDataTable();
    var orders = dt.AsEnumerable().OrderBy(r => r.Field<string>("ShipCountry"));
    var result = orders.ThenBy(r => r.Field<string>("CustomerID")).Select(o =>
        new
        {
            OrderID = o.Field<int>("OrderID"),
            CustomerID = o.Field<string>("CustomerID"),
            EmployeeID = o.Field<int>("EmployeeID"),
            OrderDate = o.Field<DateTime>("OrderDate"),
            ShipCountry = o.Field<string>("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));
    }
}

Where

Filter operations require a predicate match to be passed. Check out the code below,
private static void WhereOperation()
{
    var dt = GetOrdersDataTable();
    var filteredOrders = dt.AsEnumerable().Where(o => o.Field<string>("ShipCountry") == "Brazil")
.Select(o =>
        new
        {
            OrderID = o.Field<int>("OrderID"),
            CustomerID = o.Field<string>("CustomerID"),
            EmployeeID = o.Field<int>("EmployeeID"),
            OrderDate = o.Field<DateTime>("OrderDate"),
            ShipCountry = o.Field<string>("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 translate our code to more typed collection and work 
with the legacy DataTable objects.
Note: The API documentation suggests that these API’s are used internally in the .NET FW and 
not intended to be used in our code directly :).
Hope this helps.
-Fahad 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: