DataTable's NewRow() method causes re-evaluation of LINQ expression after being used as a BindingSource with AsDataView()

Question

In the following code, I have a LINQ expression that produces an EnumerableRowCollection(Of DataRow) that is eventually used in a BindingSource.

The first time the code loops, it's fine. However, once the BindingSource for the first Control has been set using AsDataView() on the IEnumerable, the next time the NewRow() method on the DataTable is called, it re-evaluates the LINQ expression.

Option Explicit On
Option Infer Off
Option Strict On

Public Class Form1
    Dim ds As New DataSet()

    Private Sub Form1_Load( sender As Object,  e As EventArgs) Handles MyBase.Load
        ds.Tables.Add("Table1")

        Dim comparisonInteger As Integer = 12
        ds.Tables("Table1").Columns.Add("ID", GetType(Integer))
        ds.Tables("Table1").Columns.Add("IntegerValue", GetType(Integer))
        ds.Tables("Table1").Columns.Add("StringValue", GetType(String))

        ds.Tables("Table1").Rows.Add({1, 47, "row 1"})
        ds.Tables("Table1").Rows.Add({2, 2, "row 1"})
        ds.Tables("Table1").Rows.Add({3, 7, "row 1"})
        ds.Tables("Table1").Rows.Add({4, 6, "row 1"})

        For i As Integer = 0 To 2
            Dim iterator As Integer = i
            Dim tb As New TextBox()

            CreateNewRowIfMissing(comparisonInteger+i)

            Dim dataValue As EnumerableRowCollection(Of DataRow) =  ds.Tables("Table1").AsEnumerable() _
                                                                        .Where(Function(v) DirectCast(v("IntegerValue"), Integer) = comparisonInteger + iterator)

            Dim bs As New BindingSource(dataValue.AsDataView(), Nothing)
            Dim b As New Binding("Text", bs, "IntegerValue")
            b.DataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            tb.DataBindings.Add(b)

            tb.Location = New Point(10, 10 * Me.Controls.Count+1)

            Me.Controls.Add(tb)
        Next
    End Sub

    Private Sub CreateNewRowIfMissing(comparisonInteger As Integer)

        If  ds.Tables("Table1").AsEnumerable() _
                .Where(Function(v) DirectCast(v("IntegerValue"), Integer) = comparisonInteger) _
                .Count() = 0

            ds.Tables("Table1").Rows.Add(ds.Tables("Table1").NewRow()) 'Prompts the dataValue IEnumerable to begin evaluating again.
            ds.Tables("Table1").Rows(ds.Tables("Table1").Rows.Count-1)("ID") = ds.Tables("Table1").Rows.Count
            ds.Tables("Table1").Rows(ds.Tables("Table1").Rows.Count-1)("IntegerValue") = comparisonInteger
            ds.Tables("Table1").Rows(ds.Tables("Table1").Rows.Count-1)("StringValue") = "row" & ds.Tables("Table1").Rows.Count.ToString()
        End If
    End Sub
End Class

To clarify, I used to use a standard DataView with a RowFilter as the BindingSource, which worked:

Dim dv As New DataView(ds.Tables("Table1"))
dv.RowFilter = "IntegerValue=" & (comparisonInteger+iteratorValue)

However I felt it might be nicer to make use of LINQ instead.

So, what's going on here? Why is the LINQ expression the DataView was based on being re-evaluated on the call to the DataTable's NewRow() method? Is there any way I can prevent this?

I had hoped that AsDataView() would have produced a DataView that would behave in the same way as the above two lines.

As potential work-around, I've found that I can use this as the BindingSource's DataSource:

New DataView(dataValue.AsEnumerable().CopyToDataTable())

However, I'm concerned about potential performance with such a line in the future, and that I might as well use the DataView with the RowFilter rather than the LINQ if it required the above line.

Answer 1

It looks like whenever the underlying DataTable is updated, the LINQ expression for the DataView is automatically re-evaluated. This means that the LINQ was being re-evaluated when the new row was added to the DataTable, rather than on the call to NewRow(). This wasn't immediately obvious to me as I was doing both actions in a single line: ds.Tables("Table1").Rows.Add(ds.Tables("Table1").NewRow())

@RezaAghaei mentioned that the ListChanged event will get raised if the underlying DataTable is updated. Through testing, it seems that if the DataView is created from a LINQ expression, it re-valuates the LINQ before the ListChanged event is raised, if the underlying DataTable is updated.

As a consequence of this, setting the BindingSource's RaiseListChangedEvents property won't stop the evaluation of the LINQ, and as the evaluation happens prior to the ListChanged event raised, there appears to be no other event that can be handled to cancel the behaviour, so I don't think it's possible to stop this from happening.

However, now that I understand what it's doing, it makes sense for the underlying LINQ expression to be re-evaluated when the row itself is added to the DataTable, so it probably wouldn't be a good idea to stop it anyway.

