Child Aggregate Filter Not Recognising Newly Added Child Rows (ADO.NET 2.0 Dataset)

The problem is simple: Astonishingly, it seems that in an ADO.NET 2.0
dataset, adding new rows to a child table does not seem to immediately
reflect in any aggregate filter placed upon the parent table.

For simplicity I have written some code below for a simple .NET 2.0
winforms app and a very basic stongly typed dataset 'MyDataset'.
'MyDataset' has the following schema (of two simple tables and one
relationship between them):

Table1: 'ParentTable'
ID (System.Int32, AutoIncrement, Primary Key)
Name (System.String)

Table2: 'ChildTable'
GUID (System.Guid, Primary Key)
ParentTableID (System.Int32)

Relationship1: 'FK_ParentTable_ChildTable'
PK: [ParentTable].[ID]
FK: [ChildTable].[ParentTableID]
Both Relation and Foreign Key Constraint checked with cascading Update
and Deletes.


The Form just needs two Labels (Label1, Label2) and a Button (Button1)
and then you can paste the following into the code file:

[code]

Public Class Form1
Dim m_oDS As New MyDataset

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
FillParent()
FillChild()

' ================================================
' This should return 2 but it actaully returns 0
' ================================================
Label1.Text = CountRowsWithChildren()

' ================================================
' Re-Merging/Filling the Parent Table seems to
' cause the filter to 'refresh' and return 2
' - But this is NOT a very elegent work around!
' ================================================
FillParent()
Label2.Text = CountRowsWithChildren()
End Sub

Public Sub FillParent()
Dim oTB As New MyDataset.ParentTableDataTable

oTB.AddParentTableRow("Row 1")
oTB.AddParentTableRow("Row 2")

oTB.AcceptChanges() ' Curiously, the filter works correctly if
you comment this line out (i.e. When the ParentRow also has a RowState
of 'Added')
m_oDS.ParentTable.Merge(oTB, True)
End Sub

Public Sub FillChild()
m_oDS.ChildTable.AddChildTableRow(Guid.NewGuid,
m_oDS.ParentTable.FindByID(0))
m_oDS.ChildTable.AddChildTableRow(Guid.NewGuid,
m_oDS.ParentTable.FindByID(1))
End Sub

Public Function CountRowsWithChildren()
Return
m_oDS.ParentTable.Select("COUNT(CHILD(FK_ParentTable_ChildTable).
[ParentTableID]) > 0").GetLength(0)
End Function

End Class

[/code]

After clicking the button, you should see that the first label shows 0
(i.e. the filter does not recognise the new child rows).

However, the second label correctly returns 2, but this is only
because table has been 'refreshed' with a merge.

Three questions:

1) Is this a bug or can anybody tell me if I am missing anything?

2) If it is a bug, is it a known bug with ADO.NET 2.0 (I have
previously had similar issues with 1.1, but I thought these were
supposed to have been fixed), or a is it a 'feature'?

3) Is there any elegent workaround to ensure the parent filter always
returns the correct result? ( i.e. a way to quickly refresh the
filter's/index's memory without resorting to repopulating the entire
parent table every time a child row is added).

Many Thanks,

Simon

Simon
Tue Mar 20 11:41:07 CDT 2007

After a bit more digging around I believe I have found the core of the
problem:

1) *IF* the Parent Row's Rowstate is 'UnChanged' then, any Aggregate
Functions referencing Child Rows actually work off the Original Field
Values (IMO this is rather unexpected behaviour). That is, EXCEPT
'Deleted' Rows, which are correctly ignored. As I've mentioned before,
Added Rows are also (IMO) incorrectly ignored, presumably as they have
no Original Field Values.

2) However, when the Parent Row's Rowstate is either 'Added' or
'Modified', then the Aggregate Functions correctly work off the
Modified/Current Child Field Values.

