Friday, July 9, 2010

CopyToDataTable: LINQ to DataSet

Linq has a built in CopyToDataTable() method to convert resultset of a LINQ query to DataTable. This works fine if the query returns DataRow type. But surprisingly they don't work if a LINQ query implements a JOIN.
For example, the resultset of following query can not be converted into a DataTable using CopyToDataTable() method.
var final = from s in sourceTable.AsEnumerable()
join d in destinationTable.AsEnumerable() on s.Field("Id1") equals d.Field("Id2")
select new { Name = d.Field("Name"), Zip = s.Field("Zip"), Error = d.Field("Error") };


After a bit of toiling I found that to achieve this it will require implementation of an extension method. It was described here:
http://blogs.msdn.com/b/aconrad/archive/2007/09/07/science-project.aspx

And also on MSDN

I was using the CopyToDataTable() method over other queries too and implementation of the extension methods as suggested above resulted in exceptions where the method was called earlier.

I figured it will be safe if I rename the extension methods to avoid ambiguity. And that also helped the earlier exception to go away.

No comments: