Entity Framework with Stored Procedures
One of my latest tasks used to be updating a present custom MVC utility which used Entity Framework with Stored Procedures (model first approach) to question data. The home web page retrieved articles on the stock charge overall performance of organizations for a chosen inventory exchange from the SQL database.
Under regular circumstances, the home web page loaded efficiently, but when new data was once being populated in the database, the domestic web page timed out. It was clear that some kind of lock rivalry was happening in SQL Server for the home page query.
After a bit of research and some code / SQL profiling, we concluded that two matters wished to be addressed with the way the machine used to be presently working.
1. We needed to fine-tune the question to make certain that only the facts we certainly wished should be retrieved. The default question generated with the aid of EF was once retrieving too a great deal information.
2. We wanted to be able to set the transaction isolation degree so that we didn’t want to wait for row locks to be released, even if that intended getting a barely stale model of article data.
Ordinarily, to tackle difficulty #1, we may want to adjust the EF approach calls to make certain that solely the essential records wished used to be retrieved. However, to reap #2, it appeared like the great route was once to write a custom stored procedure to retrieve the article facts required for the domestic web page and then update Entity Framework to use our fine-tuned saved technique as a substitute of the default question generated by using EF.
Step 1: Create the Stored Procedures
The SELECT query we needed to run on SQL server to retrieve our information was once straightforward, it just wanted quite a few inner joins to grab all related records we’d need to show our articles on the home page. There were a few key variations in the way we wrote our queries versus the way the Entity Framework query executed:
> Setting the transaction isolation level to SNAPSHOT so that the select question doesn’t want to wait until row locks are launched at some point of information updates.
> Using an easy INNER JOIN structure rather of more than one subqueries.
> Using OFFSET and FETCH NEXT to retrieve only the rows wished for that “page” of outcomes as an alternative of the full end result set.
Step 2: Update the Entity Framework Model
Once our stored technique was created in SQL, we ought to then use the “Update Model From Database” feature in Entity Framework in the EF Designer. This created the following changes in our universal EF Model:
> In our records store, the mannequin now showed the new saved manner we simply wrote
> A feature import was created for the saved procedure
> A complex type used to be created as the return type for the characteristic import.
Examining the code generated by using the replace to Entity Framework, we may want to now see that our database context presented a new approach named after our stored procedure, with a return type of our new ArticleSearchData complex type.
The key distinction here was once the new complicated kind being again as a substitute of a category based immediately on our desk model. The stored manner used to be now giving us a category object with homes for each and every column back via the SELECT query in our saved procedure. Instead of getting a hierarchy of associated classes, we were now getting a single object type with a flattened shape that had homes for each of the associated entities we wanted to show our domestic page articles.
Step 3: Update Required Object Mappings
The new objects back by means of the Entity Framework characteristic import now needed to be completely built-in into the MVC software so that the right facts would show up in every view.
In the current application, we already had a repository sample implementation to set up structured access to the EF model. The remaining step of integrating the new saved method required the following code updates:
1. Update the articles repository to make a new name to the stored system approach name delivered to our information context, passing thru any required search parameters.
2. Update object mapping strategies to convert the ArticleSearchData buildings into view fashions required by way of the MVC application.
Appendix: Adding and Tuning Database Indexes
In addition to improving the performance of the query we used for fetching data, we also performed some simple analysis on the current SQL tables to see if we could add some configuration in SQL itself to assist enhance query performance. In fact, this is a key step to troubleshooting or stopping any overall performance troubles associated with a SQL database. Using the Database Engine Tuning Advisor device that comes with SQL Server, we recognized some additional indexes to add to some of the tables in the database which similarly enhance web page performance.