Christopher J. Grace Christopher J. Grace - 1 month ago 10
C# Question

Entity Framework 6 stored procedure call resultset decimal value is 0

This is an ASP.NET MVC project using EF6. A stored procedure needs to be called that takes a table-valued parameter.

EntityFrameworkExtras
is used to facilitate the call.

The stored procedure returns a result set with several rows and columns. One of the columns is a
Decimal(8,2)
. The stored procedure is executed by calling EF6's

context.ExecuteStoredProcedure<ClassMatchingResultset>


The call is successful. A
List<ClassMatchingResultset>
is returned. All the properties in it are filled out except for the decimal column which is zero on all rows.

The stored procedure tests out fine in SSMS, and returns the decimal values when calling it with the same parameters.

ClassMatchingResultset
uses the
Decimal
datatype for the decimal property. The stored procedure is returning a select statement on a temp table with a decimal column of the same name.

What is happening here? The decimal property's name is
TotalCost
. Is
TotalCost
just not getting set at all? Is EF setting
TotalCost
to zero because of some problem reading the result set?

Answer

This turned out to have nothing to do with EF6 or EF6 Extras. The reason the proc worked fine when being called from SSMS was because the TVP was constructed correctly there. The C# code however filled the TVP with incorrect values resulting in the proc returning zero for the decimal column. Ugh!

The troubleshooting technique of repeatedly simplifying the .net code and the proc, and retesting after each iteration, eventually pinpointed the problem, but it took forever.

Test driven development would have uncovered this immediately and saved a tremendous amount of time. In my experience, for TDD to by truly effective, it must be in a project using IoC containers and a mock framework. Those projects seem hard to come by.

Use of SQL Extended Events to trace the calls may have shown the value being passed into the proc's parameters. This information is not available with EF6's built in logging feature.