"指定的转换是无效的"在LINQ,当日期时间字段为空 [英] "Specified cast is not valid" in LINQ when DateTime field is null
问题描述
我有一个问题。这是我的代码使用LINQ。
VAR resultCases =从ROW2在dtCases.AsEnumerable()
,其中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(),
类= ROW2 [类别名称]。的ToString(),
部门= ROW2 [DEPARTMENTNAME]。的ToString(),
empName = ROW2 [empName]。的ToString(),
judgeName = 。ROW2 [judgeName]的ToString(),
asName = ROW2 [asCasesName]的ToString(),
}。
如果 closedDate
或 EFFECTIVEDATE
收益 DBnull.Value
,我得到
的 InvalidCastException的是未处理 - 指定的转换是无效的
块引用>
那么,如何才能防止这种误差<? / p>
解决方案tldr; 的问题是双重的:DataTable中使用
DBNull.Value
来表示空的价值观既不DBNull.Value
或空
可浇铸到的DateTime
。
借助
字段< T>
一>以后增加了扩展方法,使得它们同为DBNull和可空/引用类型空值容易得多;它也隐藏了一个强类型的签名背后的转换。这些 LINQ到DataSet中扩展方法的知道如何映射DBNull.Value
到空$ C 。$ C>适当
由于这个使用
row.Field<&日期时间GT(caseEffectiveDate)
将返回的或者的的的DateTime?
用一个值(如果该查询返回的值)或空
,它也可能会抛出一个异常,如果服务器返回一个不兼容的价值 - 但它会的从不的回报DBNull.Value
。
行[caseEffectiveDate] 将返回
日期时间>
值(或其他类型)或DBValue.Null
是的不的铸能够日期时间?
(更不用说的DateTime
)和结果中描述的错误。
下面是如何重现最小此异常:
对象v = DBNull.Value;
DateTime的DT =(DateTime的)V;
不过,细到转换
空
到的DateTime
:对象v = NULL;
日期时间? maybeDt =(DateTime的?)V;
和再平凡凝聚它而去
的DateTime
如果需要的话:对象v = NULL;
日期时间? 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
oreffectiveDate
returnDBnull.Value
, I getInvalidCastException 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 neitherDBNull.Value
ornull
are castable toDateTime
.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 mapDBNull.Value
tonull
as appropriate.Because of this using
row.Field<DateTime?>("caseEffectiveDate")
will return either theDateTime?
with a value (if the query returned a value) ornull
, and it may also throw an exception if the server returned an incompatible value - but it will never returnDBNull.Value
.However, the standard
row["caseEffectiveDate"]
will return aDateTime
value (or some other type) orDBValue.Null
which is not cast-able toDateTime?
(much less aDateTime
) 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 aDateTime?
: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,当日期时间字段为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!