How to use ExecuteStoreQuery with parameters on the Entity Framework

This tutorial is an example of  How to use the ExecuteStoreQuery method with parameters on the  Entity Framework, thus directly executing SQL commands against the Model Data Source.
We'll use a stored procedure that takes in a string parameter and returns some records, which we'll store inside a generic List<>. Using SQL commands or stored procedures is an effective way to retrieve only the records that you need, instead of fetching ALL the table data, to apply on it some kind of filtering using Where() or Single() methods, with all the performance costs that this later imply.

We'll exemplify running commands against the database on two ways:
1) using Stored Procedures
2) using an SQL command

 How to use ExecuteStoreQuery with parameters on the Entity Framework

1) For our first example, we create a stored procedure which takes a parameter and return three records using the received argument:

Next, we define an SqlParameter for sending some text to the procedure:

 SqlParameter p_code = new SqlParameter("code", "TEST");
            List<string> resultComplaintStateCode =
                                (from c in  Context
                                     .ExecuteStoreQuery<string>("GetText @code" ,p_code
                                 )
                                 select c).ToList();

 Notice that we use Linq to insert the returned records inside a generic List<> :

Is really straightforward. However, if you use ExecuteMethodCall() method instead of ExecuteStoreQuery(), you'll need to update the Entity Framework Data Model, importing the stored procedure:

2) As the ExecuteStoreQuery method's name implies, we can also use it to execute some SQL command against the database.
For instance, let's copy the same SQL code from the stored procedure, and execute it directly from the C# code:

 As you see, the SQL command was executed according to the three parameters that we sent :

That's all!!!!

By Carmel Shvartzman

עריכה: כרמל שוורצמן