Entity Framework with Stored Proc that optionally returns recordset

Question

I have a stored proc that returns a recordset normally, but in certain circumstances it doesn't return a recordset at all. This is causing EF some grief:

"The data reader is incompatible with the specified 'myDBContext.usp_MyProc_Result'. A member of the type, 'TheFirstColumn', does not have a corresponding column in the data reader with the same name."

Is there a way I can tell it just return null or something like that if the proc doesnt return anything

Answer 1

I've only ever seen this issue with EF + MySQL when dealing with VERY highly concurrent stored-procedure calls that contain transactions, but once it happens the first time, it happens several thousand times more, very quickly. Funnily enough, I've never had this error with EF + SQLServer and I use that a lot more frequently.

There are a few things I've done to rectify this issue:

Database-level

Firstly you should update the stored procedure and implement some exception handling. I know the OP said this wasn't possible for him, but it should be the first recourse anyway! This example is in mysql

CREATE PROCEDURE `YourProcedureName` (IN YourParameter YourType)
BEGIN
    DECLARE STUFF;

    -- Thanks to tips from http://khanrahim.wordpress.com/2010/05/16/transaction-with-stored-procedure-in-mysql-server/
    -- and syntax from http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING 
    BEGIN 
        ROLLBACK; 
        SELECT AnInstanceOfTheResultSet FROM YourTable WHERE 1 = 0; -- Force an empty result-set 
    END;

    START TRANSACTION;
        -- Do your complex operations
    COMMIT;
END

In-Code

Secondly, in code, wrap your EF call with the most specific Exception that is thrown, i.e.: EntityCommandExecutionException. Here's an example in c#

YourType result = null;
try 
{ 
    result = yourContext.YourProcedureName(YourParameter).FirstOrDefault(); 
}
catch (EntityCommandExecutionException ecee) 
{ 
    // Log it? Handle it somehow... 
}
if (result == null)
{
    // Handle your error state
}

// Continue processing normally...

Good luck!