Chapter 10. Working with stored procedures
This chapter covers
- Mapping stored procedures in the EDM
- Retrieving data using stored procedures
- Embedding SQL commands in the EDM
- Updating data using stored procedures
Now that you’ve mastered Entity SQL, we can move forward and take a closer look at another advanced feature of Entity Framework: the use of stored procedures.
Because LINQ to Entities makes it so easy to write queries, leaving the burden of creating SQL code to the framework, you may think that stored procedures are no longer needed. From a developer’s point of view, writing queries against the domain model with LINQ to Entities is more natural than writing a stored procedure that returns raw data. But there is another side to this coin: the DBA.
Often, DBAs want full control over the commands that are executed against the database. Furthermore, they want only authorized users to have read and write permissions on the tables, views, and so on. A well-defined set of stored procedures gives the DBA this control over the database and guarantees that the SQL is highly optimized.
Another situation where stored procedures are useful is when you want to put some logic in them. In OrderIT, when an order is placed, you have to update the quantity of in-stock products by subtracting the items sold. A stored procedure is the ideal place to put such logic.