How to use InOut ObjectParameter in EF4?

Question

Recently, I had to return only one value in addition to the tabular data returned by my stored procedure. Since EF does not support stored procedures with multiple result sets, I decided that I could accomplish this with an output parameter. However, using this method, I ran into a problem when I only returned rounded values ​​for some numeric value fields.

The parameter of my stored procedure was declared as:

@MyValue numeric(19,6) output 

When calling the mapping function, I had:

 var myValue = new ObjectParameter("MyValue", typeof(decimal)); List<MyResultItem> results = this.ObjectContext.CallMyStoredProc(someId, myValue).ToList(); 

This is what always returned a value rounded to the nearest integer (i.e. scaling zero).

At first, I was able to fix this by manually editing the basic XML in .edmx, manually adding the Precision and Scale attributes:

 <Parameter Name="MyValue" Type="numeric" Mode="InOut" Precision="19" Scale="6" /> 

It was not surprising that it was completely discarded the next time I executed the command “Update Model from Database”.

I seem to have fixed this more reliably by updating my declaration for ObjectParameter as such:

 var myValue = new ObjectParameter("MyValue", 999999999.999999M); 

However, it seems awful how to hack, and I'm worried about problems in the future (even if it's just a service regarding this magic number). Is there a better and more reliable way to use the output parameters in the Entity Framework?

Answer 1

In the end, I needed it more, so I created a helper method for it:

 /// <summary> /// Get the maximum allowed value for a SQL numeric of the specified scale and precision. /// </summary> /// <param name="scale">The scale.</param> /// <param name="precision">The precision.</param> /// <returns>Decimal representing the maximum value for the specified numeric.</returns> public static decimal SqlNumericMax(int scale, int precision) { return (decimal)Math.Pow(10, (scale - precision)) - (decimal)Math.Pow(10, (-1 * precision)); } 

This does not exempt the use of magic numbers in code, but at least it helps to ensure an exact match between what you see in the code and what you see in the database. (i.e., with the proc numeric(19,6) parameter stored, you call Utility.SqlNumericMax(19, 6) , so that the connection becomes more obvious.)

It seems that providing the maximum value to the parameter before the saved proc call is still the best and most reliable method to populate the result correctly.