Filer DataTable排除DBNull.Value

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

我从excel文件收到一个DataTable,第一列中的数据如下所示:

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("F1")).Distinct().ToArray();

我正在使用LINQ来选择distict,如果没有DBNull.Value值,它可以工作。 albumIds = dt.AsEnumerable()。选择(p =>(int)p.Field(“F1”))。Distinct()。ToArray();

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

但是如果出现DBNull.Value代表空的excel单元格,我会得到转换错误。

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

如何从结果集中过滤掉那些DBNull.Value?

Thank you

5 个解决方案

#1

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...

正如Jeremy的回答中暗示的那样,如果您对System.Data.DataSetExtensions.dll有一个引用,那么您将获得一些方便的扩展方法,以便使用LINQ处理DataSet和DataTables。具体来说,您可以使用Field ()方法将可能包含DBNull的整数列转换为可为空的int列... ?>

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

#2

You just need to filter the null values out first. Then your LINQ expression should work great.

您只需要先过滤掉空值。然后你的LINQ表达式应该很好用。

#3

You need to check the fields for DBNull before you attempt the conversion using the Where method is probably easiest.

在使用Where方法可能最简单的尝试转换之前,您需要检查DBNull的字段。

dt.AsEnumerable().Where(p => p.Field("F1") != DBNull.Value).Select(p => (int)p.Field("F1")).Distinct().ToArray();

#4

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

我无法检查这个,因为我没有这个数据库设置

#5

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

而不是使用Linq我执行foreach循环并删除行,如果value为null。然后做Linq明显

            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();

#1

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

#2