CODEWITHSUNDEEP
c#
GrammatonCleric
GrammatonCleric

Reputation: 211

Compare Rows in DataTable

I have 2 DataTables that looks like this:

YesterdaysTable  
AAA  
BBB  
CCC  
DDD  
EEE  
FFF  
GGG  
HHH  
III  
JJJ  

TodaysTable  
AAA  
BBB  
DDD  
FFF  
GGG  
HHH  
III  
JJJ

As you can see, TodaysTable does not contain CCC & EEE. When I try to find the differences to let me know what's in YesterdaysTable that does not exist in TodaysTable using the following code, I get an error:

InvalidOperationException: the source contains no DataRows

but there are rows in my source:

DataTable dtSource = new DataTable();
dtSource = yesterdaysTable.AsEnumerable().Except(todaysTable.AsEnumerable()).CopyToDataTable();

var difference = yesterdaysTable.AsEnumerable().Select(r => r.Field<string>("Apps")).Except(todaysTable.AsEnumerable().Select(r => r.Field<string>("Apps")));

if (difference.Any())
{
    DataTable Table3 = (from row in todaysTable.AsEnumerable()
                        join app in difference on row.Field<string>("Apps") equals app
                        select row).CopyToDataTable();
}

I get the same error when I use similar code:

var appsNotInB = yesterdaysTable.AsEnumerable().Select(r => r.Field<string>("Apps"))
        .Except(todaysTable.AsEnumerable().Select(r => r.Field<string>("Apps")));

var TableC = (from row in yesterdaysTable.AsEnumerable()
              join id in appsNotInB on row.Field<string>("Apps") equals id
              select row).CopyToDataTable();

I went as far as trying to write a nested for-loop to try to put the differences into a List<string> object, to no avail:

for (int i = 0; i < yesterdaysTable.Rows.Count; i++)
{
    for (int j = 0; j < todaysTable.Rows.Count; j++)
    {
        if (yesterdaysTable.Rows[i]["Apps"].ToString() != todaysTable.Rows[j]["Apps"].ToString())
        {
            //lstNoMatch.Add(yesterdaysTable.Rows[i]["Apps"].ToString());
            lstNoMatch.Add(todaysTable.Rows[j]["Apps"].ToString());
        }
    }
}

I've tried a few different solutions but nothing is working and I don't want the post to be unnecessarily long. I've hit a dead-end :(

Any suggestions?

Upvotes: 0

Views: 900

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460108

CopyToDataTable throws this exception if the query didnt return any rows. One reason why i avoid that method. You could avoid the exception with pre-checking with query.Any(). But that executes it twice. So a foreach to add the rows is to be preferred.

I try to find the differences to let me know what's in YesterdaysTable that does not exist in TodaysTable

Then this query does the job (you've done this correctly):

var onlyYesterdayApps = yesterdaysTable.AsEnumerable()
    .Select(r => r.Field<string>("Apps"))
    .Except(todaysTable.AsEnumerable().Select(r => r.Field<string>("Apps")));

If you want the rows in a third table you have to use YesterdaysTable as source:

DataTable result = yesterdaysTable.Clone(); // empty table with same columns
var yesterdayRows = from r in yesterdaysTable.AsEnumerable()
                    join app in onlyYesterdayApps
                    on r.Field<string>("Apps") equals app
                    select r;
foreach(DataRow row in yesterdayRows)
    result.ImportRow(row); // import rows if the query above yielded rows

Just the final foreach will execute the two queries(which are actually merged to one).

Upvotes: 1

Related Questions