How to retrieve the data from a stored proc?

Question

I want to call a stored procedure from my EF6 MVC 5 app.
The stored procedure is just a SELECT * FROM Customers WHERE <conditions>, but the problem is that in MVC 5 I don't know how to retrieve the records.

I have done it before with .NET Core, which is a breeze:

   IQueryable<Customer> customers = Context.Customers.FromSqlRaw("exec sp_get_nearest_customers", param1, param2);  

In MVC 5 the only way I found was this:

   var customers = new List<Customer>();  
     
               using var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());  
               using var cmd = new SqlCommand("sp_get_nearest_customers", con);  
     
               cmd.CommandType = CommandType.StoredProcedure;  
               cmd.Parameters.AddWithValue("@ClientId", clientId);  
               cmd.Parameters.AddWithValue("@Latitude", lat);  
               cmd.Parameters.AddWithValue("@Longitude", lng);  
               cmd.Parameters.AddWithValue("@Range", range);  
     
               con.Open();  
     
               using var da = new SqlDataAdapter();  
               da.SelectCommand = cmd;  
     
               using var ds = new DataSet();  
               da.Fill(ds);  
     
               for (int i = 0; i < ds.Tables[0].Rows.Count; i++)  
               {  
                   Customer customer = new Customer();  
                   customer.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"].ToString());  
                   customer.Company = ds.Tables[0].Rows[i]["Company"].ToString();  
                   customer.Geolocation = Newtonsoft.Json.JsonConvert.DeserializeObject<GeolocationJSON>(ds.Tables[0].Rows[i]["Geolocation"].ToString());  
     
                   customers.Add(customer);  
               }  
     
               return customers;  

As you must agree it's a much longer way to obtain the same result.
I hope I am missing the obvious here and that someone can suggest a more proper and quick way to do this.
For the record, I use unitOfWork, repository pattern etc (if it helps).

Again, it's in MVC 5, not .net core.

Thanks!

Answer 1

HI @Stesvis

You can use in this way.

dbContext.Database.SqlQuery<YourEntityType>("storedProcedureName",params);  

This is quite simple and easy to use.

Answer 2

In old EF you used the designer

https://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

Or off the context

https://www.c-sharpcorner.com/UploadFile/ff2f08/call-store-procedure-from-entity-framework/

Answer 3

Hi @Stesvis ,
I think you could add these codes to your generic repository.Just like this:

218415-new-text-document.txt
Note: T will come from ProductRepository : GenericRepository<Product>.

Best regards,
Yijing Sun

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.