How to Filter out null rows from DataTable with linq?

Question

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!

Solution 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)

Solution 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;

Solution 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;

Comments

[Admin]

I usually do table.OfType<DataRow>() to both linquify the DataTable and cast the rows from object to DataRow. Not sure the behavior if one of the rows ends up being null, however.

[Thomas Levesque]

There can't be a null row in a DataTable. And you don't need OfType<DataRow>(), since items of a DataTable are always DataRows, so you can just Cast<DataRow>() (which is the same but doesn't filter on the type)

[Saravanan]

IS it not possible to ensure that 1 column alone is not null like column number 2 is only not null. My Code now is ` IEnumerable<Guid> refIds = (from datarow in dataTable.AsEnumerable() select dataRow.Field<Guid>(dataColumn.ColumnName));`. Where i get the invalid cast exception when i use the refIds.ToList().

[Saravanan]

Edit: Kindly note that IEnumerable is of type String and I am not fetching unique ids, as they cannot be null.