Does a stored procedure use such a bad idea?

Question

Microsoft often provided ways to simplify the development of simple and trivial things.

There are certain things that I don't like about EFxx. First of all, the fact that you need to DOWNLOAD the record first to complete the update, so the operation will become a two-step process in which, perhaps, you just need to update the logical value.

Secondly, I like Stored Procedures because I can run 10 different things in the same connection call, where, if I used EFxx, I would have to run 10 separate DB calls (or more if the update was involved) .

My concern and question to the MVC EF guru ... Does stored procedure use such a bad idea? I still see EFxx as another way that Microsoft gives us the ability to develop simple programs much faster, but this is not really the recommended way.

Any hints and tips will be highly appreciated, especially for the concepts of "the best way to start updating on EFxx" and "Stored procedures are bad for EFxx".

Answer 1

You fall into a logical error. Just because EF is designed to work in a certain way does not mean that you should not do it differently. And just because EF may not be good at doing a certain thing in a certain way does not mean that EF sucks or should not be used for anything. This is an argument of "All" or "Nothing." If he cannot do everything perfectly, then it is useless .. and it just is not.

EF is an object-relational mapping tool. You will use it only when you want to work with your data as objects. You would not use it if you want to work with your data as relational sets (also called SQL).

You also don't stick to using EF or nothing. You can use EF for requests and use stored procs for updates. Or vice versa. This is about using the tool that is best suited to the situation.

And no, EFs are not just “simple” or “trivial” things. But using it for more complex scenarios often requires a deeper knowledge of how EF works so that you know what it does under the covers.

Using a stored procedure in EF is as simple as saying MyContext.Database.ExecuteSqlCommand() or MyContext.Database.SqlQuery() . This is the easiest way to do this, and it provides an elementary object for sproc matching, but it does not support more complex ORM functions such as caching, change tracking, etc.

EF6 will more fully support sprocs to support requests, updates, and deletions, supporting more features.

EF is not a magic bullet. It has trade-offs, and you have to decide whether it will be right for you in the circumstances that you are going to use it.

FYI, you are absolutely mistaken in the fact that you need to get the object before updating it, although this is the easiest way to deal with it. EF also implements a unit of work template, so if you make 10 inserts, he is not going to make 10 round trips, he will send them all in one prepared statement.

Just as you can write bad SQL, you can write bad EF queries. Just because you are good at SQL and bad at EF does not mean that EF sucks. This means that you are not yet an expert in this.

So, to your question, no. No one ever said that using Sprocs is a bad idea. The fact is that in many cases sprocs are redundant. They also create an artificial division of your logic into two different subsystems. Writing your requests in C # means that you completely write your business logic in one language, which, like many of the benefits of the service. Some environments require the use of sproc, some do not.

Answer 2

This was asked and talked about many times. Like this one.

There will always be pros and cons for both. It is just a matter of what matters to you. Do you just need simple CRUD operations (one at a time)? I would probably use ORM. Do you perform database operations? Use SP. Do you need to grow fast? Use ORM. Do you need flexibility, so you need full control over SQL? Use SP.

Also, note that you can reduce the number of DB attempts that your context in EF makes. You can read more about the various types of EF downloads . In addition, an ESP call is possible in EF. Reading data using SP and Add / Update using SP .