Inconsistent LINQ Results comparing DataTables

Question

I'm trying to use LINQ to compare columns in 2 DataTables for differences. The two columns are of the same type (Int32) and the query for both is the same, but only one gives the expected results. The other one does not always catch changes. It registers changes in value from 1 to 0, but changes in value from 0 to 1 are ignored. The two LINQ statements are shown below.

This works:

    Dim result = From table1 In newTable
                 Where Not (From table2 In origTable 
                     Where (table2("Ack Required") = table1("Ack Required"))).Any() 
                 Select table1.ItemArray()

This works when the value changes to 1, but does nothing when it changes to 0:

    Dim result2 = From table1 In newTable
                  Where Not (From table2 In origTable 
                      Where (table2("Active") = table1("Active"))).Any()
                  Select table1.ItemArray()

What am I doing wrong here?

Edit: Here is a looping implementation I found that does what I'm trying to do:

Public Function AreTablesTheSame(table1 As DataTable, table2 As DataTable) As Boolean
    ' Reference - https://stackoverflow.com/questions/7517968/how-to-compare-2-datatables

    ' If row/column count are different there is no need to look at data
    If table1.Rows.Count <> table2.Rows.Count OrElse table1.Columns.Count <> table2.Columns.Count Then
        Return False
    End If

    ' Row/column count are the same in both tables
    ' Check each value until difference found or end of table reached
    For i As Integer = 0 To table1.Rows.Count - 1
        For c As Integer = 0 To table1.Columns.Count - 1
            If Not Equals(table1.Rows(i)(c), table2.Rows(i)(c)) Then
                Return False

            End If
        Next
    Next

    Return True

End Function

You don't appear to be joining the rows in the two tables on any sort of key to compare matching rows, so I'm not sure that either is doing what you think. Is there some sort of ID field in the tables? — Mark, Jul 28 '16 at 21:36
That makes sense. There is not an ID field. I'll have to look at the data, but I think a combination of two fields would produce a unique value. — thephez, Jul 28 '16 at 21:44

score 0 · Answer 1 · answered Jul 28 '16 at 21:42

0

I think your query could be refactored to a single method, which takes the column name as a parameter.

Because you put the second query in parenthesis it will work not as you would expect. I'm not sure what actually happens, but I think the created expression tree will compile to something completely different.

If you want to compare each row in the first table, with each row in the second table write the query as follows:

Function CompareTables(newTable As DataTable, origTable As DataTable, column as String) 
          As IEnumerable(Of Object())

        Return _ 
            From table1 In newTable
            From table2 In origTable 
            Where (table2(column) <> table1(column))
            Select table1.ItemArray

End Function

answered Jul 28 '16 at 21:42

Ric .Net

5,540
1
20
39

Thanks for the feedback. I'm getting unexpected results with this too. For instance, comparing 2 identical tables with 4 rows is returning a result with 6 rows (one of the rows is duplicated twice). – thephez Jul 29 '16 at 12:53
What is the result you'd expect? – Ric .Net Jul 30 '16 at 09:32
In the case of identical tables, I'm expecting no rows in the result (I'm trying to get only the rows that do not match). – thephez Aug 01 '16 at 13:06
Yep ok, but if you check one column, does this mean the tables are identical? – Ric .Net Aug 01 '16 at 14:18
No, I'm just comparing known identical tables as a basic check to see if I'm getting the results I'm looking for. I've gone ahead and implemented a more brute force for-loop approach that does what I need. It's just a bit ugly so I was trying to figure out how to do it with LINQ. – thephez Aug 01 '16 at 17:13
Show the working for loop on your question, it gives a good idea what you exactly want. Ping me of you updated the question – Ric .Net Aug 01 '16 at 21:06
I found another SO answer that does what I'm trying to do. [How to compare 2 datatables](https://stackoverflow.com/questions/7517968/how-to-compare-2-datatables/27512126#27512126). – thephez Aug 03 '16 at 16:01

Inconsistent LINQ Results comparing DataTables

1 Answers1