How to access 'Results', 'Messages', and 'Return Value' of a Stored Procedure using Entity Framework 4?

Question

QUESTION

How do I access the 'Results', 'Messages', and 'Return Value' of a Stored Procedure using Entity Framework 4.4 and C# 4.0?

Below is the Stored Procedure that takes three parameters. One way or another when I run the Store Procedure I should, I hope, be able to access all three values for 'Results', 'Messages', and 'Return Value'.

Can someone help me figure out how to do that with EF? Using the code that is generated out of EF all I seem to be able to access is the 'Results' of the query ( returned rows )

Stored Procedure

USE [THIS_DB]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  

ALTER PROCEDURE [dbo].[THIS_PROCEDURE]  
    @FIRST_PARAM CHAR(17) = NULL,  
    @SECOND_PARAM CHAR(2) = NULL,  
    @THIRD_PARAM CHAR(5) = NULL  
AS  
BEGIN  
    SET NOCOUNT ON;  
    DECLARE @ReturnValue INT = 0;  
    IF COALESCE(@SECOND_PARAM, 'XX') NOT IN ('XX', 'YY')  
    BEGIN  
        RAISERROR('Invalid @SECOND_PARAM value: %s; @SECOND_PARAM mXXt be XX or YY.', 2, 1, @SECOND_PARAM ) WITH SETERROR;  
        SET @ReturnValue = -50100;  
    END  
    IF COALESCE(@SECOND_PARAM, 'XX') = 'YY'  
    BEGIN  
        RAISERROR('@SECOND_PARAM value: %s; YY is valid, but currently is not supported, returning XX results.', 2, 1, @SECOND_PARAM) WITH SETERROR;  
        SET @ReturnValue = -50105;  
    END  
    IF COALESCE(@THIRD_PARAM, 'XX-EN') NOT IN ('XX-EN')  
    BEGIN  
        RAISERROR('Invalid @THIRD_PARAM value: %s; @THIRD_PARAM mXXt be XX-EN.', 2, 1, @THIRD_PARAM) WITH SETERROR;  
        SET @ReturnValue = -50101;  
    END  
    SELECT DISTINCT  
        THESE.VALUES  
        FROM dbo.THIS_TABLE  
        WHERE THESE.CONDITIONS;  

    IF @@ROWCOUNT = 0  
    BEGIN  
        DECLARE @SP_MATCHCOUNT INT  

        EXEC @SP_MATCHCOUNT = [dbo].[MATCHTABLE] @PATTERNH = @PATTERN  
        IF @SP_MATCHCOUNT > 0  
        BEGIN  
            RAISERROR('Mapping from HERE to HERE not found for PATTERN: %s.', 2, 1, @PATTERN) WITH SETERROR  
            SET @ReturnValue = -50103;  
        END  
        ELSE  
        BEGIN  
            RAISERROR('PATTERN Pattern not found for PATTERN: %s.', 2, 1, @PATTERN) WITH SETERROR  
            SET @ReturnValue = -50104;  
        END  
    END  
    RETURN @ReturnValue  
END  

CODE

public virtual ObjectResult<THIS_PROCEDURE_RESULT> THIS_PROCEDURE_METHOD(string FIRST, string SECOND, string THIRD)  
{  
    var FIRST_PARAM = FIRST != null ?  
        new ObjectParameter("FIRST", FIRST) :  
        new ObjectParameter("FIRST", typeof(string));  

    var SECOND_PARAM = SECOND != null ?  
        new ObjectParameter("SECOND", SECOND) :  
        new ObjectParameter("SECOND", typeof(string));  

    var THIRD_PARAM = THIRD != null ?  
        new ObjectParameter("THIRD", THIRD) :  
        new ObjectParameter("THIRD", typeof(string));  

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<THIS_PROCEDURE_RESULT>("THIS_PROCEDURE", FIRST_PARAM, SECOND_PARAM, THIRD_PARAM);  
}  

Mitsue

Has this problem with EF not returning stored procs return value been fixed with EF 6.1.3? – Arnie

Answer 1

So, first things first :-) Just want to make sure we're on the same page before I answer the 3 parts of the question. EF is designed to be an ORM (object-relational-mapper). That means its purpose for being is to translate relational data to code objects (and vice-versa). The mechanism it uses for this is result sets (not return values). So most of the plumbing inside EF is specifically designed to operate on result sets, and also to automatically generate SQL for getting those result sets. However, since people requested it, EF now has the capability to execute stored procedures, but that ability is not comprehensive, and is sort of a side-effect to the main capabilities of the product. Having said that, EF does use ADO.NET under the covers, and that's where you are going to get your answers because ADO.NET does handle all your scenarios.

First problem - how to get results. EF will execute the SP in this case, and presumably, it's mapped to some object that has properties that match the result columns. That means that EF will create a collection (enumerable query result set to be more precise) of objects, each of which represents a row of data in the results. In your case, the return of your method is ObjectResult. ObjectResult is a collection of objects, and each item is of type THIS_PROCEDURE_RESULT, which in turn has a property for each mapped column of the result.

Second problem - how to get messages. If Raiserror is used with a certain range of severity, will cause ADO.NET to throw and exception (of type SqlException). EF will just just surface (pass through) that error. That SQLException instance will contain all the error & message information. To see it, you just have to catch the error:

try
{
    // call EF SP method here...
}
catch(SqlException se)
{
    Debug.WriteLine(se.Message);
}
catch(Exception e)
{
    // all non-DB errors will be seen here...
}

However, if the Raiserror statement is of a warning or info severity, ADO.NET will not throw an exception. In that case, you have to use an event of the connection object to see info and warning messages from the databse. To do this in EF, you have to get the EntityConnection from the EF object context, and then get the Store Connection from the EntityConnection. If you are using SQL Server (SqlClient ADO.NET provider), this will be a SqlConnection instance. That instance contains an event called InfoMessage. You can hook up an event handler to that event to trap messages. More info here: http://support.microsoft.com/kb/321903

Last problem - how to get Return Value. This one is going to suck. Based on my first paragraph, EF isn't really designed to arbitrarily handle SP calls. While it will map result sets to object collections, it doesn't handle return values from SPs. You will have to use ADO.NET without the EF layer in order to access the Parameters collections of the SqlCommand object. One of the parameters is of parameter-type ReturnValue, and it will contain the return value itself.

Estellestella

Thank you for such a detailed answer. Your time is greatly appreciated. – Mitsue

Thanks a lot! As for getting the messages, it was a lot easier (and not working as described) at least with EF6. var con = context.Database.Connection as SqlConnection; and then con.InfoMessage += (s, e) => { System.Diagnostics.Debug.WriteLine(e.Message); }; ... Formatting might be totally broken.. – Tallis