Invalid cast exception from generated domain method with char output parameter

[Craig]

Hi

I'm getting an Invalid Cast Exception from the generated domain method when it is processing the output parameter of my stored procedure in SQL Server 2008 R2. The domain method is generated from a stored procedure with a void result shape. Here's the stored procedure header:

Create Procedure mySp
( @parm1 smallint
, @parm2 varchar(20)
, @parm3 char(1)
, @parm4 char(1) OUTPUT
  )
begin
...

@parm4 returns either 'Y' or 'N'.



Here's the call to the stored procedure in the generated method:

   ...
   int queryResult = this.ExecuteNonQuery("[mySp]", CommandType.StoredProcedure, parameterParm1, parameterParm2, parameterParm3, parameterParm4, parameterReturnValue);

   returnValue = parameterReturnValue.Value == DBNull.Value
            ? -1
            : (int)parameterReturnValue.Value;
        parm4 = parameterParm4.Value == DBNull.Value
                ? default(System.Nullable<System.Char>)
                : (System.Nullable<System.Char>)parameterParm4.Value;

   return queryResult;
}

The stored procedure executes ok. Return value is 0 and parameterParm4.value = 'N'.

The error System.InvalidCastException was unhandled by user code Message=Specified cast is not valid is raised when the statement

(System.Nullable<System.Char>)parameterPar4.Value; is executed.



In the immediate window in VS IDE I executed the following (with results)

?(System.Nullable<System.Char>)parameterParm4.Value;
Cannot unbox 'parameterParm4.Value' as a 'char?'
?parameterParm4.Value;
"N"

Perhaps there is a better way I can do the same to avoid this issue?

OpenAccessORM is version 2013.3.1014.1.

Craig

[Ady]

Hello Craig,

 Based on some research, it seems that the value returned by the stored procedure seems to be something other than what are are trying to cast.
Are you sure the exception is thrown at the '(System.Nullable<System.Char>)parameterPar4.Value' statement or is it during the 'parameterReturnValue.Value == DBNull.Value ? -1 : (int)parameterReturnValue.Value ' statement?

Can you examine the type of the value that is being cast?  You can copy the generated code into a partial class (so that is not overwritten the next time you save the rlinq file) and modify the cast by first 'unboxing' to the type of the value and then to the desired type.

Regards,
Ady
Telerik

[Craig]

Hi Ady,
The code below reproduces the error. OAMethodSim is a stripped down version of the generated method and my call to it is in the two lines in Main. I don't think it should matter what value I assign to parm4 since it should be updated by OAMethodSim.
Craig

using System;
using System.Data;
using System.Linq;
using Telerik.OpenAccess.Data.Common;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            char? parm4 = char.Parse(" ");
            int result = OAMethodSim(ref parm4);

            Console.WriteLine("Press a key ...");
            Console.ReadKey();
        }

        static int OAMethodSim(ref System.Nullable<System.Char> validInd)
        {
            OAParameter parameterValidInd = new OAParameter();
            parameterValidInd.ParameterName = "Valid_Ind";
            parameterValidInd.Direction = ParameterDirection.InputOutput;
            if (validInd.HasValue)
            {
                parameterValidInd.Value = validInd.Value;
            }
            else
            {
                parameterValidInd.DbType = DbType.String;
                parameterValidInd.Value = DBNull.Value;
            }

            //simulate sp call
            parameterValidInd.Value = "N";

            validInd = parameterValidInd.Value == DBNull.Value
                ? default(System.Nullable<System.Char>)
                : (System.Nullable<System.Char>)parameterValidInd.Value;

            return 0;
        }

    }
}

[Ady]

Hello Craig,

Thank you for the sample code. That helped in verifying the problem.
The root cause of the problem lies in the fact that although 'param4' is of type 'char(1)' and is assigned a character literal in the stored procedure, the value is obtained as a 'string' via the ADO.NET framework.

Since we detect that the parameter length is 1, we generate the method with a parameter of CLR type 'char'. Casting a string to char fails.

To work around this you can either change the parameter length in the stored procedure from 1 to 2. Delete the generated method via the designer (Model Object Explorer), Update the model from the database and then generate the method again. This time a 'string' parameter will be generated and it should work.
Alternatively you can copy the generated method into a partial context class and modify the method parameter to a string or then convert the string to a char explicitly.

Do get back in case you need further assistance.

Regards,
Ady
Telerik

[Craig]

Hi Ady,
I will be unable to change the stored procedure because it is shared and in use in existing applications.

"Since we detect that the parameter length is 1, we generate the method with a parameter of CLR type 'char'. Casting a string to char fails."

Will this be fixed so that the generated OA Method correctly handles char(1) parameters or handles them as varchar (strings)? 

We use char(1) indicators a lot. I would like to try the second option but I would not want to have to edit the method every time the RLINQ model is updated from the database or regenerated. Would that be the case?  If not, please can you provide an example?

 
Thanks,
Craig

[Ady]

Hi Craig,

 Since the domain method generation code is generic for all backends (Oracle, MySql etc) and not specific for Sql Server any changes there would need careful consideration and deliberation. We would need to test it against various use cases. I will bring this up for discussion and let you know if and when a fix is scheduled.

The workaround I suggested needs to be done only once.

1. You should copy the generated domain method.
2. Create a partial class for the EntitiesModel class (or whatever you context class is called). This will be a separate source file in the project with the same class name. This file is not overwritten by the designer on every save.
3. Past the method code there.

This way you will not lose the changes on saving the rlinq file.

 Do get back in case you need further assistance.

Regards,
Ady
Telerik

[Craig]

Hi Ady,
Thanks for your help.

I found it easier in the end to wrap the stored procedure with my own stored procedure that uses varchar(2) as the output parameter which was treated as a string. (Varchar(1) was considered the same as char(1) and also translated to char?).

Regards,
Craig

[Ady]

Hi Craig,

 Glad that helped.

Feel free to get back in case you need further assistance.

Regards,
Ady
Telerik