Querying Dataset with LINQ
By : Kasim Wirama, MCDBA, MVP SQL Server
This article, I would like to show how to access ADO.NET dataset and datatable using LINQ. System.Data.Dataset is a in-memory representation of a set of data. It has relational objects such as table, relationship among tables and constraint. So it is good candidate to be accessible by LINQ.
Querying data between datacontext and ADO.NET is a bit different. I would say there is stuff besides LINQ to SQL, i.e LINQ to Dataset (ADO.NET). In LINQ to Dataset, datatable doesn’t implement IEnumerable<T>, so you need call method AsEnumerable on the datatable to wrap it. Each member inside enumerable is datarow type. To access the column inside datarow, you can use Field<T> instead of direct cast as you usually do in previous .NET 3.5/C# 3.0.
Here is the example:
class Class1{
private const string connstring = "database=northwind;trusted_connection=yes;";
private const string query = @"select orderid, orderdate, freight, shipname,
shipaddress, shipcity, shipcountry
from orders where customerid = @customerid;
select od.orderid, od.unitprice, od.quantity, od.discount,
p.[productName]
from [order details] od
inner join orders o on o.orderid = od.orderid
left join products p on p.productid = od.productid
where o.customerid = @customerid;"; public static void Main()
{
DataSet ds = LoadData();
DataTable orders = ds.Tables["orders"];
DataTable orderdetails = ds.Tables["orderdetails"];
var query1 = from o in orders.AsEnumerable()
join od in orderdetails.AsEnumerable()
on o.Field<int>("orderid") equals od.Field<int>("orderid")
into temptable
where o.Field<DateTime>("orderdate").Year >= 1998
orderby o.Field<DateTime>("orderdate") descending
select new { OrderID = o.Field<int>("orderid"),
OrderDate = o.Field<DateTime>("orderdate"),
Amount = temptable.Sum( od => od.Field<decimal>("unitprice") * od.Field<short>("quantity"))};
}
public static DataSet LoadData()
{
DataSet ds = new DataSet("custorders");
SqlDataAdapter da = new SqlDataAdapter(query, connstring);
da.SelectCommand.Parameters.AddWithValue("@customerid", "QUICK");
da.TableMappings.Add("Table", "orders");
da.TableMappings.Add("Table1", "orderdetails");
da.Fill(ds);
return ds;
}
}
If you define datable relationship among one another, you can call method GetChildRow from parent table to access child table, see this example below
class Class1{
private const string connstring = …
public static void Main()
{
DataSet ds = LoadData();
DataTable orders = ds.Tables["orders"];
DataTable orderdetails = ds.Tables["orderdetails"];
ds.Relations.Add("Order_Orderdetails", orders.Columns["orderid"], orderdetails.Columns["orderid"]);
var query3 = from o in orders.AsEnumerable()
where o.Field<DateTime>("orderdate").Year >= 1998
orderby o.Field<DateTime>("orderdate") descending
select new { OrderID = o.Field<int>("orderid"),
OrderDate = o.Field<DateTime>("orderdate"),
TotalAmount = o.GetChildRows("Order_Orderdetails").Sum
(x=>x.Field<decimal>("unitprice") * x.Field<short>("quantity"))
};
}
public static DataSet LoadData(){ …}
}
You can also modify value of a cell without existing mandatory to build query expression. For modification, you call method SetField<T>, as example shown below:
foreach (DataRow r in orderdetails.Rows)
{
decimal before = r.Field<decimal>("unitprice");
if (r.Field<decimal>("unitprice") < 10)
{
r.SetField<decimal>("unitprice", 10);
Console.WriteLine(r.Field<int>("orderid"));
Console.WriteLine("before : " + before);
Console.WriteLine("after : " + r.Field<decimal>("unitprice"));
Console.WriteLine();
}
}