Linq - How to use 2 columns in where clause

Question

I have two DataTables and I want to select the rows from the first Table which are not present in second Table based on 2 columns(col1, col2) using linq

Please check below example

I tried example from this page Compare two DataTables and select the rows that are not present in second table

from the example they are used only one column

Edit 1 I have tried

        DataTable Table1 = new DataTable();
        Table1.Columns.Add("col1", typeof(string));
        Table1.Columns.Add("col2", typeof(string));

        DataRow r1 = Table1.NewRow();
        r1["col1"] = "A";
        r1["col2"] = "A-1";
        Table1.Rows.Add(r1);

        DataRow r2 = Table1.NewRow();
        r2["col1"] = "B";
        r2["col2"] = "B-2";
        Table1.Rows.Add(r2);

        DataRow r3 = Table1.NewRow();
        r3["col1"] = "C";
        r3["col2"] = "C-3";
        Table1.Rows.Add(r3);

        DataRow r4 = Table1.NewRow();
        r4["col1"] = "D";
        r4["col2"] = "D-4";
        Table1.Rows.Add(r4);

        DataRow r5 = Table1.NewRow();
        r5["col1"] = "E";
        r5["col2"] = "E-5";
        Table1.Rows.Add(r5);


        DataTable Table2 = new DataTable();
        Table2.Columns.Add("col1", typeof(string));
        Table2.Columns.Add("col2", typeof(string));

        DataRow r11 = Table2.NewRow();
        r11["col1"] = "A";
        r11["col2"] = "A-1";
        Table2.Rows.Add(r11);

        DataRow r22 = Table2.NewRow();
        r22["col1"] = "B";
        r22["col2"] = "B-2";
        Table2.Rows.Add(r22);

        DataRow r33 = Table2.NewRow();
        r33["col1"] = "C";
        r33["col2"] = "C-4";
        Table2.Rows.Add(r33);

        DataRow r44 = Table2.NewRow();
        r44["col1"] = "D";
        r44["col2"] = "DD";
        Table2.Rows.Add(r44);

        DataRow r55 = Table2.NewRow();
        r55["col1"] = "E";
        r55["col2"] = "EE";
        Table2.Rows.Add(r55);

        DataRow r66 = Table2.NewRow();
        r66["col1"] = "F";
        r66["col2"] = "FF";
        Table2.Rows.Add(r66);

Example - 1

DataTable table3s = (from a in Table1.AsEnumerable()
                             where !Table2.AsEnumerable().Any(e => (e.Field<string>("col1") == a.Field<string>("col1"))
                             && (e.Field<string>("col2") == a.Field<string>("col2")))
                             select a).CopyToDataTable();

Example - 2

 DataTable TableC = Table1.AsEnumerable().Where(ra => !Table2.AsEnumerable()
                            .Any(rb => rb.Field<string>("col1") == ra.Field<string>("col1")
                            && rb.Field<string>("col2") == ra.Field<string>("col2"))).CopyToDataTable();

Example 1 & 2 gives error when no matching rows

The source contains no DataRows

please give working example based on my sample code and suggest most efficient way because DataTable may contains large record like 10000 rows, 20000 rows and more

Answer 1

Or to have something with a proper outer join without an implicit loop using Any:

        var res = from a in Table1
            join b in Table2
                on (a.col1, a.col2) equals (b.col1, b.col2)
                into temp
            from b in temp.DefaultIfEmpty(default)
            where b.col2 == null
            select a;

It just joins the two tables using a composite key and puts it into the temp table. Then it does an outer join (DefaultIfEmpty) and takes only those entries from Table1 where the join returned an empty result.

Answer 2

Assuming you have

class Table1
{
    public string col1 { get; set; }
    public string col2 { get; set; }
}

class Table2
{
    public string col1 { get; set; }
    public string col2 { get; set; }
}

and

List<Table1> table1s = new List<Table1>();
List<Table2> table2s = new List<Table2>();

the query is

var table3s = from table1 in table1s
                where !table2s.Any(e => (e.col1 == table1.col1) && (e.col2 == table1.col2))
                select table1;

Answer 3

Try this. Basically, this line of code selects every element from Table1 whose "col1" and "col2" values do not exist in Table2.

var results = Table1.AsEnumerable().Where(t1 => Table2.AsEnumerable().All(t2 => t2["col1"] != 
    t1["col1"] || t2["col2"] != t1["col2"]));

Answer 4

I tried to resolve this using below logic. Please let me know if I missed something here?

 static void LinkPerf()
    {
        string[] arr = { "A", "B", "C", "D", "E", "F", "G", "H", "I" };

        DataTable table1 = new DataTable();

        table1.Columns.Add("Id");
        table1.Columns.Add("Col1");
        table1.Columns.Add("Col2");

        DataTable table2 = new DataTable();
        table2.Columns.Add("Id");
        table2.Columns.Add("Col1");
        table2.Columns.Add("Col2");

        DataTable ResultTable3 = new DataTable();
        ResultTable3.Columns.Add("Id");
        ResultTable3.Columns.Add("Col1");
        ResultTable3.Columns.Add("Col2");

        Random rand = new Random();
        for (int i = 1; i <= 10000; i++)
        {
            DataRow row = table1.NewRow();
            int index = rand.Next(arr.Length);
            var colVal = arr[index];

            //Table 1
            row[0] = i.ToString();
            row[1] = colVal;
            row[2] = colVal + "-" + i.ToString();
            table1.Rows.Add(row);

            //Table 2
            row = table2.NewRow();
            row[0] = i.ToString();
            row[1] = colVal;
            row[2] = (i % 5 == 0) ? colVal + colVal + i.ToString() : colVal + "-" + i.ToString();
            table2.Rows.Add(row);
        }

        Stopwatch watch = new Stopwatch();
        watch.Start();

        var result = table1.AsEnumerable()
.Where(ra => !table2.AsEnumerable()
                    .Any(rb => rb.Field<string>("Col1") == ra.Field<string>("Col1") && rb.Field<string>("Col2") == ra.Field<string>("Col2")));
        if (result.Any())
        {
            foreach (var item in result)
            {
                ResultTable3.ImportRow(item);
            }
        }
        watch.Stop();
        var timeTaken = watch.Elapsed;
        Console.WriteLine("Time taken: " + timeTaken.ToString(@"m\:ss\.fff"));
        Console.ReadLine();
    }