Wednesday, November 26, 2014

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:

How to use ExecuteStoreQuery with parameters on the Entity Framework



Next, we define an SqlParameter for sending some text to the procedure:
How to use ExecuteStoreQuery with parameters on the Entity Framework 1

 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<> :

How to use ExecuteStoreQuery with parameters on the Entity Framework 2



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:
How to use ExecuteStoreQuery with parameters on the Entity Framework 3



How to use ExecuteStoreQuery with parameters on the Entity Framework 4




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:
How to use ExecuteStoreQuery with parameters on the Entity Framework 5


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

How to use ExecuteStoreQuery with parameters on the Entity Framework 6




That's all!!!!


By Carmel Shvartzman

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