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
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();
Can you try this:
dt.AsEnumerable().Where(p => p.IsNull("F1") == false)
.Select(p => p.Field<int>("F1")).Distinct().ToArray();
I can't really check this as I don't have this DB setup
You need to check the fields for DBNull before you attempt the conversion using the Where method is probably easiest.
dt.AsEnumerable().Where(p => p.Field("F1") != DBNull.Value).Select(p => (int)p.Field("F1")).Distinct().ToArray();
I'll answer my own question.
Instead of using Linq I do a foreach loop and delete rows if value is null. and then do Linq distinct
foreach(DataRow row in dt.Rows)
{
if (String.IsNullOrEmpty(row["F1"].ToString()))
row.Delete();
}
dt.AcceptChanges();
albumIds = dt.AsEnumerable().Select(p => (int)p.Field<double>("F1")).Distinct().ToArray();