InvalidCastException for nullable DateTimes and nullable custom types

[hrichardlee]

Repro steps:

in Source\Samples\Registration.Sample\ParameterConversionExamples.cs, add this function in the ParameterConversionExamples class

        [ExcelFunction] public static string dnaNullableOptionalDateTime(DateTime? val = null) => val.HasValue ? "VAL: " + val : "NULL";

Then in Excel, if you call =dnaNullableOptionalDateTime(43101) the result is:

!!! ERROR:   System.InvalidCastException: Specified cast is not valid.             at   dnaNullableOptionalDateTime(Closure , Object )             at WrapperType.Wrapped_f32_Invoke(Object   , Object )
--

I would have expected "1-Jan-18", which is what you get if call =dnaDirectDateTime(43101). Also, =dnaNullableOptionalDateTime() returns "NULL" as expected.

The same thing happens with custom nullable structs. If you add this type to ParameterConversionExamples.cs

public struct TestStruct {
        public string _value;
        public TestStruct(string value) => _value = value;
        public override string ToString() => $"TestStruct:{_value}";
    }

and add these test functions in the ParameterConversionExamples class:

        [ExcelFunction] public static string dnaTestStruct(TestStruct s) => s.ToString();
        [ExcelFunction] public static string dnaNullableTestStruct(TestStruct? s) => s.HasValue ? s.Value.ToString() : "NULL";
        [ExcelFunction] public static string dnaNullableOptionalTestStruct(TestStruct? s = null) => s.HasValue ? s.Value.ToString() : "NULL";

and add a conversion to ExampleAddIn.GetParameterConversionConfig:

.AddParameterConversion((string value) => new TestStruct(value))

Then you get similarly unexpected results: =dnaNullableOptionalTestStruct("hey") returns that same InvalidCastException.

Versions

I reproduced this with the current HEAD of ExcelDNA.Registration (27bc193), Excel 1812 (Build 11126.20196 Click-to-Run) on Windows 10.

Impact

We're currently on an ancient version of ExcelDNA.CustomRegistration (i.e. from before the rename). We can't migrate to the latest version of ExcelDNA.Registration because we have functions that take DateTime? parameters and other nullable parameters (using a ParameterConversion specific to DateTime?). I.e. our ParameterConversionConfig currently looks like:

                .AddParameterConversion(ParameterConversions.GetOptionalConversion(treatEmptyAsMissing: true))
                .AddParameterConversion(ParameterConversions.GetNullableConversion(true))
                .AddParameterConversion((object o) => ConvertAnyObjectToNullableDateTime(o));

In other words, we were previously implementing our own custom logic for nullable custom types. This also no longer works with the latest version, and fails with

System.NullReferenceException: Object reference not set to an instance of an object.
   at 
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.GetParameterConversions(ExcelDna.Registration.ParameterConversionConfiguration conversionConfig, System.Type initialParamType, ExcelDna.Registration.ExcelParameterRegistration paramRegistration) Line 72	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.GetParameterConversion(ExcelDna.Registration.ParameterConversionConfiguration conversionConfig, System.Type initialParamType, ExcelDna.Registration.ExcelParameterRegistration paramRegistration) Line 62	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversions.NullableConversion(ExcelDna.Registration.ParameterConversionConfiguration config, System.Type type, ExcelDna.Registration.ExcelParameterRegistration paramReg, bool treatEmptyAsMissing, bool treatNAErrorAsMissing) Line 51	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversions.GetNullableConversion.AnonymousMethod__0(System.Type type, ExcelDna.Registration.ExcelParameterRegistration paramReg) Line 28	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionConfiguration.ParameterConversion.Convert(System.Type paramType, ExcelDna.Registration.ExcelParameterRegistration paramReg) Line 45	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.GetParameterConversions(ExcelDna.Registration.ParameterConversionConfiguration conversionConfig, System.Type initialParamType, ExcelDna.Registration.ExcelParameterRegistration paramRegistration) Line 74	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.<ProcessParameterConversions>d__0.MoveNext() Line 26	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.AsyncRegistration.<ProcessAsyncRegistrations>d__0.MoveNext() Line 36	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.<ProcessParameterConversions>d__0.MoveNext() Line 15	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ParamsRegistration.<ProcessParamsRegistrations>d__0.MoveNext() Line 19	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.FunctionExecutionRegistration.<ProcessFunctionExecutionHandlers>d__0.MoveNext() Line 13	C#
 	ExcelDna.Registration.dll!ExcelDna.Registration.ExcelRegistration.RegisterFunctions(System.Collections.Generic.IEnumerable<ExcelDna.Registration.ExcelFunctionRegistration> registrationEntries) Line 44	C#
 	Registration.Sample!Registration.Sample.ExampleAddIn.AutoOpen() Line 26	C#

I'm only including this in case the additional information is helpful--I don't need this bug to be fixed, because I'm happy to switch to the new way of using ParameterConversionConfiguration.AddNullableConversion.