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.

You need something like this https://stackoverflow.com/a/52738791/2946329 — Salah Akbari, Apr 13 '20 at 12:41
This solution is intended to be used in UiPath, so I'm rather limited in what I can do with code. A LINQ-query would have been optimal. — Nick Farsi, Apr 13 '20 at 12:44
@SalahAkbari This is a DataTable, not a list of custom objects. — juharr, Apr 13 '20 at 12:49
@juharr I believe it is still possible, maybe something like this `IEqualityComparer` since the `DataTable` is an object still. — Salah Akbari, Apr 13 '20 at 12:53

NetMage · Accepted Answer · 2020-04-13T19:54:27.727

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.

As for HashSet in UiPath, unfortunately it does not do well with anonymous types, so I'd have to stick to option #1 — Nick Farsi, Apr 13 '20 at 21:58

score 0 · Answer 2 · answered Apr 13 '20 at 13:05

0

I think I got it. Is there a better way though?

var result = today.AsEnumerable()
    .Where(r =>
        today.AsEnumerable()
            .Select(r =>
                new { id = r["id"].ToString(), part_number = r["part_number"].ToString(), description = r["description"].ToString() })
            .Except(yesterday.AsEnumerable()
                .Select(r =>
                    new { id = r["id"].ToString(), part_number = r["part_number"].ToString(), description = r["description"].ToString() }))
            .Contains(new { id = r["id"].ToString(), part_number = r["part_number"].ToString(), description = r["description"].ToString() }))
    .CopyToDataTable();

answered Apr 13 '20 at 13:05

Nick Farsi

366
4
19

Is there a way to assign the "new" construct to a variable? An array of columns or smth? So I don't type the same 3 times – Nick Farsi Apr 13 '20 at 14:32
If you could create a lambda variable, you could do `var selectorFn = (DataTable r) => new { id = r["id"].ToString(), part_number = r["part_number"].ToString(), description = r["description"].ToString() };` And then use it `today.AsEnumerable().Where(r => today.AsEnumerable().Select(selectorFn).Except(yesterday.AsEnumerable().Select(selectorFn)).Contains(selectorFn(r)).CopyToDataTable();`. – NetMage Apr 13 '20 at 18:59
You would need a static cast helper for this to work. See My answer update. – NetMage Apr 13 '20 at 19:53

Perform "except" using particular columns

2 Answers2