VB.NET使用LINQ时Decimal转Nullable可空类型失败问题求解

数据老陈

2026-8-30

问题描述

在VB.NET环境下使用LINQ编写双DataTable关联查询逻辑时，出现Decimal类型到Nullable可空类型的转换失败问题，尝试多种实现方式均未解决。

原实现代码

Dim data = From row1 In dt1.AsEnumerable()
           Join row2 In dt2.AsEnumerable()
           On row1.Field(Of String)("BND_ID") Equals row2.Field(Of String)("BND_ID")
           Where (row1.Field(Of Nullable)("UST_YTM").Equals(row2.Field(Of Nullable)("UST_YTM")) = False And 
                  row1.Field(Of Nullable)("CRT_UST_YTM").Equals(row2.Field(Of Nullable)("CRT_UST_YTM")) = False And 
                  row1.Field(Of String)("ISSU_ORG_NM") <> row2.Field(Of String)("ISSU_ORG_NM") And 
                  row1.Field(Of String)("PRC_CALC_DT") <> row2.Field(Of String)("PRC_CALC_DT") And 
                  row1.Field(Of String)("IS_HOLI") <> row2.Field(Of String)("IS_HOLI") And 
                  row1.Field(Of String)("ISSU_DT") <> row2.Field(Of String)("ISSU_DT") And 
                  row1.Field(Of String)("EXR_DT") <> row2.Field(Of String)("EXR_DT") And 
                  row1.Field(Of String)("TERM") <> row2.Field(Of String)("TERM") And 
                  row1.Field(Of String)("CRT_TERM") <> row2.Field(Of String)("CRT_TERM") And
                  row1.Field(Of Nullable)("CPN_RT").Equals(row2.Field(Of Nullable)("CPN_RT")) = False And
                  row1.Field(Of Nullable)("BLM_ISSU_SP").Equals(row2.Field(Of Nullable)("BLM_ISSU_SP")) = False And
                  row1.Field(Of Nullable)("BLM_ROFR_SP").Equals(row2.Field(Of Nullable)("BLM_ROFR_SP")) = False And
                  row1.Field(Of Nullable)("NICE_SP").Equals(row2.Field(Of Nullable)("NICE_SP")) = False And
                  row1.Field(Of Nullable)("ISSU_SP").Equals(row2.Field(Of Nullable)("ISSU_SP")) = False)            
           Select New With {
               .BND_ID = row1.Field(Of String)("BND_ID"),
               .UST_YTM = row1.Field(Of Nullable)("UST_YTM"),
               .CRT_UST_YTM = row1.Field(Of Nullable)("CRT_UST_YTM"),
               .ISSU_ORG_NM = row1.Field(Of String)("ISSU_ORG_NM"),
               .PRC_CALC_DT = row1.Field(Of String)("PRC_CALC_DT"),
               .IS_HOLI = row1.Field(Of String)("IS_HOLI"),
               .ISSU_DT = row1.Field(Of String)("ISSU_DT"),
               .EXR_DT = row1.Field(Of String)("EXR_DT"),
               .TERM = row1.Field(Of String)("TERM"),
               .CRT_TERM = row1.Field(Of Decimal)("CRT_TERM"),
               .CPN_RT = row1.Field(Of Nullable)("CPN_RT"),
               .BLM_ISSU_SP = row1.Field(Of Nullable)("BLM_ISSU_SP"),
               .BLM_ROFR_SP = row1.Field(Of Nullable)("BLM_ROFR_SP"),
               .NICE_SP = row1.Field(Of Nullable)("NICE_SP"),
               .ISSU_SP = row1.Field(Of Nullable)("ISSU_SP")
           }

错误原因

泛型类型参数使用错误：Nullable是开放式泛型类型，必须指定具体的底层值类型才能使用，代码中直接写Field(Of Nullable)没有声明Nullable对应的基础类型是Decimal，编译器无法完成类型匹配，自然抛出转换异常。
值比较逻辑存在隐患：直接调用可空类型的.Equals() = False判断不等，在值为Null（对应数据库DBNull）时容易出现非预期的比较结果。

