locked
Decimal output parameter rounded to integer in EF5.0 RRS feed

  • Question

  • Hi,

    There have been so many questions around this issue but I cant find a concrete answer. We are using existing database stored procedure as below,

    -----------------------------------------------------------------------------
    CREATE PROCEDURE spTest(@a int, @b decimal(18,2) output)
    as
    BEGIN
    SELECT @b=23.22
            SELECT * FROM <TABLE> where id = @a
    END
    -----------------------------------------------------------------------------

    When I call the stored procedure in in C# app (code below) I get the result for the output parameter as 23 instead of 23.22

    ObjectParameter b = new ObjectParameter("b", typeof(System.Decimal))
    var result = myentities.context.spTest(1234, b)


    This exactly the same issue posted by Imre Horvath (http://social.msdn.microsoft.com/Forums/en-US/14bdde82-c084-44dd-ad83-c1305cb966d2/decimal-output-parameter-rounded-to-integer) but the difference is we are using SQL Server 2008 and entity framework 5.0. After reading the suggestion from his post I have opened the edmx file in xml editor and noticed the following for the output parameter @b as below,

    <Parameter Name="b" Type="decimal" Mode="InOut" />

    I changed it to 

    <Parameter Name="b" Type="decimal" Mode="InOut" Precision="18" Scale="2"/>

     and run the application and I got the result as expected (23.22)

    This is a work around but not a solution as you know that changes will be lost when we update the stored procedure in the entity framework designer. In our database we have lots of stored procedure that has decimal(18,2) as output parameter. I'm wondering whether this still an issue in entity framework 5.0.

    Your help will be much appreciated.

    Kumar


    Friday, August 9, 2013 10:09 PM

Answers

  • Hi itzkumaar,

    Welcome to MSDN Forum. 

    Based on my analysis of the issue, the root cause is the difference between SqlServer and C#.

    In SqlServer, each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are treated as different data type. But in C# this is not the case. Therefore we should use float or real instead of using the decimal type in Stored Procedure.

    In this example, we don’t need to change a lot. I have a simple way for you to perform in order to resolve your problem.

    The ObjectParameter Class has two constructors. We should use this one. “public ObjectParameter(string name, object value)”. If you enter 11.01, then the result is 23.22. If you enter 11.1, then the result is 23.2.

    ObjectParameter b = new ObjectParameter("b",11.01);   

    I hope this will help resolve your problem.

    Best Regards,

    Monday, August 12, 2013 6:12 AM

All replies

  • Hi itzkumaar,

    Welcome to MSDN Forum. 

    Based on my analysis of the issue, the root cause is the difference between SqlServer and C#.

    In SqlServer, each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are treated as different data type. But in C# this is not the case. Therefore we should use float or real instead of using the decimal type in Stored Procedure.

    In this example, we don’t need to change a lot. I have a simple way for you to perform in order to resolve your problem.

    The ObjectParameter Class has two constructors. We should use this one. “public ObjectParameter(string name, object value)”. If you enter 11.01, then the result is 23.22. If you enter 11.1, then the result is 23.2.

    ObjectParameter b = new ObjectParameter("b",11.01);   

    I hope this will help resolve your problem.

    Best Regards,

    Monday, August 12, 2013 6:12 AM
  • Hi Hetro,

    Thanks for your reply. I completely understand the way SQL Server and C# treats the data types.

    The approach you mentioned works but the problem  is we can't set some default value  to determine precision and scale. Instead is there any way where we can force entity framework to use decimal(18,4)?

    Thanks & regards,

    Kumar

    Tuesday, August 20, 2013 4:09 AM