Thursday, January 14, 2010

Linq2DataSet Introduction

Lately I’ve been working for a client that recently moved an existing application to VS 2008 / .NET 3.5. The app uses some DataSets, so I’ve been playing to what I call Linq2DataSet (or Linq to DataSet).

Getting Started

To use Linq2DataSet will need to use the .NET 3.5 runtime and, in addition to the usual ADO.NET assemblies you will need to add a reference to System.Data.DataSetExtensions.dll.

Quick comparison to Linq2Sql

// Linq To Sql
int Linq2SqlGetCount()
     BigTableSQLDataContext data = new BigTableSQLDataContext();
     return (from x in data.Big_Tables select x).Count();
// Linq to DataSet
int Linq2DataSetGetCount()
     BigTableDS data = new BigTableDS();
     Big_TableTableAdapter da = new Big_TableTableAdapter();

     return (from x in data.Big_Table select x).Count();

The code isn’t that much different. There is a little more code to setup the DataSet than the SQL DataContext. No big deal.

There is a big gotcha. I ran both functions against a table with 20,000 records. The DataSet is fully loaded before anything can happen. In the code above, the Linq2Sql function runs in < 1 second and the Linq2DataSet version takes about a minute and a half.

In Linq2DataSet, all 20,000 are loaded into the DataSet before the Linq code is executed. In Linq2Sql, the engine executes the Linq and generates the SQL to get the data (probably something like SELECT COUNT(*) FROM Big_Table).

Yes, this looks bad, however if you already have the DataSet hanging around, Linq2DataSet may be a Mega-Cool way to solve your problems. For the record, I would not recommend Linq2DataSet for new “Green Field” projects, however, if you already have the DataSet lying around, Linq can make it easy to get that little bit of extra info you need. Linq2DataSets is also a way to get into Linq quickly then you can learn about Linq2Sql or the Entity Framework.

More to come…


Pavel said...

I was hoping to use Linq2DS precisely to avoid full loading of the DS. My hope was that "somehow" the filter on the tables queried would be used to lazy fetch only records that are the result of the query. That would be a real benefit of Linq2DS, otherwise I am not sure how it is better than say just casting the Rows collection to IQueryable<>. Thanks for at least saving me some time experimenting.

Jack Stephens said...

You are correct, Linq2DataSet provides no help loading or saving the data. You still have to use DataAdapters, DataReaders and same old ADO.NET tools from 2002.