vrijdag 13 april 2007

.NET 1.1: Problems when selecting rows from a DataTable using an aggregate filter

I came accross a rather weird error today when I was working in C# 1.1. I was trying to select the records that exist in a DataTable that had more then one child in a related datatable.

To make things a little bit more clear, I've reproduced this by creating a Typed Dataset which contains 2 tables from the Northwind database:

What I want to do, is to select the Categories for which there are multiple products classified into.
So, this is what I've done; I created an instance of this dataset, and populated it with some data:

NorthwindDS ds = new NorthwindDS();
NorthwindDS.CategoriesRow c1 = ds.Categories.NewCategoriesRow();
c1.CategoryID = 1;
c1.CategoryName = "Category 1";
ds.Categories.AddCategoriesRow (c1);

NorthwindDS.CategoriesRow c2 = ds.Categories.NewCategoriesRow();
c2.CategoryID = 2;
c2.CategoryName = "Category 2";
ds.Categories.AddCategoriesRow (c2);

NorthwindDS.ProductsRow p1 = ds.Products.NewProductsRow();
p1.ProductID = 1;
p1.CategoryID = 1;
p1.ProductName = "Product 1";
ds.Products.AddProductsRow (p1);

NorthwindDS.ProductsRow p2 = ds.Products.NewProductsRow();
p2.ProductID = 2;
p2.CategoryID = 1;
p2.ProductName = "Product 2";
ds.Products.AddProductsRow (p2);

// The following line is important to illustrate the problem :)

This is pretty simple, I've just populated the Dataset with 2 categories and 2 products, and the 2 products are both linked to Category 1.
Now, the Query I've talked about earlier should thus return 1 Category, specifically, Category 1, since there's more then one product related to this category.
I do this using the following code:

string filterExpression = "COUNT (Child.CategoryID) > 1";
DataRow[] dr = ds.Categories.Select (filterExpression);

foreach( NorthwindDS.Category cat in dr )
Console.WriteLine (cat.CategoryName);

This gives me the expected result, it writes 'Category 1' to the output-window.
However, suppose that I've populated this Dataset with data coming from the Database, and that I add a product - record to this Dataset after the Dataset has been populated...
I can simulate this by adding a ProductRow to the DataSet after the 'AcceptChanges()' call:

// previous lines which create the dataset, and add the categories
// and the first 2 products are left out for brevity

NorthwindDS.ProductsRow p3 = ds.Products.NewProductsRow();
p2.ProductID = 3;
p2.CategoryID = 1;
p2.ProductName = "Product 3";
ds.Products.AddProductsRow (p3);

filterExpression = "COUNT (Child.CategoryID) > 1";
DataRow[] dr = ds.Categories.Select (filterExpression);

foreach( NorthwindDS.Category cat in dr )
Console.WriteLine (cat.CategoryName);

Now, I would expect that this gives me the exact same result as before, however, I get an exception:

An unhandled exception of type 'System.Data.VersionNotFoundException' occurred in system.data.dll

Addition information: There is no original data to access.

Probably, .NET wants to access the original contents of my new ProductRow, but since it is a new one, it has no Original DataViewRowState ...
I've tried to work around this by specifying that the CurrentRows should be used:

ds.Categories.Select (filterExpression, 

But, to no avail. (Which didn't surprise me, because this line just says: return the Current DataViewRowState of the Categories and the problem seems to occur when the Product datarows are accessed).

So, this seems to be a bug in the .NET 1.1 (I've tried this in .NET 2.0 as well, and there, it works like a charm).

So, is there anybody who knows how to solve or work around this issue ?

1 opmerking:

Frederik Gheysels zei

Hmm, perhaps this can be fixed by this fix from Microsoft, but unfortunately, this is not a fix that is publically available, I'll have to contact MS for this. :(