CODEWITHSUNDEEP
c#exceptiondatetime
Azri Zakaria
Azri Zakaria

Reputation: 1364

"Specified cast is not valid" in LINQ when DateTime field is null

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?

Upvotes: 1

Views: 5231

Answers (2)

user166390
user166390

Reputation:

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;

Upvotes: 5

armen.shimoon
armen.shimoon

Reputation: 6401

Update: try this

public static class ConversionExtensions
{
    public static string SafeDateTime(this object value)
    {
        if (value == DBNull.Value) return string.Empty;

        var valueType = value.GetType();
        if (typeof (DateTime?).IsAssignableFrom(valueType))
        {
            var nullableValue = value as DateTime?;
            if (nullableValue == null) return string.Empty;
            return nullableValue.Value.ToString();
        }
        if (typeof (DateTime).IsAssignableFrom(valueType))
        {
            return ((DateTime) value).ToString();
        }
        return string.Empty;
    }
}

Called with: 

effectiveDate = row2["caseEffectiveDate"].SafeDateTime()

Upvotes: 0

Related Questions