Comparing two datatables through Linq

Question

Hi I'm trying to compare two datatable through Linq. But I get this exception:

Specific cast is invalid

Please help me as I am new to Linq. This is the code I'm using:

var matched1 = from table1 in dtAvailableStores.AsEnumerable()
               join table2 in dtControlStores.AsEnumerable() 
               on table1.Field<int>("STORE_NBR") 
               equals table2.Field<int>("STORE_NBR")
               select table1;

Here STORE_NBR is a string value.

Answer 1

You can have a fairly good idea with this piece of code:

var qry1 = datatable1.AsEnumerable().Select(a => new { MobileNo = a["ID"].ToString() });
var qry2 = datatable2.AsEnumerable().Select(b => new { MobileNo = b["ID"].ToString() });

var exceptAB = qry1.Except(qry2);

DataTable dtMisMatch = (from a in datatable1.AsEnumerable() join ab in exceptAB on a["ID"].ToString() equals ab.MobileNo select a).CopyToDataTable();

References:

1. Compare two datatable using LINQ Query
2. Compare two DataTables for differences in C#?

Answer 2

This would happen if that field isn't actually an int.