asked on

Is there a way to get non Matches from two tables using LINQ

Is there a way to only return/ include non matches? Excluding the rows that match

Table A:
Name Number Title
Joe 1 Mr
Jim 2 Dr
Jack 3 Mr
Mike 4 Dr

Table B:
Name Number Title
Mike 4 Mr
Moe 5 Dr
Max 6 Mr
Jim 2 Dr
Jack 3 Mr

??? Query:
Name Number Title
Joe 1 Mr
Mike 4 Dr
Mike 4 Mr
Moe 5 Dr
Max 6 Mr

Member_2_4226667

Hi EAK31640GW, I can help you with the SQL query bit of it. Firstly, the syntax query depends on what platform you are working with. In general speaking, this usually should work:

(
      SELECT Name,Number,Title
       FROM TableA
      except
      SELECT Name,Number,Title
       FROM TableB
)
union
(
      SELECT Name,Number,Title
       FROM TableB
      except
      SELECT Name,Number,Title
       FROM TableA
)

veenaravind

Hi EAK31640G,

I have just changed bpnchan's SQL query to Linq. The below is the Linq format.

   TestClassesDataContext db = new TestClassesDataContext();

        var a = from ta in db.TableA
                select new { ta.Name, ta.Number, ta.Title };

        var b = from tb in db.TableB
                select new { tb.Name, tb.Number, tb.Title };

        var notInA = a.Except(b);
        var notInB = b.Except(a);

        var result = notInA.Union(notInB);

        foreach (var i in result)
        {
            Console.WriteLine(i);
        }

Open in new window

Hope this helps.

Thanks!

Member_2_4226667

So, this is how to do that in LINQ? Learn something new again, thanks ^v^

EAK31640GW

ASKER

Ok so Either I am doing this wrong Or except() doesn't work as it seems it should.

            DataTable dtPhysical = new DataTable();
            DataTable dtDataBase = new DataTable();           

            dtPhysical.Columns.Add(new DataColumn("ItemNum", typeof(string)));
            dtPhysical.Columns.Add(new DataColumn("SerialNumber", typeof(string)));
            dtPhysical.Columns.Add(new DataColumn("Shelf", typeof(string)));
            dtPhysical.Columns.Add(new DataColumn("Bin", typeof(string)));
            dtPhysical.Columns.Add(new DataColumn("Quantity", typeof(int)));

            dtDataBase.Columns.Add(new DataColumn("ItemNum", typeof(string)));
            dtDataBase.Columns.Add(new DataColumn("SerialNumber", typeof(string)));
            dtDataBase.Columns.Add(new DataColumn("Shelf", typeof(string)));
            dtDataBase.Columns.Add(new DataColumn("Bin", typeof(string)));
            dtDataBase.Columns.Add(new DataColumn("Quantity", typeof(int)));

            DataTable dtQueryPh = dtPhysical.Clone();
            DataTable dtQueryDb = dtDataBase.Clone();

            for (int i = 0; i < 100; i++)
            {
                DataRow row = dtPhysical.NewRow();
                row["ItemNum"] = i.ToString();
                row["SerialNumber"] = i.ToString() + "abc" + (i * 2).ToString();
                row["Shelf"] = "Shelf " + (i / 10).ToString();
                row["Bin"] = "Bin " + (i / 5).ToString();
                row["Quantity"] = (i % 2 == 0) ? i : i * 2;
                dtPhysical.Rows.Add(row);
            }
            for (int i = 0; i < 100; i++)
            {
                DataRow row = dtDataBase.NewRow();
                row["ItemNum"] = i.ToString();
                //row["Scanned"] = (i + i).ToString();
                row["SerialNumber"] = i.ToString() + "abc" + (i * 2).ToString();
                row["Shelf"] = "Shelf " + (i / 10).ToString();
                row["Bin"] = "Bin " + (i / 5).ToString();
                row["Quantity"] = (i % 2 == 0) ? i : i + 2;
                dtDataBase.Rows.Add(row);
            }

            var queryPh = from dtP in dtPhysical.AsEnumerable()
                          select dtP;

            var queryDb = from dtD in dtDataBase.AsEnumerable()
                          select dtD;

            var qDbOnly = queryDb.Except(queryPh);
            var qPhOnly = queryPh.Except(queryDb);

            dtQueryDb = qDbOnly.CopyToDataTable();
            dtQueryPh = qPhOnly.CopyToDataTable();

            dgvDb.DataSource = dtQueryDb;
            dgvPh.DataSource = dtQueryPh;

Open in new window

(I am binding them to two DataGridViews) And all the values are showing up in each as if none of them match.
Am I missing something?

ASKER CERTIFIED SOLUTION

veenaravind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

veenaravind

Hi,

The only replacement you need to do when you are using DataTable is,

Replace the below,

            var qDbOnly = queryDb.Except(queryPh);
            var qPhOnly = queryPh.Except(queryDb);

Open in new window

with,

 var qDbOnly = queryDb.Except(queryPh, DataRowComparer.Default);
 var qPhOnly = queryPh.Except(queryDb, DataRowComparer.Default);

Open in new window

Good luck!

EAK31640GW

ASKER

veenaravind,

thanks a bunch! Ended up having to create my own IEqualityComparer<T> to use since the tables I was comparing were not as nice and neat as the example we used. But your answer was right on!