Decimal output parameter rounded to integer in EF5.0

Question

We are using existing database stored procedure similar to the one below that returns decimal output,

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

Answer 1

I got the same issue with you and I have resolved it after I referenced from this article http://tiku.io/questions/1120572/decimal-output-parameter-rounded-to-integer-in-ef5-0

There is 3 steps to resolve this issue:

1. Right click on edmx file => Open with... => XML (text) Editor.
2. search text <Parameter Name="b" Type="numeric" Mode="InOut" /> then replace it by <Parameter Name="b" Type="numeric" Mode="InOut" Precision="20" Scale="2" />
3. search text <Parameter Name="b" Mode="InOut" Type="Decimal" /> then replace it by <Parameter Name="b" Mode="InOut" Type="Decimal" Precision="20" Scale="2" />

Hope this will help you!

Answer 2

In EF6, I got the same problem ! But find a simple solution. I try to set the output ObjectParameter value, then return the decimal with scale, like 5602.86

public decimal GetQuotationAmount(string rec_id, decimal price)
{
    ObjectParameter qTA_AMT = new ObjectParameter("QTA_AMT", typeof(decimal));
    qTA_AMT.Value = 100000.00m;
    db.GRP_Calc_QuotationAmount(rec_id, price,qTA_AMT);
    return Convert.ToDecimal(qTA_AMT.Value);
}

Answer 3

No, this is not fixed in EF 5.0 It probably won't be fixed in 6.0 either.

UPDATE:
I have recently updated to EF 6.0, and no, the problem is still not fixed. I guess that EF 6.0 is now Open Source so you could always patch it yourself if you are into that kind of thing.