How to Filter out null rows from DataTable with linq??

Question

Hi,

I have a Datable that I have built from excel data, but sometimes excel returns rows in which all the fields are null.

I'd like to filter these out generically without regard for column names.

I think Linq would do this nicely but having a bit of trouble getting this to happen.

So far this is what I got:

 var nonemptyrows = from r in table.AsEnumerable()
                                              from f in r.ItemArray
                                                  where f != null
                                                     select r;

But it's not quite there. Anyone see what I'm missing or if I'm on the wrong track?

Thanks in advance!

Answer 1

Like this:

table.AsEnumerable()
     .Where(r => r.ItemArray.Any(v => v != null && v != DBNull.Value))

Or, using query comprehension syntax:

from r in table.AsEnumerable()
where r.ItemArray.Any(v => v != null && v != DBNull.Value)

Answer 2

A bit cleaner, using the table columns :

var nonemptyrows = from row in table.AsEnumerable()
                   where table.Columns.Any(col => !row.IsNull(col))
                   select row;

Answer 3

Thanks for your reply! right after I posted, this idea below hit me and it worked for me:

var nonemptyrows = from r in table.Rows.Cast<DataRow>() where r.ItemArray.All(c => c != DBNull.Value) select r;