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!

SLaks · Accepted Answer · 2010-03-07 16:32:40Z

4

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)

answered Mar 7, 2010 at 16:32

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saravanan Over a year ago

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 Over a year ago

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

Thomas Levesque · Accepted Answer · 2010-03-07 16:49:19Z

3

A bit cleaner, using the table columns :

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

answered Mar 7, 2010 at 16:49

Thomas Levesque

294k73 gold badges639 silver badges769 bronze badges

2 Comments

user1228 Over a year ago

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 Over a year ago

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)

user259286 · Accepted Answer · 2010-03-07 19:03:12Z

1

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;

answered Mar 7, 2010 at 19:03

user259286

1,0154 gold badges20 silver badges39 bronze badges

Collectives™ on Stack Overflow

How to Filter out null rows from DataTable with linq?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related