LINQ CopyToDataTable -- 不能将 DBNull.Value 转换为类型"System.Decimal"。请使用可为空的类型

Question

我正在解析两个excel文件，其中包含许多字段，最重要的是total_dollars和total_units字段，通常具有值，但其中有些是空白或空的。我想使用UPC字段相等的LINQ进行JOIN这两个数据表。但是，我遇到了以下错误：

Cannot cast DBNull.Value to type 'System.Decimal'. Please use a nullable type

由于空字段，我尝试加入两个表时会发生此错误。

我的LINQ代码如下：

Dim query = From c In dtDollars.AsEnumerable() _
      Join r In dtUnits.AsEnumerable() _
      On c.Field(Of String)("UPC") Equals r.Field(Of String)("UPC") _
      Select _
      New _
      With {.UPC = r.Field(Of String)("UPC"), .WIC_NUMBER = c.Field(Of String)("WIC_NUMBER"), _
      .WAG_ITEM_DESC = c.Field(Of String)("WAG_ITEM_DESC"), _
      .WAG_BRAND = c.Field(Of String)("WAG_BRAND"), .UOM = c.Field(Of String)("UOM"), _
      .GSK_PROMO_GRP = c.Field(Of String)("GSK_PROMO_GRP"), _
      .TOTAL_DOLLARS = c.Field(Of Decimal)("TOTAL_DOLLARS"), _
      .TOTAL_UNITS = r.Field(Of Integer)("TOTAL_UNITS"), _
      .WKND_DATE = c.Field(Of DateTime)("WKND_DATE")}

Answer 1

鉴于wknd_date也可以为null，以Nullable(Of DateTime)（DateTime?）为单位，例如：

Dim query = From c In dtDollars.AsEnumerable() _
   Join r In dtUnits.AsEnumerable() _
   On c.Field(Of String)("UPC") Equals r.Field(Of String)("UPC") _
   Select _
   New _
   With {.UPC = r.Field(Of String)("UPC"), .WIC_NUMBER = c.Field(Of String)("WIC_NUMBER"), _
   .WAG_ITEM_DESC = c.Field(Of String)("WAG_ITEM_DESC"), _
   .WAG_BRAND = c.Field(Of String)("WAG_BRAND"), .UOM = c.Field(Of String)("UOM"), _
   .GSK_PROMO_GRP = c.Field(Of String)("GSK_PROMO_GRP"), _
   .TOTAL_DOLLARS = c.Field(Of Decimal?)("TOTAL_DOLLARS"), _
   .TOTAL_UNITS = r.Field(Of Integer?)("TOTAL_UNITS"), _
   .WKND_DATE = c.Field(Of DateTime?)("WKND_DATE")}