“指定的转换无效”在LINQ当DateTime字段为空时 [英] "Specified cast is not valid" in LINQ when DateTime field is null

问题描述

我遇到了一个问题。这个我的代码使用LINQ。

  var resultCases = from dt2中的row2AsEnumerable（）
其中row2.Field< ; int>（caseID）== caseID2 
选择新
 {
 caseName = row2 [caseName] ToString（），
 caseCourtPlace = row2 [caseCourtPlace ] ToString（），
 caseCourtRef = row2 [caseCourtRef] ToString（），
 caseOfficeRef = row2 [caseOfficeRef] ToString（），
 effectiveDate =（（DateTime） row2 [caseEffectiveDate]），
 closedDate =（（DateTime）row2 [caseClosedDate]），
 caseFolderPath = row2 [casesFolderPath] ToString（），
 category = row2 [categoryName] ToString（），
 department = row2 [departmentName] ToString（），
 toString（），
判断名称= row2 [判断名称] ToString（），
 asName = row2 [asCasesName] ToString（），
}; 
  

如果 closedDate 或有效日期返回 DBnull.Value ，我得到

InvalidCastException未处理 - 指定的转换无效。

那么如何防止此错误？ / p>

解决方案

tldr; 问题是双重的：DataTable使用 DBNull.Value 表示null值，既不是 DBNull.Value 或 null 可转换为 DateTime 。

字段< T> 扩展方法稍后添加，使得处理具有空值的DBNull和Nullable /引用类型更容易;它也隐藏了强类型签名后的转换。这些 LINQ to DataSet 扩展方法知道如何将 DBNull.Value 映射到 null

因为这样使用 row.Field< DateTime？> caseEffectiveDate）将返回 DateTime？，其值（如果查询返回值）或 null ，如果服务器返回不兼容的值，它也可能会引发异常 - 但是它将永远不会返回 DBNull.Value 。

但是，标准的行[caseEffectiveDate] 将返回一个 DateTime value（或其他类型）或 DBValue.Null 这是不到 DateTime？（更不用说一个 DateTime ），并导致描述的错误。

这里是如何最小化重现这个例外：

 对象v = DBNull.Value; 
 DateTime dt =（DateTime）v; 
  

但是，转换 null to a DateTime？：

  object v = null; 
 DateTime？ maybeDt =（DateTime？）v; 
  

然后轻松将它合并到 DateTime 如果需要：

 对象v = null; 
 DateTime？ dt =（DateTime？）v ?? DateTime.MinValue; 
  

I got a problem. This my code use a LINQ.

var resultCases = from row2 in dtCases.AsEnumerable()
                  where row2.Field<int>("caseID") == caseID2
                  select new
                  {
                              caseName = row2["caseName"].ToString(),
                              caseCourtPlace = row2["caseCourtPlace"].ToString(),
                              caseCourtRef = row2["caseCourtRef"].ToString(),
                              caseOfficeRef = row2["caseOfficeRef"].ToString(),
                              effectiveDate = ((DateTime)row2["caseEffectiveDate"]),
                              closedDate = ((DateTime)row2["caseClosedDate"]),
                              caseFolderPath = row2["casesFolderPath"].ToString(),
                              category = row2["categoryName"].ToString(),
                              department = row2["departmentName"].ToString(),
                              empName = row2["empName"].ToString(),
                              judgeName = row2["judgeName"].ToString(),
                              asName = row2["asCasesName"].ToString(),
                  };

If closedDate or effectiveDate return DBnull.Value, I get

InvalidCastException was unhandled - Specified cast is not valid.

So how can I prevent this error?

解决方案

tldr; The issue is two-fold: DataTable uses DBNull.Value to represent "null" values and neither DBNull.Value or null are castable to DateTime.

The Field<T> extension method was added later to make dealing with DBNull and Nullable/reference types with null values much easier; it also hides the conversion behind a strongly-typed signature. These LINQ to DataSet extension method knows how to map DBNull.Value to null as appropriate.

Because of this using row.Field<DateTime?>("caseEffectiveDate") will return either the DateTime? with a value (if the query returned a value) or null, and it may also throw an exception if the server returned an incompatible value - but it will never return DBNull.Value.

However, the standard row["caseEffectiveDate"] will return a DateTime value (or some other type) or DBValue.Null which is not cast-able to DateTime? (much less a DateTime) and results in the error described.

Here is how to minimally reproduce this exception:

object v = DBNull.Value;
DateTime dt = (DateTime)v;

However, it is fine to convert a null to a DateTime?:

object v = null;
DateTime? maybeDt = (DateTime?)v;

And then trivial to coalesce it away to DateTime if required:

object v = null;
DateTime? dt = (DateTime?)v ?? DateTime.MinValue;

这篇关于“指定的转换无效”在LINQ当DateTime字段为空时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！