Stored Procedure sometimes returns short, sometimes returns int

Question

I'm working with a legacy codebase and need to call a stored procedure that I'm not allowed to modify. This stored procedure returns a row or multiple rows of validation data.

Example of result set (two columns, code and text):

0 "success"

OR

3 "short error"
4 "detailed error"

In the procedure itself, the message is selected simply as:

Select 0 as code, 'success' as text

Problem:

I'm using Entity Framework to map the result of this stored procedure to a custom class:

public class ValidationResult
{
    public int code { get; set; }
    public string text { get; set; }
}

The call itself:

var result = context.Database.SqlQuery<ValidationResult>(@"old_sproc").ToList();

I've written some integration tests, and have noticed that when the procedure returns the success message, the 0 comes across as a short. When it returns a non-zero message, it comes across as an int. I assumed that setting code as an int, the short would fit in. Unfortunately, I get the following exception for my success test:

The specified cast from a materialized 'System.Int16' type to the 'System.Int32' type is not valid.

When I switch code to a short to make my success test pass, my failure test fails with the following exception:

The specified cast from a materialized 'System.Int32' type to the 'System.Int16' type is not valid.

ADO.NET is an answer

One solution is to fall back to ADO.NET's SqlDataReader object, so I have that as a fallback solution. I'm wondering if there is something I can do on the EF side to get this working, though.

Answer 1

(This is a follow-up to my previous answer. It is only relevant for sql-server-2012 and later.)

Short answer:

var sql = "EXECUTE old_sproc WITH RESULT SETS ((code INT, text VARCHAR(MAX)))";
var result = context.Database.SqlQuery<ValidationResult(sql).ToList();

Approach taken in this answer:

This answer will follow in your footsteps and use SqlQuery to execute your stored procedure. (Why not an altogether different approach? Because there might not be any alternative. I'll go into this further below.)

Let's start with an observation about your current code:

var result = context.Database.SqlQuery<ValidationResult>(@"old_sproc").ToList();

The query text "old_sproc" is really abbreviated T-SQL for "EXECUTE old_sproc". I am mentioning this because it's easy to think that SqlQuery somehow treats the name of a stored procedure specially; but no, this is actually a regular T-SQL statement.

In this answer, we will modify your current SQL only a tiny bit.

Implicit type conversions with the WITH RESULT SETS clause:

So let's stay with what you're already doing: EXECUTE the stored procedure via SqlQuery. Starting with SQL Server 2012, the EXECUTE statement supports an optional clause called WITH RESULT SETS that allows you to specify what result sets you expect to get back. SQL Server will attempt to perform implicit type conversions if the actual result sets do not match that specification.

In your case, you might do this:

var sql = "EXECUTE old_sproc WITH RESULT SETS ((code INT, text VARCHAR(MAX)))";
var result = context.Database.SqlQuery<ValidationResult(sql).ToList();

The added clause states that you expect to get back one result set having a code INT and a text VARCHAR(MAX) column. The important bit is code INT: If the stored procedure happens to produce SMALLINT values for code, SQL Server will perform the conversion to INT for you.

Implicit conversions could take you even further: For example, you could specify code as VARCHAR(…) or even NUMERIC(…) (and change your C# properties to string or decimal, respectively).

If you're using Entity Framework's SqlQuery method, it's unlikely to get any neater than that.

For quick reference, here are some quotes from the linked-to MSDN reference page:

"The actual result set being returned during execution can differ from the result defined using the WITH RESULT SETS clause in one of the following ways: number of result sets, number of columns, column name, nullability, and data type."

"If the data types differ, an implicit conversion to the defined data type is performed."

Do I have to write a SQL query? Isn't there another (more ORM) way?

None that I am aware of.

Entity Framework has been evolving in a "Code First" direction in the recent past (it's at version 6 at this time of writing), and that trend is likely to continue.

The book "Programming Entity Framework Code First" by Julie Lerman & Rowan Miller (published in 2012 by O'Reilly) has a short chapter "Working with Stored Procedures", which contains two code examples; both of which use SqlQuery to map a stored procedure's result set.

I guess that if these two EF experts do not show another way of mapping stored procedures, then perhaps EF currently does not offer any alternative to SqlQuery.

(P.S.: Admittedly the OP's main problem is not stored procedures per se; it's making EF perform an automatic type conversion. Even then, I am not aware of another way than the one shown here.)