Execute Stored Procedure In Entity Framework

In EF (Entity Framework), there are mainly two ways to execute the stored procedure.

In EF (Entity Framework), there are mainly two ways to execute the stored procedure.

Execute Command

Normally, all developers do it this way.

var affectedDatas = context.Database.ExecuteSqlCommand("store_procedure_name @field1, @field2, @field3, ...",
   new SqlParameter("@field1", "value1"),
   new SqlParameter("@field2", "value2"),
   new SqlParameter("@field3", "value3"),
...);

In the above scenario, if more parameters are added in the stored procedure, then we have to add that newly created parameter in this statement also. If we forget to add that, it will throw an error. 

To overcome this type of mistake, we can create an Extension method by which we can simplify this operation.

public class SQLQueryClass {
    public string Command {
        get;
        set;
    }
    public SqlParameter[] Parameters {
        get;
        set;
    }
}
public static SQLQueryClass CreateCommandAndParameters(this object obj) {
    var props = obj.GetType().GetProperties();
    var result = new SQLQueryClass();
    var lstSqlParameters = new List();
    var lstName = new List();
    for (int i = 0; i < props.Length; i++) {
        var propertyName = $ "@{ props[i].Name}";
        var value = props[i].GetValue(obj, null);
        lstName.Add(propertyName);
        var sqlParameter = new SqlParameter(propertyName, value ? ? DBNull.Value);
        lstSqlParameters.Add(sqlParameter);
    }
    result.Command = string.Join(", ", lstName);
    result.Parameters = lstSqlParameters.ToArray();
    return result;
}

This is how to use the above created method in your project.

var parameters = new
{
   field1 = value1,
   field2 = value2,
   field3 = value3,
...
};
var result = parameters.CreateCommandAndParameters();

Then, it will return two things in that response.

1. It will return the field name comma separated.
2. It will return the SQL parameters.

And finally, execute the command.

var command = $"store_procedure_name {result.Command}";
var affectedDatas = context.Database.ExecuteSqlCommand(command, result.Parameters);

SQLQuery 

Normally, all developers do it this way.

var affectedDatas = context.Database.SqlQuery("store_procedure_name @field1, @field2, @field3, ...",
   new SqlParameter("@field1", "value1"),
   new SqlParameter("@field2", "value2"),
   new SqlParameter("@field3", "value3"),
...);

In the above scenario, if one more parameter is added in the stored procedure, then we have to add that newly created parameter in this statement also. If we fail, then it will throw an error. 

To overcome this type of mistake, we can create an Extension method by which we can simplify this operation.

var result = parameters.CreateCommandAndParameters();
var command = $"store_procedure_name {result.Command}";
var affectedDatas = context.Database.SqlQuery(command, result.Parameters);