CODEWITHSUNDEEP
c#exceloledb
codingbiz
codingbiz

Reputation: 26396

LINQ on Datatable Find where all rows are empty

I have a code reading data from excel spreadsheet and I have gone this far with some answers on SO

DataTable dt = ds.Tables[0];
dt = dt.AsEnumerable().Where((row, index) => index > 4).CopyToDataTable();
DataTable filteredRows = dt.Rows.Cast<DataRow>().Where(row => row.ItemArray.All(field => !(field is System.DBNull))).CopyToDataTable();

having this 

dt.Rows.Cast<DataRow>().Where(row => row.ItemArray.All(field => (field is System.DBNull)))

returns all rows that are empty.

I have also tried Any, it didn't give the required output

The code above works for where all the fields are not NULL i.e. every columns has a field. This exempt all rows that have 1 column missing but that's not what I want.

I want to exempt all rows that have all columns empty.

Upvotes: 2

Views: 12819

Answers (2)

Servy
Servy

Reputation: 203830

Just move the NOT (!) out one level. You want the items where "all rows are null" is not true, rather than where "all of the rows are not null" is true.

DataTable filteredRows = dt.Rows.Cast<DataRow>()
    .Where(row => !row.ItemArray.All(field => field is System.DBNull))
    .CopyToDataTable();

Upvotes: 6

S Grimminck
S Grimminck

Reputation: 528

Have you tried filtering to Any Field instead of All?

DataTable filteredRows = dt.Rows.Cast<DataRow>().Where(row => row.ItemArray.Any(field => !(field is System.DBNull))).CopyToDataTable();

Upvotes: 1

Related Questions