Adds an extension method named ExecuteSqlCommandWithReturn that extends EntityFramework's Database type (System.Data.Entity.Database) allowing one to obtain the return value from a call to a stored procedure (called with the 'EXEC' keyword). NO CHANGES need be made in code (in your SQL scripts or in any big way in the calling code)! Just replace…

DatabaseXtensions.cs

using System;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;

namespace Eclipsoft.Db
{
	/// <summary>
	/// Adds an extension method named ExecuteSqlCommandWithReturn
	/// that extends EntityFramework's Database type (System.Data.Entity.Database)
	/// allowing one to obtain the return value from a call to a
	/// stored procedure (called with the 'EXEC' keyword).
	/// <para/>
	/// NO CHANGES need be made in code (in your SQL scripts or in any big way in the
	/// calling code)! Just replace your call to ExecuteSqlCommand
	/// with ExecuteSqlCommandWithReturn, and the return int value will be set to
	/// the out returnVal param.
	/// <para />
	/// Thanks goes to Jieyang Hu here (http://jieyanghu.com/2013/03/01/retrieving-a-return-value-from-a-stored-procedure-using-the-entity-framework-dbcontext-database-class/)
	/// for illustrating the key that allows this to be done. Namely (though a caller
	/// does not need to know any of this), just as you
	/// can do in a SQL prompt, you just set the result of the EXECuted procedure to
	/// a SQL variable (in this case which is sent in as a parameter), like this:
	/// EXEC @ReturnVal = sp_MyCoolProc;. This code adds the fragment ' @ReturnVal ='
	/// after the first 'EXEC' (followed by whitespace) it finds, and adds to the SqlParameters
	/// (or creates SqlParamters if there were none) a ReturnVal parameter, though the
	/// caller will never see these.
	/// <para />
	/// Author: Nicholas Petersen 2013
	/// </summary>
	public static class DatabaseXtensions
	{
		private const string conReturnValueName = "ReturnVal";
		private const string conReturnValueParamName = "@" + conReturnValueName;

		public static int ExecuteSqlCommandWithReturn(this Database db, string sql, out int returnValue, params object[] parameters)
		{
			returnValue = Int32.MinValue;
			string finalSql = _ReplaceEXECInSql(sql);
			if (finalSql == null)
				return db.ExecuteSqlCommand(sql, parameters);

			var returnParam = new SqlParameter() {
				ParameterName = conReturnValueName,
				SqlDbType = SqlDbType.Int,
				Direction = System.Data.ParameterDirection.Output
			};

			if (parameters == null || parameters.Length == 0)
				parameters = new object[1] { returnParam };
			else {
				int len = parameters.Length;
				var newParams = new object[len + 1];
				for (int i = 0; i < len; i++)
					newParams[i] = parameters[i];
				newParams[len] = returnParam;
				parameters = newParams;
			}

			int result = db.ExecuteSqlCommand(finalSql, parameters);

			if (parameters != null && parameters.Length > 0) {
				int plen = parameters.Length;
				for (int i = plen - 1; i >= 0; i--) {
					SqlParameter p = parameters[i] as SqlParameter;
					if (p == null) continue;
					if (p.ParameterName == conReturnValueName) {
						if (p.Value == null || !(p.Value is int))
							continue;
						returnValue = (int)p.Value;
						break;
					}
				}
			}
			return result;
		}

		private static string _ReplaceEXECInSql(string sql)
		{
			const string execReturnValueEquals = "EXEC " + conReturnValueParamName + " =";

			if (sql == null || sql.Length < 1)
				return null;

			int len = sql.Length;
			int idx = sql.IndexOf("EXEC");

			while (idx >= 0) {
				if (idx + 7 > len) return null;
				if (char.IsWhiteSpace(sql[idx + 4])) {
					string result =
						(idx == 0 ? "" : sql.Substring(0, idx)) +
						execReturnValueEquals +
						sql.Substring(idx + 4);
					return result;
				}
				idx = sql.IndexOf("EXEC", idx + 4);
			}
			return null;
		}
	}
}