So the last few hours I’ve been racking my brain trying to figure out at what point I was doing an INT conversion. After stepping through the code from the web tier down to the EF context no luck!
I have a stored procedure in SQL Server 2014 that should be returning a decimal. It’s a very simple stored procedure similar to this..
ALTER PROCEDURE [dbo].[spGetDays](@UserId nvarchar(10), @DateFrom datetime, @DateTo datetime, @ALDays decimal(6,1) OUTPUT) AS BEGIN declare @end datetime = DATEADD(hour,23,@DateTo); set @end = DATEADD(minute,59,@end); declare @count int; SELECT @count = COUNT(*) FROM Activity WHERE UserID = @UserId AND (ActivityDate BETWEEN @DateFrom AND @end); set @ALDays = CAST(@count AS DECIMAL(6,1)) / CAST(2 AS DECIMAL(6,1)); END
When I execute the procedure in SQL Management studio the result I am expecting
17.5 is returned.
However when I run my application, I can step through the code, into the EF context and the value returned is
From some Google searches it would appear this is a well known issue that has been around since EF4 and has yet to be fixed.
The fix I found was to edit the EDMX manually in a text editor and add in the
Precision="18" Scale="2" properties to the output parameter.
Then after rebuilding the application and rerunning my application the correct values were returned.
It does seem like this should be a very easy fix for Microsoft and it’s disappointing that in such a fundamental technology such as entity framework, it has gone unfixed for so long.