3) This behaviour seems to occur in both the .Compute and .Select
DataTable Methods, HOWEVER, Expression columns seem to always
correctly reference the Current Child Row values (irrespective of the
Parent Row's RowState).

This seems rather inconsistent behaviour to me, and I would expect
that the correct behaviour for these methods would be that the
Aggregate Functions should always work off the Current Child Values
(like it does for an Expression Column) Although I don't consider
using Expression Columns a work around to this, as they suffer from
some other very well documented problems!

I would appreciate any comments/feedback from anybody who maybe has
been working with this in the past, or even has a different view on
what the correct behaviour for these methods should be?

I've not seen any specific documentation highlighting this behaviour
(or reasons for it), and I'm surprised this exists in such a
fundamental component of the (.NET 2.0) dataset object, maybe somebody
could point me in the right direction for this?

Here is some more specific example code to highlight the problem
exactly:

[code]

Public Class Form1
'
==================================================================
' Form Requires:
' - 3 Labels (Label1, Label2, Label3)
' - 1 Button (Button1)Public Class Form1
'
==================================================================
' Form Requires:
' - 3 Labels (Label1, Label2, Label3)
' - 1 Button (Button1)
' - 1 UnTypes Dataset (SataSet1)
'
==================================================================

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
DataSet1 = SetupTableSchema()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
DataSet1.Clear()
FillParent()

'
==================================================================
' Use Select To Count The Number Of Parent Rows That
Correctly
' Match 3 Children In An Aggregate Filter
'
' Performing a This Should Return 3, as all Parents have 3
Children
' - However, It incorrectly returns 2
'
==================================================================
Label1.Text =
DataSet1.Tables("Parent").Select("COUNT(CHILD(FK_Parent_Child).
[RowState]) = 3").GetLength(0)


Dim iChildRowStates As Int32

'
==================================================================
' Now Use Compute To SUM The Parent RowState Values (BitMask)
For
' Rows Whose Child Aggregate Filter SUM Matches
iChildRowStates
'
==================================================================

'
==================================================================
' Which Parent Row Rowstates Match Correctly SUM All Three
Child
' Rowstates: Added, Modified, UnChanged? (Deletes Should Be
Ignored)
' - Expected Answer: 22 (UnChanged - 2, Added - 4, Modified
- 16)
' - Actual Answer: 20 (Added - 4, Modified - 16)
'
==================================================================
iChildRowStates = DataRowState.Unchanged Or
DataRowState.Modified Or DataRowState.Added
Label2.Text =
DataSet1.Tables("Parent").Compute("SUM([RowState])",
String.Format("SUM(CHILD(FK_Parent_Child).[RowState]) = {0}",
iChildRowStates)).ToString()


'
==================================================================
' UnChanged Parent Rows ONLY Filter The SUM Of UnChanged Child
Rows
' (And Original Values Of Modified Child Rows)
' - i.e. This following returns: 2 (UnChanged)
' - However, it's not all Original Values, as Deleted Row
Original
' Values Are Correctly Ignored
'
==================================================================
iChildRowStates = DataRowState.Unchanged
Label3.Text =
DataSet1.Tables("Parent").Compute("SUM([RowState])",
String.Format("SUM(CHILD(FK_Parent_Child).[RowState]) = {0}",
iChildRowStates)).ToString()
End Sub

Public Sub FillParent()
' ==================================================
' Add Three Rows To The Parent Table With Different
' Rowstates (Added, UnChanged, Modified)
' (Storing Their RowState Enum In A Separate Field)
' ==================================================
With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
DataRowState.Added})
FillChildren(.Item("ID"))
End With

With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
DataRowState.Unchanged})
FillChildren(.Item("ID"))
.AcceptChanges()
End With

With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
0})
FillChildren(.Item("ID"))
.AcceptChanges()
.Item("RowState") = DataRowState.Modified
End With
End Sub

Public Sub FillChildren(ByVal iParentID As Int32)
' ==================================================
' Add For Rows To The Child Table With All Four
' Rowstates (Added, UnChanged, Modified, Deleted)
' (Storing Their RowState Enum In A Separate Field)
' - This is done for each Parent Row
' ==================================================
DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Added})

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Unchanged})
.AcceptChanges()
End With

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, 0})
.AcceptChanges()
.Item("RowState") = DataRowState.Modified
End With

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Deleted})
.AcceptChanges()
.Delete()
End With
End Sub

Public Function SetupTableSchema() As DataSet
Dim ds As New DataSet

Dim oParent As DataTable = ds.Tables.Add("Parent")
Dim oChild As DataTable = ds.Tables.Add("Child")

' ==================================================
' Set Up Parent Table
' ==================================================
With oParent
With .Columns.Add("ID", GetType(System.Int32))
.AutoIncrement = True
.AllowDBNull = False
.ReadOnly = True
End With
.PrimaryKey = New DataColumn() {.Columns("ID")}

.Columns.Add("RowState", GetType(System.Int32))

End With

' ==================================================
' Set Up Child Table
' ==================================================
With oChild
With .Columns.Add("ID", GetType(System.Int32))
.AutoIncrement = True
.AllowDBNull = False
.ReadOnly = True
End With
.PrimaryKey = New DataColumn() {.Columns("ID")}

.Columns.Add("ParentID", GetType(System.Int32))
.Columns.Add("RowState", GetType(System.Int32))
End With

' ==================================================
' Create Relationship
' ==================================================
ds.Relations.Add("FK_Parent_Child", oParent.Columns("ID"),
oChild.Columns("ParentID"), True)