修复方案

将所有数值类可空字段的泛型参数明确指定为Nullable(Of Decimal)（VB中可简写为Decimal?），同时换用Object.Equals()方法做值比较，避免空值判断异常。
修复后的核心代码如下：

Dim data = From row1 In dt1.AsEnumerable()
           Join row2 In dt2.AsEnumerable()
           On row1.Field(Of String)("BND_ID") Equals row2.Field(Of String)("BND_ID")
           Where (Not Object.Equals(row1.Field(Of Decimal?)("UST_YTM"), row2.Field(Of Decimal?)("UST_YTM")) And 
                  Not Object.Equals(row1.Field(Of Decimal?)("CRT_UST_YTM"), row2.Field(Of Decimal?)("CRT_UST_YTM")) And 
                  row1.Field(Of String)("ISSU_ORG_NM") <> row2.Field(Of String)("ISSU_ORG_NM") And 
                  row1.Field(Of String)("PRC_CALC_DT") <> row2.Field(Of String)("PRC_CALC_DT") And 
                  row1.Field(Of String)("IS_HOLI") <> row2.Field(Of String)("IS_HOLI") And 
                  row1.Field(Of String)("ISSU_DT") <> row2.Field(Of String)("ISSU_DT") And 
                  row1.Field(Of String)("EXR_DT") <> row2.Field(Of String)("EXR_DT") And 
                  row1.Field(Of String)("TERM") <> row2.Field(Of String)("TERM") And 
                  row1.Field(Of String)("CRT_TERM") <> row2.Field(Of String)("CRT_TERM") And
                  Not Object.Equals(row1.Field(Of Decimal?)("CPN_RT"), row2.Field(Of Decimal?)("CPN_RT")) And
                  Not Object.Equals(row1.Field(Of Decimal?)("BLM_ISSU_SP"), row2.Field(Of Decimal?)("BLM_ISSU_SP")) And
                  Not Object.Equals(row1.Field(Of Decimal?)("BLM_ROFR_SP"), row2.Field(Of Decimal?)("BLM_ROFR_SP")) And
                  Not Object.Equals(row1.Field(Of Decimal?)("NICE_SP"), row2.Field(Of Decimal?)("NICE_SP")) And
                  Not Object.Equals(row1.Field(Of Decimal?)("ISSU_SP"), row2.Field(Of Decimal?)("ISSU_SP")))            
           Select New With {
               .BND_ID = row1.Field(Of String)("BND_ID"),
               .UST_YTM = row1.Field(Of Decimal?)("UST_YTM"),
               .CRT_UST_YTM = row1.Field(Of Decimal?)("CRT_UST_YTM"),
               .ISSU_ORG_NM = row1.Field(Of String)("ISSU_ORG_NM"),
               .PRC_CALC_DT = row1.Field(Of String)("PRC_CALC_DT"),
               .IS_HOLI = row1.Field(Of String)("IS_HOLI"),
               .ISSU_DT = row1.Field(Of String)("ISSU_DT"),
               .EXR_DT = row1.Field(Of String)("EXR_DT"),
               .TERM = row1.Field(Of String)("TERM"),
               .CRT_TERM = row1.Field(Of Decimal)("CRT_TERM"),
               .CPN_RT = row1.Field(Of Decimal?)("CPN_RT"),
               .BLM_ISSU_SP = row1.Field(Of Decimal?)("BLM_ISSU_SP"),
               .BLM_ROFR_SP = row1.Field(Of Decimal?)("BLM_ROFR_SP"),
               .NICE_SP = row1.Field(Of Decimal?)("NICE_SP"),
               .ISSU_SP = row1.Field(Of Decimal?)("ISSU_SP")
           }

注意：如果对应字段实际存储的是Double类型，需要将Decimal?替换为Double?，避免二次转换错误。

内容的提问来源于stack exchange，提问作者jmlee