Introduction
LINQ is one of the new entrants in C# 3.0, introduced as part of .NET 3.5. As you are already aware, LINQ is a unified querying framework to retrieve data from various data sources such as relational databases, XML, plain objects and new LINQ providers are being written for other types of data sources as well.
LINQ to DataSets
.NET 3.5 comes with many LINQ providers out-of-the-box and one of them is LINQ to DataSets. Even though ADO.NET provides mechanisms in the form of methods and properties, LINQ to DataSets provides a easy and flexible option that resembles standard SQL in terms of language syntax to query and filter data in DataTable objects. Let us see an example of querying a DataTable using LINQ:
C#
DataTable dt = CustomerDataHelper.GetCustomers ();
var nycCustomer = from dr in dt.AsEnumerable ()
where (dr.Field<string> ("City") == "New York") && (dr.Field<int>("CustomerID") > 100)
select new
{
CustID = dr.Field<int> ("CustomerID"),
CustName = dr.Field<string> ("CustomerName")
};
foreach (var c in nycCustomer)
{
Console.WriteLine (String.Format("{0} - {1}", c.CustID, c.CustName));
}
One thing to notice from the above code is the way we access individual columns in the DataRow: we use Field<>()
, a generic extension method of the DataRow
class. Field acts as a wrapper around the DataRow columns to provide type-safe access to the underlying column values (remember, one of the advantages of LINQ is type-safe querying) and it provides a few other benefits as well. LINQ can also be used on typed DataTable
instances. In this case, we do not have to use the Field extension method to access the columns, because the typed DataTable itself takes care of column type-safety.
Of course, the same query can be written using standard query operators (method invocation syntax) also. For example, the following all-method-invocation syntax is equivalent to the above SQL- like syntax and produces identical results. In fact, SQL-like syntax is converted to the standard query operator syntax behind the scenes during compilation.
C#
nycCustomer = dt.AsEnumerable()
.Where(dr => dr.Field<string> ("City") == "New York")
.Select (dr => new {CustID = dr.Field<int> ("CustomerID"), CustName = dr.Field<string> ("CustomerName")});
Since ADO.NET (and DataTable hence) was introduced in .NET much before LINQ, support for LINQ for DataTable is incorporated via a new assembly System.Data.DataSetExtensions.dll. Hence, remember to add a reference to this assembly when writing code for LINQ to DataSets.
Links