CODEWITHSUNDEEP
c#linqdatatable
Ullan
Ullan

Reputation: 1007

How to compare two DataTables and returns records with only the unmatching data

I am trying to compare two DataTables TableA and TableB.

For Example:

TableA has 7 columns and 5 rows. Currency column is the primary key TableB 5 columns and 3 rows. Currency column is the primary key

I want to compare Table1A's each rows data against TableB's rows data using the currency and display only the rows with un matching data (U pto the column Rate4) enter image description here

Thanks for your help

Upvotes: 0

Views: 3026

Answers (3)

King King
King King

Reputation: 63317

Try this:

DataTable dt = TableB.Clone();
dt = TableA.AsEnumerable().Join(TableB.AsEnumerable(),
                                row=>row.Field<string>(0),
                                row=>row.Field<string>(0), (a,b)=> new {a,b})
        .Select(pair=> {
          DataRow row = dt.NewRow();
          row.SetField<string>(0, pair.a.Field<string>(0));
          bool notNull = false;
          for(int i = 1; i < 5; i++){ 
            var a = pair.a.Field<decimal>(i);
            var b = pair.b.Field<decimal>(i);
            if(a == b) row.SetField<decimal>(i, 0);
            else {
               row.SetField<decimal>(i, Math.Max(a,b));
               notNull = true;
            }
          }
          return notNull ? row : null;
       }).Where(row=>row != null).CopyToDataTable();

NOTE: I suppose the data type in all the columns Rate X is decimal, the first column Currency is of course string.

Upvotes: 0

Elie
Elie

Reputation: 1140

var tableA = new DataTable();
tableA.Columns.Add("Currency", typeof(string));
tableA.Columns.Add("Rate1", typeof(decimal));
tableA.Columns.Add("Rate2", typeof(decimal));
tableA.Columns.Add("Rate3", typeof(decimal));
tableA.Columns.Add("Rate4", typeof(decimal));
tableA.Columns.Add("Rate5", typeof(decimal));
tableA.Columns.Add("Rate6", typeof(decimal));

tableA.Rows.Add("USD", 1m, 2m, 3m, 4m, 5.5m, 4.5m);
tableA.Rows.Add("JPY", 1.11m, 4.1m, 3.3m, 4.6m, 5.5m, 3.3m);
tableA.Rows.Add("GBP", 3.0m, 1m, 3m, 4m, 7.7m, 8.8m);
tableA.Rows.Add("EUR", 3.0m, 1m, 3m, 4m, 7.7m, 8.8m);
tableA.Rows.Add("MXN", 3.0m, 1m, 3m, 4m, 7.7m, 8.8m);


var tableB = new DataTable();
tableB.Columns.Add("Currency", typeof(string));
tableB.Columns.Add("Rate1", typeof(decimal));
tableB.Columns.Add("Rate2", typeof(decimal));
tableB.Columns.Add("Rate3", typeof(decimal));
tableB.Columns.Add("Rate4", typeof(decimal));

tableB.Rows.Add("USD", 1m, 2m, 3m, 4m);
tableB.Rows.Add("JPY", 1.11m, 9.9m, 3.3m, 4.6m);
tableB.Rows.Add("GBP", 3m, 1m, 3m, 4m);

var query = from r1 in tableA.AsEnumerable()
            from r2 in tableB.AsEnumerable()
            where
                r1.Field<string>("Currency") == r2.Field<string>("Currency")

            && r1.Field<decimal>("Rate1") == r2.Field<decimal>("Rate1")
            && r1.Field<decimal>("Rate2") == r2.Field<decimal>("Rate2")
            && r1.Field<decimal>("Rate3") == r2.Field<decimal>("Rate3")
            && r1.Field<decimal>("Rate4") == r2.Field<decimal>("Rate4")
    select r2;

var result = tableB.AsEnumerable().Except(query).ToList();

Upvotes: 1

Magnus
Magnus

Reputation: 46909

Something like this perhaps:

var q = from a in dtA.AsEnumerable()
        join b in dtB.AsEnumerable()
          on a.Field<string>("Currency") equals b.Field<string>("Currency")

        where a.Field<double>("Rate1") != b.Field<double>("Rate1") || 
              a.Field<double>("Rate2") != b.Field<double>("Rate2") || ....

        select new 
        {
            Currency = a.Field<string>("Currency"),
            Rate1    = a.Field<double>("Rate1") == b.Field<double>("Rate1") ? 
                           0 : a.Field<double>("Rate1"),
            Rate2    = a.Field<double>("Rate2") == b.Field<double>("Rate2") ? 
                           0 : a.Field<double>("Rate2"),
            ...
        };

Upvotes: 0

Related Questions