Compare same DataTable by certain columns, return mismatches to CopyToDataTable() linq c#

[mrw (vd)]

I have two DataTables (Columns: ID, MODEL, POWER, CLASS):

Table 1:

411 Audi   500 300
412 BMW    400 800
413 Lexus  200 700

Table 2:

876 Audi   500 300
896 BMW    400 870
832 Lexus  200 700
874 Fiat   250 450

As result I need to get (mismatch by at least one criteria, not taking in consideration ID):

412 BMW    400 800
896 BMW    400 870

I can’t perform Except because there is first number that does not match between two (4… & 8…). If I am querying before merging two DataTables together:

var matched = from table1 in MainProcess.dt1.AsEnumerable()
     join table2 in MainProcess.dt2.AsEnumerable()
     on table1.Field<string>("MODEL") equals table2.Field<string>("MODEL")

     where table1.Field<string>("POWER") != table2.Field<string>("POWER")
     || table1.Field<string>("CLASS") != table2.Field<string>("CLASS")
                 select table1;

     return matched.CopyToDataTable();

As result I am getting only one of results 412 BMW 400 800. I have tried with .Count() > 1 && .Count() < 1 but it didn’t work either.

What is the right way to solve this problem? I guess I need to merge two DataTables before querying?

[ppr (Peter Preuss)]

@mrw
welcome to the forum
As you select only table1 the table2 from Join Pair is not fetched/handled.

Find an approach in VB.Net Syntax here:

Starter help here:
GetLR_1Col_2ColCompare.xaml (10.8 KB)

[mrw (vd)]

var matched = from table1 in MainProcess.dt1.AsEnumerable()
    join table2 in MainProcess.dt2.AsEnumerable()
       on table1.Field<string>("MODEL") equals table2.Field<string>("MODEL")
    where table1.Field<string>("POWER") != table2.Field<string>("POWER")
        || table1.Field<string>("CLASS") != table2.Field<string>("CLASS")
    select new { table1, table2 };

var result = matched.SelectMany(x => new[] { x.table1, x.table2 })
    .CopyToDataTable();

seem to work. I don’t understand what Function’s are. Also can you give a hint how to apply the same logic if I have only one DataTable that I am filtering, instead of two as I was pointing out in my original question?

[ppr (Peter Preuss)]

@mrw

the Lambda syntax differs between c# and VB.Net. Have a look here:
Anatomy of the Lambda Expression

function is the equivalent to => from C#

About join on the same table, the motivation / requirment is important to know.

Maybe you want to find out which rows have same Model, but differs on Power or Class.
We can check the option of

• a self join approach
• group by on the keys Model and doing some group member evaluation

just as an idea, not confirmed as it is bound to fixed requirements and sample data.

However just lets work only on the inital sceanrio and close the opic once it is done. For second scenario feel free to open an new Topic.

Regards
Peter