Filer DataTable to exclude DBNull.Value

Question

I receive a DataTable from excel file and data in the first Column looks like this:

11129

DBNull.Value

29299

29020

DBNull.Value

29020

I'm using LINQ to select distict and it works if there are no DBNull.Value values as below.

albumIds = dt.AsEnumerable().Select(p => (int)p.Field<double>("F1")).Distinct().ToArray();

But if DBNull.Value present which represent empty excel cells I get conversion errors.

How do I filter out those DBNull.Value from my result set?

Thank you

Solution

As hinted at in Jeremy's answer, If you've a reference to System.Data.DataSetExtensions.dll, you'll get some handy extension methods for working with DataSets and DataTables using LINQ. Specifically, you can use the Field<int?>() method to convert an integer column that might contain DBNull into a column of nullable ints...

albumIds = dt.AsEnumerable().Select(row => row.Field<int?>("F1"))
                            .Where(val => val.HasValue)
                            .Select(val => val.Value)
                            .Distinct()
                            .ToArray();

Comments

[Sergey]

Yea so how do I filter out Null values?

[Sergey]

this won't work because with Field Extension you need to specify a type Field<T>