It was causing some bother for me though as the new row I was adding was not fully set-up, resulting in an InvalidCastException. However, in my example above, I merely needed to make sure that the new row is fully set-up before adding it to the DataTable. Thus, when the LINQ is re-evaluated, the row contains valid data and the expression runs without error.

So, in the example above, the CreateNewRowIfMissing() method merely needs to be adjusted to the following:

Private Sub CreateNewRowIfMissing(comparisonInteger As Integer)

    If  ds.Tables("Table1").AsEnumerable() _
            .Where(Function(v) DirectCast(v("IntegerValue"), Integer) = comparisonInteger) _
            .Count() = 0

        Dim newRow As DataRow = ds.Tables("Table1").NewRow()

        newRow("ID") = ds.Tables("Table1").Rows.Count
        newRow("IntegerValue") = comparisonInteger
        newRow("StringValue") = "row" & ds.Tables("Table1").Rows.Count.ToString()

        ds.Tables("Table1").Rows.Add(newRow) 'Prompts the dataValue IEnumerable to begin evaluating again.
    End If
End Sub

This ensures that the new row is only added to the DataTable after it is ready and contains valid data. As result, when it is finally added, the LINQ expression re-evaluates and doesn't throw an InvalidCastException.

Answer 2

It's the normal behavior of DataTableExtensions.AsEnumerable() and it's documented:

The enumerable object returned by the AsEnumerable method is permanently bound to the DataTable that produced it. Multiple calls to the AsEnumerable method will return multiple, independent queryable objects that are all bound to the source DataTable.

Also the normal behavior of DataView which is also documented:

The DataView does not store data, but instead represents a connected view of its corresponding DataTable.

When you call DataTable.AsEnumemarble() method, the EnumerableRowCollection<DatRow> which returns by the method keeps a reference to the original DataTable and then use it each time you call EnumerableRowCollection(Of T).AsDataView() method and returns rows of original DataTable which is used to create the collection.

So in your example dataValue.AsDataView() which you used as DataSource of the BindingSource always returns current rows of DataTable when a ListChanged event raised.

But if you assign new DataView(data.AsEnumerable().CopyToDataTable()) to DataSource of the BindingSource, since you are using a different DataTable by calling CopyToDataTable, changing the original DataTable doesn't have any impact on the data source which is completely unaware of the original DataTable. In fact its enough to use below statement as DataSource of the BindingSource:

bs.DataSource = dataValue.CopyToDataTable()

Example

Put a DataGridView and 3 buttons on a Form, then handle Click event of buttons like below.

DataTable dt;
EnumerableRowCollection<DataRow> data;
BindingSource bs;
private void button1_Click(object sender, EventArgs e)
{
    dt = new DataTable();
    dt.Columns.Add("A", typeof(int));
    for (int i = 0; i < 5; i++)
        dt.Rows.Add(i);
    data = dt.AsEnumerable().Where(x => x.Field<int>(0) >= 3);
    bs = new BindingSource();
    bs.DataSource = data.AsDataView(); 
    bs.RaiseListChangedEvents = true;  
    this.dataGridView1.DataSource = bs;
}
private void button2_Click(object sender, EventArgs e)
{
    dt.Rows.Add(dt.AsEnumerable().Max(x => x.Field<int>(0)) + 1);
}
private void button3_Click(object sender, EventArgs e)
{
    var row = data.Where(x => x.Field<int>(0) == 3).First();
    row[0] = 333;
    MessageBox.Show(dt.Rows[3][0].ToString());
}

Test 1

1. Click on Button1, the DataGridView will show 3, 4 in column A.
2. Click on Button2, new row with 5 as value of column A will be added and shown immediately in grid. Also it shows a MessageBox which shows 3 as count of items in data.
3. Click on Button3, 3 will be changed to 333 and immediately will be shown in grid. Also it shows 333 as value of cell in data table.

Result → by turning on ListChanged and using AsDataView you will see changes immediately in grid.

Test 2

1. Turn off raising ListChanged using bs.RaiseListChangedEvents = false;
2. Click on Button1 will act like test 1.
3. Click on Button2 will no more shows new record immediately. While it shows 3 as count of rows in data.
4. Click on Button3 will no more shows edited value immediately. While the messagebox shows the data has been changed in DataTable.

Result → If you turn off ListChanged event, the changed will not be shown in the list immediately.

Test 3

1. Use bs.DataSource = data.CopyToDataTable();
2. Set bs.RaiseListChangedEvents = true;
3. Click on Button1 to fill grid.
4. Click on Button2 doesn't have any impact on grid, but shows 3 as number of rows.
5. Click on Button3 doesn't have any impact on grid, but shows 333 as value of data table.

Result → AsDataTable creates a new DataTable which is not related to original DataTable.