Linq-2-DataSet

In the previous blog I looked into the Linq-2-Sql , this time its Linq-2-DataSet. In .Net 3.5 Linq-2-DataSet support is fully implemented in the assembly System.Data.DataSetExtensions.dll .

To take advantage of this we have to include this assembly into our project. Filling up dataset can be done by any method , most frequently SqlDataAdapter is used .
While querying DataSet with Linq , we query the DataRow collection in the DataTable. Like other datasources that we query through linq , in Linq-2-DataSet we actually enumerate over the collection of DataRow object, so we have all the member of DataRow available for query expression.

Lets assume that we have DataSet filled with the Customers table data objects. From this dataset we actually enumerate over the table Customers.
So the first step is to make this DataTable enumerable, there are extention methods that helps us to achive this - ToQueryable() / AsEnumerable().

var query = _dsCustomers.Tables[0].ToQueryable();
var query = _dsCustomers.Tables[0].AsEnumerable();

These methods convert the DataTable as the datasource for the Linq.Lets see an example.

var custRow = (from cust in _dsCustomers.Tables[0].ToQueryable()
where cust.Field<string>("CustName") = = "Amy"
select cust).SingleOrDefault();

Here we used the Field method to access the DataColumn in the DataRow of Customers table , we could have also used default accessor cust["CustName"].
But with the default column accessor we would have to cast the column value into appropriate datatype and check for the null if the column is nullable. The Field method as we can see is generic type, we do not have to cast it and it also supports the nullable types.

Lets look at more complex one :-

var query = from cust in _dsCustomers.Tables[0].AsEnumerable()
join ord in _dsCustomers.Tables[1].AsEnumerable()
on cust.Field<int>("CustiId") equals ord.Field<int>("Ord_CustId")
where ord.Field<string>("Ord_OrderItem") == "NetBook" &&
ord.Field<datetime>("OrderDate").Month == 8
select new
{
CustName = cust.Field<string>("CustName"),
OrderId = ord.Field<int> ("Ord_OrderNo"),
CustId = cust.Field<int> ("CustId"),
OrderItem = ord.Field<string> ("Ord_OrderItem")
};

If we have the relationship defined between the tables we can use it as :

ds.Relations.Add("OrderRelation",dsCustomers.Tables[0].Columns["Ord_CustId"], dsCustomers.Tables[1].Columns["CustId"]);

var query = from cust in _dsCustomers.Tables[0].AsEnumerable()
from ord in _dsCustomers.Tables[1].GetChildRows("OrderRelation")
where ord.Field<string>("Ord_OrderItem") == "NetBook" &&
ord.Field<datetime>("OrderDate").Month == 8
select new
{
CustName = cust.Field<string>("CustName"),
OrderId = ord.Field<int>("Ord_OrderNo"),
CustId = cust.Field<int> ("CustId"),
OrderItem = ord.Field<string> ("Ord_OrderItem")
};

In the above queries we reterived the filtered datarows in the varirable 'query' , now we can iterate through these rows or query them. Previously there was the method that allowed us to convert these anonymous type datarow collection in to datatable ( ToDataTable(), another one to load query data into datatable LoadSequence(), now removed ) , but now we only have CopyToDataTable() method that converts the strongly typed DataRow collection into datatable. Here we must know the type of datarow in the collection to convert them into datatable.

var query = from cust in _dsCustomers.Tables[0].AsEnumerable()
where cust.Field<string>("CustName").StartsWith("A")
select cust;

DataTable newDataTable = query.CopyToDataTable()

We can also convert the query to a DataView and bind that to any control of WPF (ListBox, WpfToolKit Grid etc..). Here also the type of the DataRow should be known , as the method AsDataView() is generic method same as CopyToDataTable().

dataGrid1.DataSource = query.AsDataView() .

We can use other extension methods to compute the datarow collection. We have reterived our rows from the above linq queries in 'query' vairable , now lets perform some of computation on these rows. Almost all the computation extension method takes a delegate as a parameter to perform computaion on. Lets start with Max method.

int lowestRank = query.Where(r => r.CustName.StartsWith("A")).Max( r => r.CustRank);

Sum for a particular Datacolumn.

int sumTotalSalary = query.Where(r => r.DeptId == 10).Sum(r => r.CustSalary);

Average :-

double averageSalary = query.Where(r => r.DeptId == 10).Average(r => r.CustSalary);

GroupBy:-

var grpRows = query.GroupBy(r => r.DeptId);

OrderBy:-

var grpRows = query.OrderBy(r => r.DeptId);

We can futher explore all the posibilities that exists with Linq-2-DataSet.