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
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#
Last Comment
EAK31640GW
Hi EAK31640G,
I have just changed bpnchan's SQL query to Linq. The below is the Linq format.
Hope this helps.
Thanks!
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);
}
Hope this helps.
Thanks!
So, this is how to do that in LINQ? Learn something new again, thanks ^v^
ASKER
Ok so Either I am doing this wrong Or except() doesn't work as it seems it should.
(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?
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;
(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
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.
Hi,
The only replacement you need to do when you are using DataTable is,
Replace the below,
with,
Good luck!
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);
with,
var qDbOnly = queryDb.Except(queryPh, DataRowComparer.Default);
var qPhOnly = queryPh.Except(queryDb, DataRowComparer.Default);
Good luck!
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!
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!
(
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
)