Making DataSets tolerable using LINQ to DataSet
Posted
Monday, February 18, 2008 10:01 AM
by
CoreyRoth
Unfortunately, most of us aren't working in a perfect world, so it is bound to happen that you run into a dataset or two. Whatever the reason (the developer was lazy, you're maintaining legacy code, someone didn't know any better, or you're just working with the SharePoint API), it would be nice if there was a way to make dealing with datasets easier. LINQ to DataSet does this by allowing you to perform queries or move the data easily into a domain object.
To work with LINQ to DataSet, an extension method called AsEnumerable() is tacked onto the DataTable class making it queryable by LINQ This extension method is provided by adding System.Data.DataSetExtensions.dll to your project. Then, it is just a matter of knowing the syntax to get individual columns of data. Using a generic xtension method called Field, we can get the value of a column with the appropriate type.
In this example, we are going to filter the datatable on the ItemDateTime field and return a new anonymous type. We are assuming the datatable contains columns IntColumn1, StringColumn1, and ItemDateTime.
var queryResults = from queryResult in myDataTable.AsEnumerable()
where queryResult.Field<DateTime>("ItemDateTime") < DateTime.Now
select new
{
intColumn1 = queryResult.Field<int>("IntColumn1"),
stringColumn1 = queryResult.Field<string>("StringColun1")
};
This really makes it easy to perform subqueries on things presented to you via datasets. What if you want to get away from that nasty dataset and work with a domain object? LINQ makes that pretty easy as well. Just assign the values into a domain object. Here is a simple domain object (note: that it uses automatic properties).
public class MyDomainObject
{
public DateTime ItemDateTime
{
get;
set;
}
public int IntColumn1
{
get;
set;
}
public string StringColumn1
{
get;
set;
}
}
Here is how you would assign it to the domain object.
var queryResults2 = from queryResult in myDataTable.AsEnumerable()
select new MyDomainObject
{
ItemDateTime = queryResult.Field<DateTime>("ItemDateTime"),
IntColumn1 = queryResult.Field<int>("IntColumn1"),
StringColumn1 = queryResult.Field<string>("StringColun1")
};
Who knows how efficient this is, but it is quite simple. If you are curious what type queryResults2 is in this, it is an EnumerableRowCollection<MyDomainObject>. If you want it as a list, you can use the ToList() method, there is also ToArray() and ToDictionary() if that's what you want. That's all on LINQ and datasets for today. Hopefully, this will make your next experience with them better.