Perform "except" using particular columns

Question

Consider 2 tables with the same schema:

var yesterday = new DataTable();
yesterday.Columns.Add("id", typeof(int));
yesterday.Columns.Add("part_number", typeof(string));
yesterday.Columns.Add("description", typeof(string));
yesterday.Columns.Add("comment", typeof(string));
yesterday.Columns.Add("information", typeof(string));
yesterday.Columns.Add("data", typeof(string));
var today = yesterday.Clone();

Add some data:

//yesterday data has 3 rows
yesterday.Rows.Add(1, "IVD_002", "IVD_002_RED", "Some comment", "Some information","Some data");
yesterday.Rows.Add(2, "IVD_003", "IVD_003_RED", "Some comment", "Some information", "Some data");
yesterday.Rows.Add(3, "IVD_004", "IVD_004_RED", "Some comment", "Some information", "Some data");

//today's data has the same 3 rows
today.Rows.Add(1, "IVD_002", "IVD_002_RED", "Some comment", "Some information", "Some data");
today.Rows.Add(2, "IVD_003", "IVD_003_RED", "Some comment", "Some information", "Some data");
today.Rows.Add(3, "IVD_004", "IVD_004_RED", "Some comment", "Some information", "Some data");

Let's add more data:

//The New Row:
//In the output table I expect to see only the following row. The "id" column is 5 whereas in previous records there is no row with id = 5, part_number = IVD_002, description = IVD_002_RED
today.Rows.Add(5, "IVD_002", "IVD_002_RED", "Some comment", "Some information", "Some data");

//Another New Row:
//I dont expect to see this row in the result table because we are doing except only on "id","part_number","description" columns
today.Rows.Add(1, "IVD_002", "IVD_002_RED", "ROSES ARE RED", "PEANUTS", "=)");

My goal is to get rows from "today" table except rows from "yesterday" table BUT comparing only columns "id","part_number","description".

Would really appreciate a pure LINQ solution, w/o loops.

Answer 1

I would suggest using !Any. I would prefer to convert the yesterday DataTable to a HashSet so you aren't constantly linearly scanning yesterday for matches, but I am not sure what limitations UiPath has.

var result = today.AsEnumerable().Where(t => !yesterday.AsEnumerable().Any(y => (y["id"].Equals(t["id"]) &&
                                                                                 y["part_number"].Equals(t["part_number"]) &&
                                                                                 y["description"].Equals(t["description"]))))
                                 .CopyToDataTable();

If you could use a HashSet, then you could do:

var yesterdayHash = yesterday.AsEnumerable().Select(y => new { id = (int)y["id"], partnum = y["part_number"].ToString(), desc = y["description"].ToString() }).ToHashSet();
var result2 = today.AsEnumerable().Where(t => !yesterdayHash.Contains(new { id = (int)t["id"], partnum = t["part_number"].ToString(), desc = t["description"].ToString() })).CopyToDataTable();

With a static class Cast helper for creating Func delegates that return anonymous types, you can create a lambda variable to hold the common key expression:

public static class To {
    public static Func<TResult> Func<TResult>(Func<TResult> func) => func;
    public static Func<T, TResult> Func<T, TResult>(Func<T, TResult> func) => func;
}

var selectorFn = To.Func((DataRow r) => new { id = r.Field<int>("id"), partnum = r.Field<string>("part_number"), desc = r.Field<string>("description") });
var yesterdayHash2 = yesterday.AsEnumerable().Select(selectorFn).ToHashSet();
var result3 = today.AsEnumerable().Where(t => !yesterdayHash2.Contains(selectorFn(t))).CopyToDataTable();

Note: I prefer using the Field<> extension method on DataRow to get strongly typed DataTable column values.