Execute Stored Procedure In Entity Framework

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.

It will return the field name comma separated.
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);

4 Comments

Join the conversation! Your thoughts help the community grow.

Mahesh Alle8y ago
I have question. You have created an extension method of object class. That means, this extension method will available to all the class objects. (i.e. when ever I created object of any class, then this extension method will be available). This is not a good solution as we are providing this extension method all the class objects that actually not needed. Do you have any other way to implement this solution? May be can you create this extension method in other class instead on base class object. Just this.....
- Jaimin Shethiya8y ago
  Yes if you have remove the this keyword in that method so not available for the all object class.
Wook Jin Lee8y ago
Wow. that is so useful. thank you.
- Jaimin Shethiya8y ago
  Thank you for your comment to this. :)