Lost decimal precision and scale using LINQ and stored procedure with output parameters

Question

I have a stored procedure that uses output parameters like this:

ALTER PROCEDURE [GetAmount] 
( 
@orderID [int], 
@totalcost decimal(18,2) OUTPUT 
) 
SELECT @totalcost = cost 
FROM mytable 
WHERE orderID = @orderID

When I drag the stored procedure onto the designer, the resulting code in the designer.cs file ends up losing the precision and scale, like this:

[Parameter(DbType="Decimal")] ref System.Nullable<decimal> totalcost

This is a problem, because things like product prices and order totals are being rounded up (i.e. 19.95 becomes 20).

Now, I can manually correct the .cs file, but I'd have to remember to do it every time an update is made. Am I doing something wrong? Is there a way to change my stored procedure that would enable LINQ to automatically detect precision and scale?

Answer 1

Can you use the money type rather than the decial(18,2) type?

Answer 2

I can manually correct the .cs file, but

Right, you need to move this code into a partial class file, edit the mapping's precision there, and drop the stored procedure from the designer.

This gives you a manual specification in code of the mapping to the stored procedure.