Entity Framework with Stored Procedures

entity framework
Posted on: June 20, 2018 Posted by: admin Comments: 25

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.

25 People reacted on this

  1. Thanks a lot for giving everyone an extraordinarily remarkable chance to read articles and blog posts from this website. It’s usually very terrific plus jam-packed with a lot of fun for me personally and my office friends to search the blog at minimum 3 times in one week to find out the fresh things you will have. Of course, I am actually pleased with all the brilliant tricks you give. Selected 3 facts in this posting are honestly the most efficient I’ve had.

  2. I and my guys have already been reviewing the excellent recommendations from your website and so immediately I had an awful suspicion I never expressed respect to the site owner for those tips. My people were totally warmed to learn all of them and already have quite simply been loving them. Appreciate your indeed being really kind and also for obtaining this sort of helpful useful guides millions of individuals are really desirous to know about. Our own honest apologies for not expressing gratitude to you earlier.

  3. A lot of thanks for all your valuable effort on this web site. My mom really likes getting into research and it is simple to grasp why. A number of us hear all of the compelling means you create both useful and interesting strategies through the web blog and encourage response from people about this concern and our daughter has always been learning a lot. Have fun with the rest of the new year. You are doing a really good job.

  4. Needed to compose you one little bit of word to give thanks yet again with your precious tactics you have featured at this time. It is quite unbelievably open-handed of people like you to give extensively all a number of people would have offered for sale as an ebook to end up making some bucks for themselves, precisely since you might have tried it if you desired. Those creative ideas likewise acted to become fantastic way to comprehend most people have the identical fervor similar to mine to learn more and more in terms of this matter. I think there are a lot more pleasant moments up front for people who looked at your blog.

  5. I want to express my appreciation to this writer for bailing me out of this predicament. Right after searching throughout the the web and seeing opinions that were not pleasant, I figured my entire life was done. Living without the presence of answers to the difficulties you have sorted out through this post is a serious case, as well as those that would have negatively affected my entire career if I had not noticed your web page. Your actual ability and kindness in maneuvering all the stuff was vital. I’m not sure what I would’ve done if I hadn’t discovered such a point like this. I can at this time look ahead to my future. Thank you so much for the skilled and results-oriented guide. I will not think twice to propose your web site to anybody who wants and needs support about this subject matter.

  6. Needed to create you a tiny observation to finally say thanks a lot as before about the great concepts you have provided on this site. It was so remarkably generous of you to deliver freely all a number of people would’ve offered as an e-book in order to make some cash for themselves, specifically since you could possibly have tried it in the event you desired. The suggestions in addition served like the fantastic way to be sure that most people have the same keenness just like mine to understand a whole lot more related to this condition. I’m sure there are many more pleasurable moments in the future for those who look over your website.

  7. I simply wanted to construct a quick message to say thanks to you for all the great advice you are posting at this site. My time-consuming internet investigation has finally been honored with reliable know-how to talk about with my friends and classmates. I would state that that most of us website visitors actually are rather endowed to dwell in a remarkable site with very many wonderful professionals with very beneficial tips and hints. I feel extremely fortunate to have used the site and look forward to so many more thrilling moments reading here. Thanks a lot once again for all the details.

  8. My husband and i were so comfortable that Michael managed to conclude his research with the ideas he was given using your site. It is now and again perplexing just to happen to be offering steps which usually many people may have been trying to sell. We grasp we now have the website owner to be grateful to because of that. All the illustrations you have made, the straightforward blog navigation, the relationships you will aid to promote – it’s got most incredible, and it’s really assisting our son and the family believe that that matter is excellent, which is certainly exceedingly vital. Many thanks for all the pieces!

  9. Thanks a lot for providing individuals with an extraordinarily memorable possiblity to discover important secrets from this web site. It is always so nice and as well , packed with amusement for me personally and my office fellow workers to search your web site the equivalent of three times in a week to find out the newest guidance you have. Of course, I’m always astounded for the good tips and hints you give. Selected two facts in this article are in truth the most effective I have ever had.

  10. I’m writing to let you understand of the wonderful experience my wife’s child encountered using your web page. She came to find so many things, including what it’s like to possess an ideal giving heart to have many more very easily learn certain hard to do issues. You undoubtedly exceeded visitors’ desires. I appreciate you for distributing such warm and friendly, trusted, explanatory and as well as unique tips about your topic to Jane.

  11. Thank you so much for providing individuals with an extraordinarily splendid chance to check tips from this web site. It is often so pleasing and as well , full of a great time for me personally and my office co-workers to visit your blog minimum three times every week to find out the fresh secrets you have. Of course, I’m so always happy with your stunning points served by you. Selected 4 tips on this page are unquestionably the finest I’ve ever had.

  12. I must show my admiration for your kindness in support of men and women who really need assistance with this one content. Your very own dedication to getting the message around appears to be exceptionally important and have continually permitted folks much like me to arrive at their dreams. Your warm and helpful help and advice indicates a whole lot a person like me and far more to my office workers. Many thanks; from everyone of us.

  13. Thanks so much for providing individuals with such a remarkable opportunity to check tips from this site. It is usually so amazing and as well , jam-packed with a great time for me personally and my office mates to visit your blog really three times weekly to study the fresh tips you have got. And lastly, I am certainly fascinated for the powerful tricks you give. Certain two facts on this page are clearly the most beneficial I’ve ever had.

  14. I and also my guys ended up digesting the nice advice located on your web site and so instantly I had a terrible feeling I had not thanked the blog owner for those tips. All the young men were definitely glad to learn them and already have really been enjoying them. Thank you for simply being quite kind and then for picking out such awesome information most people are really desperate to discover. My personal honest regret for not expressing appreciation to sooner.

  15. I and also my friends happened to be reviewing the excellent tips found on the blog and all of the sudden got an awful suspicion I never expressed respect to you for those techniques. These guys became absolutely stimulated to read them and have in effect quite simply been using those things. Many thanks for genuinely very considerate as well as for using some marvelous useful guides most people are really eager to learn about. My very own sincere regret for not saying thanks to sooner.

  16. Thanks so much for giving everyone an extremely brilliant possiblity to read from this website. It’s always very lovely plus jam-packed with fun for me personally and my office peers to visit your site a minimum of three times every week to read through the latest tips you have got. And of course, we’re usually happy with the excellent secrets you give. Some 2 tips on this page are particularly the most suitable we have ever had.

  17. I needed to compose you one very little note just to thank you so much yet again about the unique basics you have documented on this page. It is unbelievably generous with you to give unreservedly exactly what a number of people would’ve supplied for an ebook to help with making some bucks for their own end, principally given that you might well have done it if you ever decided. Those tactics also served as a easy way to comprehend someone else have similar eagerness much like my very own to figure out a whole lot more concerning this condition. I know there are millions of more fun occasions ahead for those who read carefully your blog.

  18. I happen to be writing to let you be aware of what a outstanding experience my cousin’s daughter found reading the blog. She realized lots of things, not to mention what it’s like to possess an amazing teaching heart to get many more with ease have an understanding of a number of very confusing subject areas. You actually surpassed visitors’ expectations. I appreciate you for delivering such essential, safe, educational and also easy guidance on your topic to Tanya.

  19. I wish to show thanks to the writer just for bailing me out of such a problem. Because of exploring through the world-wide-web and obtaining techniques that were not beneficial, I believed my life was done. Existing without the answers to the issues you have solved all through this article content is a serious case, and the kind that could have negatively affected my career if I had not come across the blog. Your own knowledge and kindness in dealing with the whole lot was helpful. I am not sure what I would’ve done if I hadn’t discovered such a thing like this. I’m able to at this moment look ahead to my future. Thanks for your time very much for the professional and result oriented guide. I will not be reluctant to propose your web sites to anyone who desires care about this area.

  20. I want to show appreciation to the writer for bailing me out of this particular problem. Just after researching through the world wide web and coming across suggestions which are not helpful, I was thinking my entire life was well over. Living without the presence of solutions to the difficulties you’ve resolved by way of the guideline is a serious case, and ones which could have in a negative way affected my career if I hadn’t come across the website. Your primary capability and kindness in touching a lot of stuff was valuable. I’m not sure what I would’ve done if I had not encountered such a stuff like this. I can also at this moment relish my future. Thanks so much for your skilled and sensible guide. I won’t be reluctant to refer your blog post to anybody who desires assistance on this subject.

  21. I together with my friends came checking the great information located on the blog and then all of the sudden developed a terrible suspicion I had not thanked you for those tips. These guys happened to be as a result happy to read them and already have quite simply been taking advantage of these things. Appreciation for simply being indeed considerate and then for choosing variety of superior guides most people are really needing to learn about. Our honest regret for not expressing gratitude to you sooner.

  22. I have to express my appreciation to you for bailing me out of such a challenge. Just after exploring throughout the world-wide-web and coming across tricks which were not helpful, I figured my entire life was over. Living devoid of the approaches to the issues you’ve fixed as a result of your main guide is a crucial case, and those which might have in a wrong way damaged my career if I had not discovered the website. Your own ability and kindness in taking care of all areas was priceless. I don’t know what I would have done if I hadn’t encountered such a solution like this. I can now relish my future. Thanks for your time so much for your high quality and amazing guide. I won’t be reluctant to suggest your blog to anybody who would like assistance about this matter.

  23. I precisely needed to appreciate you all over again. I’m not certain the things I could possibly have carried out in the absence of the type of suggestions revealed by you over this area of interest. It became an absolute scary issue in my opinion, but observing the very specialised form you dealt with it took me to weep with gladness. Now i am thankful for your assistance as well as pray you comprehend what an amazing job that you’re putting in instructing most people using your webpage. I am sure you haven’t encountered all of us.

Leave a Reply:

Your email address will not be published. Required fields are marked *