IT:AD:EF:HowTo:Work with Stored Procs

• By default, the result shape of each imported stored procedure or function that returns more than one column will automatically become a new complex type. In this example we want to map the results of the GetStudentGrades function to the StudentGrade entity and the results of the GetDepartmentName to none (none is the default value).
• For a function import to return an entity type, the columns returned by the corresponding stored procedure must exactly match the scalar properties of the returned entity type. A function import can also return collections of simple types, complex types, or no value.

/// <summary>
/// Executes the sql statement.
/// <para>
/// As per http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced
/// If a Stored Proc, don't forget to prefix with EXEC.
/// </para>
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="sqlStatement">The SQL statement.</param>
/// <param name="argumentsPackage">The arguments package.</param>
/// <returns></returns>
/// <remarks>
/// Usage is as follows:
/// <para>
///         <sxh>
///             <![CDATA[
/// context.Database.SqlQuery<EntityType>(
/// "EXEC ProcName @param1, @param2",
/// new SqlParameter("param1", param1),
/// new SqlParameter("param2", param2));
/// ]]>
///         </sxh>
///     </para>
/// </remarks>
protected virtual IEnumerable<TEntity> ExecuteSqlQuery<TEntity>(string sqlStatement, params object[] argumentsPackage)
{

    IDbDataParameter[] p2 = ConvertObjectToParameters(argumentsPackage);

    IEnumerable<TEntity> result = (p2 != null)
                           ? this.DbContext.Database.SqlQuery<TEntity>(sqlStatement, p2)
                           : this.DbContext.Database.SqlQuery<TEntity>(sqlStatement);


    return result;
}



/// <summary>
/// Executes the SQL command (DELETE, UPDATE, etc).
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="sqlStatement">The SQL statement.</param>
/// <param name="argumentsPackage">The arguments package.</param>
/// <returns></returns>
protected virtual int ExecuteSqlCommand<TEntity>(string sqlStatement, object argumentsPackage)
{
    IDbDataParameter[] p2 = ConvertObjectToParameters(argumentsPackage);

    int rowsAffected = (p2 != null)
                           ? this.DbContext.Database.ExecuteSqlCommand(sqlStatement, p2)
                           : this.DbContext.Database.ExecuteSqlCommand(sqlStatement);

    return rowsAffected;
}

private IDbDataParameter[] ConvertObjectToParameters(object argumentsPackage)
{
    if (argumentsPackage == null)
    {
        return null;
    }
    IList<IDbDataParameter> parameters2 = new List<IDbDataParameter>();

    IDbCommand command = this.DbContext.Database.Connection.CreateCommand();

    IDictionary<string, object> prps = argumentsPackage.GetObjectProperties();
    foreach (KeyValuePair<string, object> o in prps)
    {
        parameters2.Add(command.CreateParam(o.Key, o.Value));
    }
    
    return parameters2.ToArray();
}

• http://msdn.microsoft.com/en-us/data/jj557860
• http://msdn.microsoft.com/en-us/data/jj593489