CODEWITHSUNDEEP
c#asp.netlinqentity-frameworkdatatable
Luis Valencia
Luis Valencia

Reputation: 34028

Remove records from one datatable based on records from another datable

I have the following results in the first datatable:

Datatable with uploaded documents

clientcode    jobcode    joburl                          DocumentLink
1             1          http://ourlocaldomain.com       http://ourlocaldomain.com/documents/1.pdf
1             2          http://ourlocaldomain.com       http://ourlocaldomain.com/documents/2.pdf

Datatable with all client and jobs regardless if the document is uploaded or not. (the document link column is not present on this second datable)

clientcode    jobcode    joburl                          
1             1          http://ourlocaldomain.com       
1             2          http://ourlocaldomain.com       
1             3          http://ourlocaldomain.com    
1             4          http://ourlocaldomain.com

My 3rd datatable should return all records from Datatable2 which are not in datatable 1, BASED on a matching record with client code and jobcode.

I suppose this should be able to do with LINQ somehow but clueless where to start digging:

the code I have is:

var keywordQueryAllInfoLists = new KeywordQuery(site);
dataTableAllInfoLists = KQLUtilities.ExecuteKql(keywordQueryAllInfoLists, queryTextAllInfoLists, selectedProperties.ToArray(), keywordQueryAllInfoLists.SortList);

var keywordQuery = new KeywordQuery(site);
dataTableAllEngagementLetters = KQLUtilities.ExecuteKql(keywordQuery, queryTextAllLetterOfEngagement, selectedProperties.ToArray(), keywordQuery.SortList);

EDIT I have tried the following:

resultingDatatable = dataTableAllEngagementLetters.Select()
                    .Where(x => !dataTableAllInfoLists.Select(string.Format("ClientCode = {0} and JobCode = {1}", x["ClientCode"], x["JobCode"]))
                    .Any())
                    .CopyToDataTable();


resultingDatatable = dataTableAllEngagementLetters.Select()
                    .Where(x => !dataTableAllInfoLists.Select(string.Format("ClientCode = [{0}] and JobCode = [{1}]", x["ClientCode"], x["JobCode"]))
                    .Any())
                    .CopyToDataTable();

These 2 throw me the following exception: http://screencast.com/t/HWLZTOJEn8T

  resultingDatatable = dataTableAllEngagementLetters.Select()
                        .Where(x => !dataTableAllInfoLists.Select(string.Format("ClientCode = '{0}' and JobCode = '{1}'", x["ClientCode"], x["JobCode"]))
                        .Any())
                        .CopyToDataTable();

And this last one tells me there are to rows on the source!

Upvotes: 0

Views: 129

Answers (1)

Renatas M.
Renatas M.

Reputation: 11820

Not very nice but it's the first solution that came in my mind:

DataTable dataTable3 = dataTable2.Select().Where(x => dataTable1.Select(string.Format("clientCode = '{0}' and jobCode = '{1}'", x["clientCode"], x["jobCode"])).Count() == 0).CopyToDataTable();

EDIT

Count == 0 you can change to !Any(). So should look like this: 

DataTable dataTable3 = dataTable2.Select().Where(x => !dataTable1.Select(string.Format("clientCode = '{0}' and jobCode = '{1}'", x["clientCode"], x["jobCode"])).Any()).CopyToDataTable();

Upvotes: 1

Related Questions