How to compare 2 dataTables
You would need to loop through the rows of each table, and then through each column within that loop to compare individual values.
There's a code sample here: http://canlu.blogspot.com/2009/05/how-to-compare-two-datatables-in-adonet.html
LINQ: Comparing specific values in two datatables
Try this
DataTable dtMerged = dataTable.AsEnumerable()
.Where(ra => dt.AsEnumerable()
.Any(rb => rb.Field<string>("firstname") == ra.Field<string>("firstname")
&& rb.Field<string>("lastname") == ra.Field<string>("lastname")
&& rb.Field<DateTime>("dob") == ra.Field<DateTime>("dob")
&& rb.Field<string>("gender") == ra.Field<string>("gender")))
.CopyToDataTable();
Effecient way to compare data tables
First I would ask if you have tried this in a simple for/foreach loop instead and compared the performance?
At the moment you are creating a new Enumerable and then copying to a datatable.
If you use a for/foreach loop then you can compare and copy in the same iteration.
You should also look at the string comparison. At the moment you are trimming then converting to lowercase. This will allocate new memory for each operation for each string as strings are immutable. So in your where statement you are basically doing this (up to) 8 times per iteration.
I would also ask if you really need Trim()
? Is it likely that one DT will have a space at the front of the string and the other not? Or will a comparison still be true? Don't trim strings unless really needed.
Then you should use case insensitive string comparison rather than converting ToLower
. This will be quicker. According to MS StringComparison.OrdinalIgnoreCase
is better performing.
Do these and then compare performance and see how much difference you have
See also:
https://learn.microsoft.com/en-us/dotnet/standard/base-types/best-practices-strings
Update:
This intrigued me, so I went back and done some tests.
I generated 10,000 rows of random(ish) data in two datatables where every second row would match and executed your comparison vs a simplified for loop comparison with a String comparison like this:
for (int i = 0; i < dt1.Rows.Count; i++)
{
if (dt1.Rows[i]["N"].ToString().Equals(dt2.Rows[i]["N"].ToString(), StringComparison.OrdinalIgnoreCase)
&& dt1.Rows[i][columnName].ToString().Equals(dt2.Rows[i][columnName].ToString(), StringComparison.OrdinalIgnoreCase))
{
dtDifference.Rows.Add(dt1.Rows[i].ItemArray);
}
}
Your code = 66,000ms -> 75,000ms
For loop code = 12ms -> 20ms
A significant difference!
Then I did a comparison using the for loop method but with the two different string comparison types for the string. Using my string comparison, vs yours. But I had to test on 1 million rows for this, to get a significant difference.
This differend by between 200ms and 800ms
So it seems in this case that the string comparison is not a major factor.
So it seems that your Linq query creating the datarows is what is taking the majority of time and not the comparison of the rows themselves.
So switch to using the for loop, and all will be well in the world again!
Compare Two DataTables for Non-Matching Records C#
Using LINQ would be most natural, but you will need to convert away from the DataTable to use Except
.
var In_dt1_only = dt1.AsEnumerable().Select(r => new { first = r.Field<string>("First"), last = r.Field<string>("Last")}).Except(dt2.AsEnumerable().Select(r => new { first = r.Field<string>("First"), last = r.Field<string>("Last")}));
If you need the original DataRow
s, you can use a Where
instead:
var datarows_in_dt1_only = dt1.AsEnumerable().Where(dr1 => !dt2.AsEnumerable().Any(dr2 => dr1.Field<string>("First") == dr2.Field<string>("First") && dr1.Field<string>("Last") == dr2.Field<string>("Last")));
Related Topics
How to Override an Existing Extension Method
Creating Dynamic Queries with Entity Framework
How to Accept an Array as an ASP.NET MVC Controller Action Parameter
How to Determine for Which Platform an Executable Is Compiled
Capture the Screen Shot Using .Net
Why Is the "F" Required When Declaring Floats
Why Does (Does It Really) List<T> Implement All These Interfaces, Not Just Ilist<T>
What's the New C# Await Feature Do
Export Datatable to Excel File
Performance of Calling Delegates VS Methods
HTML Agility Pack Strip Tags Not in Whitelist
Differencebetween a Property and a Variable
Failed to Serialize the Response in Web API
Create Bitmap from a Byte Array of Pixel Data