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
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:
By Carmel Shvartzman
עריכה: כרמל שוורצמן