LINQ to SQL exception when converting nullable value to non-nullable using Convert

Question

Problem

Given the following:

class MyClass
{
    public DateTime DateTime { get; set; }
}

and a queryable, where MyTable.NullableDateTime is a DateTime? mapped to an SQL datetime:

IQueryable<MyTable> table = //something

Running the following produces an exception when the row's NullableDateTime column is NULL:

table.Select(row => new MyClass
{
    DateTime = Convert.ToDateTime(row.NullableDateTime)
}).ToArray();

The error is:

InvalidOperationException

The null value cannot be assigned to a member with type System.DateTime which is a non-nullable value type.

What I've Tried

1. Looking at the generated SQL:

   CONVERT(DateTime,[t0].[NullableDateTime]) AS [NullableDateTime]
   

2. Running the query before performing the Select. This works around the problem, but I don't want to run this on the client side just to work around the problem.

3. Using row.GetDefaultValue() instead of Convert.ToDateTime. This yields a different error:

   SqlException

   The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

   The GetDefaultValue method call is converted to the following SQL:

   COALESCE([t0].[NullableDateTime],'1/01/0001 12:00:00 AM') AS [NullableDateTime]
   

Answer 1

Probably you need to use ?? operator with some default value of DateTime (but not default(DateTime) because it's value 1/01/0001 12:00:00 AM is out of range of valid values for SQL datetime type. You probably can use SqlDateTime.MinValue.Value in right side of ??.