Yes, I know, the DataSet isn’t the leading edge technology, but as the company where I work currently, there are several companies who use DataSet as data access technology.

Recently, I had to migrate the DataSet subclasses of the framework of my current employer to .NET 4.0 and add LINQ support. After this migration I made some unit tests and I found a surprising fact: If you using LINQ with a DataSet, then you have to handle deleted rows, which isn’t as you would expecting the behaviour.

First, here a little setup to run the tests:

TestDataSet ds = new TestDataSet();
TestDataSet.BECRow row = ds.BEC.NewBECRow();
row.BecId = Guid.NewGuid();
row.RunDat = DateTime.Now;
row.MachineName = Environment.MachineName;
row.CrtDat = DateTime.Now;
row.CrtUsr = "pw";
ds.BEC.AddBECRow(row);
ds.AcceptChanges();

The old way

In the ages before LINQ you get the preferred rows in the following way out of a typed or untyped DataSet:

string strFilter = string.Format("{0} = '{1}'", ds.BEC.CrtUsrColumn.ColumnName, "pw");
TestDataSet.BECRow[] rowsBEC = (TestDataSet.BECRow[]) ds.BEC.Select(strFilter);
Console.WriteLine("Row count: {0}", rowsBEC.Length);

As you expecting, the result here is one row. The ugly thing is the magic filter string and the casting for the variable rowsBEC. But if you’re using DataSets, you are used to such code.

The new way?

With LINQ, the code could look much nicer:

var query = from r in ds.BEC
			where r.CrtUsr == "pw"
			select r;
Console.WriteLine("Row count: {0}", query.Count());

That’s better and I thought that I had the job done. But I was wrong, seriously wrong. This code behaves differently because you will get also the deleted rows (when no where clause exists) or even worse get a DeletedRowInaccessible exception. This exception occurs, when you try to access a property on a deleted row, which will be the case here, if there are deleted rows in the DataTable.

Why? Well, if you look at the code of the Select method (for example with the tool Reflector) you will see, that there is an implicit filtering on the current rows (added, modified and unchanged).

The solutions

So, how could you fix this issue? There are at least two ways. First, the nearest one:

var query = from r in (TestDataSet.BECRow[]) ds.BEC.Select()
			where r.CrtUsr == "pw"
			select r;

This solution works, but you have to do the ugly cast. Well, because we have a framework and we subclassed every DataSet class, we could fix that by an override in the generic base class.

If you don’t have a framework, where you subclassed all the DataSet classes, then there is an alternative way:

var query = from r in ds.BEC
			 where r.RowState != DataRowState.Deleted && r.CrtUsr == "pw"
			 select r;

The order here is important, because you have to check first the RowState and after that you can add the real where clause. Also the "&&" is important, because it has to break if the row is deleted.

Conclusion

There are some pitfalls with DataSet and LINQ, because you wouldn’t expect that behaviour with deleted rows. It doesn’t make any sense at all, because I nearly never saw a reason why my business logic would like to deal with deleted rows. Also the behaviour is unnatural because you think of the DataSet as an in-memory-database, and on the database you have not to deal with deleted rows, unless you have to implement a trigger.