Brendan McKenzie

Entity Framework executing stored procedures

Tuesday, 28 August 2012

Let's start with the code...

public IEnumerable<T> ExecuteStoredProcedure<T>(string name, object parameters)
    if (parameters != null)
        var _parameters = parameters.GetType().GetProperties().Select(x => new SqlParameter()
            ParameterName = x.Name,
            Value = x.GetValue(parameters, null)
        var sql = name + " " + string.Join(", ", _parameters.Select(p => "@" + p.ParameterName));
        return Database.SqlQuery<T>(sql, _parameters);
        return Database.SqlQuery<T>(name);

... and finish with an explanation.

We needed a way to execute stored procedures and deal with the data returned from them. The code above will take the name of a stored procedure and execute it against the database returning a strongly typed enumerable of entities.

This method lives within my context class that derives from DbContext but can be used anywhere you can access DbContext.Database.

Obviously there's plenty of flaws with it, but I literally just wrote it and decided to share. Feel free to discuss issues in the comments...