Avatar of EAK31640GW
EAK31640GW
 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
Microsoft SQL ServerC#

Avatar of undefined
Last Comment
EAK31640GW

8/22/2022 - Mon
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^
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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!  
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.