Return ds
End Function

End Class
' - 1 UnTypes Dataset (SataSet1)
'
==================================================================

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
DataSet1 = SetupTableSchema()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
DataSet1.Clear()
FillParent()

'
==================================================================
' Use Select To Count The Number Of Parent Rows That
Correctly
' Match 3 Children In An Aggregate Filter
'
' Performing a This Should Return 3, as all Parents have 3
Children
' - However, It incorrectly returns 2
'
==================================================================
Label1.Text =
DataSet1.Tables("Parent").Select("COUNT(CHILD(FK_Parent_Child).
[RowState]) = 3").GetLength(0)


Dim iChildRowStates As Int32

'
==================================================================
' Now Use Compute To SUM The Parent RowState Values (BitMask)
For
' Rows Whose Child Aggregate Filter SUM Matches
iChildRowStates
'
==================================================================

'
==================================================================
' Which Parent Row Rowstates Match Correctly SUM All Three
Child
' Rowstates: Added, Modified, UnChanged? (Deletes Should Be
Ignored)
' - Expected Answer: 22 (UnChanged - 2, Added - 4, Modified
- 16)
' - Actual Answer: 20 (Added - 4, Modified - 16)
'
==================================================================
iChildRowStates = DataRowState.Unchanged Or
DataRowState.Modified Or DataRowState.Added
Label2.Text =
DataSet1.Tables("Parent").Compute("SUM([RowState])",
String.Format("SUM(CHILD(FK_Parent_Child).[RowState]) = {0}",
iChildRowStates)).ToString()


'
==================================================================
' UnChanged Parent Rows ONLY Filter The SUM Of UnChanged Child
Rows
' (And Original Values Of Modified Child Rows)
' - i.e. This following returns: 2 (UnChanged)
' - However, it's not all Original Values, as Deleted Row
Original
' Values Are Correctly Ignored
'
==================================================================
iChildRowStates = DataRowState.Unchanged
Label3.Text =
DataSet1.Tables("Parent").Compute("SUM([RowState])",
String.Format("SUM(CHILD(FK_Parent_Child).[RowState]) = {0}",
iChildRowStates)).ToString()
End Sub

Public Sub FillParent()
' ==================================================
' Add Three Rows To The Parent Table With Different
' Rowstates (Added, UnChanged, Modified)
' (Storing Their RowState Enum In A Separate Field)
' ==================================================
With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
DataRowState.Added})
FillChildren(.Item("ID"))
End With

With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
DataRowState.Unchanged})
FillChildren(.Item("ID"))
.AcceptChanges()
End With

With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
0})
FillChildren(.Item("ID"))
.AcceptChanges()
.Item("RowState") = DataRowState.Modified
End With
End Sub

Public Sub FillChildren(ByVal iParentID As Int32)
' ==================================================
' Add For Rows To The Child Table With All Four
' Rowstates (Added, UnChanged, Modified, Deleted)
' (Storing Their RowState Enum In A Separate Field)
' - This is done for each Parent Row
' ==================================================
DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Added})

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Unchanged})
.AcceptChanges()
End With

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, 0})
.AcceptChanges()
.Item("RowState") = DataRowState.Modified
End With

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Deleted})
.AcceptChanges()
.Delete()
End With
End Sub

Public Function SetupTableSchema() As DataSet
Dim ds As New DataSet

Dim oParent As DataTable = ds.Tables.Add("Parent")
Dim oChild As DataTable = ds.Tables.Add("Child")

' ==================================================
' Set Up Parent Table
' ==================================================
With oParent
With .Columns.Add("ID", GetType(System.Int32))
.AutoIncrement = True
.AllowDBNull = False
.ReadOnly = True
End With
.PrimaryKey = New DataColumn() {.Columns("ID")}

.Columns.Add("RowState", GetType(System.Int32))

End With

' ==================================================
' Set Up Child Table
' ==================================================
With oChild
With .Columns.Add("ID", GetType(System.Int32))
.AutoIncrement = True
.AllowDBNull = False
.ReadOnly = True
End With
.PrimaryKey = New DataColumn() {.Columns("ID")}

.Columns.Add("ParentID", GetType(System.Int32))
.Columns.Add("RowState", GetType(System.Int32))
End With

' ==================================================
' Create Relationship
' ==================================================
ds.Relations.Add("FK_Parent_Child", oParent.Columns("ID"),
oChild.Columns("ParentID"), True)

Return ds
End Function

End Class

[/code]

Simon
Tue Mar 20 11:45:27 CDT 2007

Apologies - here's the corrected code sample:

[code]

