Compare two DataTables using LINQ

I have two Databases. They are both filled into DataTables using t开发者_高级运维he GetTable1 \ GetTable2 functions.

What I am looking to do is to basically compare the datatables using LINQ.

I Have tried :

var infoQuery =
                    (from db1 in GetTable1().AsEnumerable()
                     select db1).Except                            
                            (from db2 in GetTable2().AsEnumerable()
                             select db2);

Also tried : (Looks like it should be doing same as above ) :

var results = GetTable1().AsEnumerable().Except(GetTable2().AsEnumerable());

The results I get back are all the records in one table. I am looking for a return of 1 since 1 row is different between the two databases.

I am using default Equals method from Object , do I need to override that implementation to get this to work?

---

It has to do with the way the rows are compared. Their values are not compared their references are.

http://msdn.microsoft.com/en-us/library/bb300779.aspx

To compare custom data types, implement the IEquatable generic interface and provide your own GetHashCode and Equals methods for the type. The default equality comparer, Default, is used to compare values of types that implement IEquatable.

Example from MSDN:

public class Product : IEquatable<Product>
{
    public string Name { get; set; }
    public int Code { get; set; }

    public bool Equals(Product other)
    {

        //Check whether the compared object is null.
        if (Object.ReferenceEquals(other, null)) return false;

        //Check whether the compared object references the same data.
        if (Object.ReferenceEquals(this, other)) return true;

        //Check whether the products' properties are equal.
        return Code.Equals(other.Code) && Name.Equals(other.Name);
    }

    // If Equals() returns true for a pair of objects 
    // then GetHashCode() must return the same value for these objects.

    public override int GetHashCode()
    {

        //Get hash code for the Name field if it is not null.
        int hashProductName = Name == null ? 0 : Name.GetHashCode();

        //Get hash code for the Code field.
        int hashProductCode = Code.GetHashCode();

        //Calculate the hash code for the product.
        return hashProductName ^ hashProductCode;
    }
}

...

    Product[] fruits1 = { new Product { Name = "apple", Code = 9 }, 
                           new Product { Name = "orange", Code = 4 },
                            new Product { Name = "lemon", Code = 12 } };

    Product[] fruits2 = { new Product { Name = "apple", Code = 9 } };

    //Get all the elements from the first array
    //except for the elements from the second array.

    IEnumerable<Product> except =
        fruits1.Except(fruits2);

    foreach (var product in except)
        Console.WriteLine(product.Name + " " + product.Code);

    /*
      This code produces the following output:

      orange 4
      lemon 12
    */

---

Even though they might have the same columns a single data row from table1 does not match a row from table2. So row_of_table1 != row_of_table2 is always true. You must provide additional information to compare them ( maybe with an identifier that is unique to both tables ). One approach you could use as a starting point is described in an answer here.

The reason why they do not match is already posted by Nate Zaugg