Public Class Form1
'
==================================================================
' Form Requires:
' - 3 Labels (Label1, Label2, Label3)
' - 1 Button (Button1)
' - 1 UnTyped Dataset (DataSet1)
'
==================================================================

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
DataSet1 = SetupTableSchema()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
DataSet1.Clear()
FillParent()

'
==================================================================
' Use Select To Count The Number Of Parent Rows That
Correctly
' Match 3 Children In An Aggregate Filter
'
' Performing a This Should Return 3, as all Parents have 3
Children
' - However, It incorrectly returns 2
'
==================================================================
Label1.Text =
DataSet1.Tables("Parent").Select("COUNT(CHILD(FK_Parent_Child).
[RowState]) = 3").GetLength(0)


Dim iChildRowStates As Int32

'
==================================================================
' Now Use Compute To SUM The Parent RowState Values (BitMask)
For
' Rows Whose Child Aggregate Filter SUM Matches
iChildRowStates
'
==================================================================

'
==================================================================
' Which Parent Row Rowstates Match Correctly SUM All Three
Child
' Rowstates: Added, Modified, UnChanged? (Deletes Should Be
Ignored)
' - Expected Answer: 22 (UnChanged - 2, Added - 4, Modified
- 16)
' - Actual Answer: 20 (Added - 4, Modified - 16)
'
==================================================================
iChildRowStates = DataRowState.Unchanged Or
DataRowState.Modified Or DataRowState.Added
Label2.Text =
DataSet1.Tables("Parent").Compute("SUM([RowState])",
String.Format("SUM(CHILD(FK_Parent_Child).[RowState]) = {0}",
iChildRowStates)).ToString()


'
==================================================================
' UnChanged Parent Rows ONLY Filter The SUM Of UnChanged Child
Rows
' (And Original Values Of Modified Child Rows)
' - i.e. This following returns: 2 (UnChanged)
' - However, it's not all Original Values, as Deleted Row
Original
' Values Are Correctly Ignored
'
==================================================================
iChildRowStates = DataRowState.Unchanged
Label3.Text =
DataSet1.Tables("Parent").Compute("SUM([RowState])",
String.Format("SUM(CHILD(FK_Parent_Child).[RowState]) = {0}",
iChildRowStates)).ToString()
End Sub

Public Sub FillParent()
' ==================================================
' Add Three Rows To The Parent Table With Different
' Rowstates (Added, UnChanged, Modified)
' (Storing Their RowState Enum In A Separate Field)
' ==================================================
With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
DataRowState.Added})
FillChildren(.Item("ID"))
End With

With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
DataRowState.Unchanged})
FillChildren(.Item("ID"))
.AcceptChanges()
End With

With DataSet1.Tables("Parent").Rows.Add(New Object() {Nothing,
0})
FillChildren(.Item("ID"))
.AcceptChanges()
.Item("RowState") = DataRowState.Modified
End With
End Sub

Public Sub FillChildren(ByVal iParentID As Int32)
' ==================================================
' Add For Rows To The Child Table With All Four
' Rowstates (Added, UnChanged, Modified, Deleted)
' (Storing Their RowState Enum In A Separate Field)
' - This is done for each Parent Row
' ==================================================
DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Added})

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Unchanged})
.AcceptChanges()
End With

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, 0})
.AcceptChanges()
.Item("RowState") = DataRowState.Modified
End With

With DataSet1.Tables("Child").Rows.Add(New Object() {Nothing,
iParentID, DataRowState.Deleted})
.AcceptChanges()
.Delete()
End With
End Sub

Public Function SetupTableSchema() As DataSet
Dim ds As New DataSet

Dim oParent As DataTable = ds.Tables.Add("Parent")
Dim oChild As DataTable = ds.Tables.Add("Child")

' ==================================================
' Set Up Parent Table
' ==================================================
With oParent
With .Columns.Add("ID", GetType(System.Int32))
.AutoIncrement = True
.AllowDBNull = False
.ReadOnly = True
End With
.PrimaryKey = New DataColumn() {.Columns("ID")}

.Columns.Add("RowState", GetType(System.Int32))

End With

' ==================================================
' Set Up Child Table
' ==================================================
With oChild
With .Columns.Add("ID", GetType(System.Int32))
.AutoIncrement = True
.AllowDBNull = False
.ReadOnly = True
End With
.PrimaryKey = New DataColumn() {.Columns("ID")}

.Columns.Add("ParentID", GetType(System.Int32))
.Columns.Add("RowState", GetType(System.Int32))
End With

' ==================================================
' Create Relationship
' ==================================================
ds.Relations.Add("FK_Parent_Child", oParent.Columns("ID"),
oChild.Columns("ParentID"), True)

Return ds
End Function

End Class

[/code]