Is using Stored Procedure such a bad idea?

Question

Microsoft has often provided ways to make it easy to develop things that are simple and trivial.

There are certain things that I dislike in EFxx. First and foremost, the fact that in order to do an update, you need to LOAD the record first, so the operation becomes a 2 step process where maybe you just want to update a boolean value.

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

My concern and question to the MVC EF gurus is ... Is using Stored Procedures such a bad idea? I still see EFxx as just another way Microsoft gives us to develop simple programs much faster, but in reality it's not the true recommended way.

Any hint and tip will be much appreciated, specially on the concept of "what's the best way to run an update on EFxx" & "is Stored Procedures bad for EFxx".

Answer 1

You are falling into a logical fallacy. Just because EF is designed to work a certain way doesn't mean you aren't supposed to ever do it a different way. And just because EF may not be good to do a certain thing in a certain way doesn't mean EF sucks or shouldn't be used for anything. This is the All or nothing argument. If it can't do everything perfectly, then it is useless.. and that's just not true.

EF is an Object-Relational Mapping tool. You would only use it 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 (aka SQL).

You're also not stuck with using EF or nothing. You could use EF for queries, and use stored procs for updates. Or the other way around. It's about using the tool that works best for the given situation.

And no, EF is not just for "simple" or "trivial" things. But, using it for more complex scenarios often requires deeper knowledge of how EF works so that you know what its doing under the covers.

Using a stored proc in EF is as simple as saying MyContext.Database.ExecuteSqlCommand() or MyContext.Database.SqlQuery(). This is the most basic way to do so, and it provides rudimentary object to sproc mapping, but it does not support the more complex ORM functionality like caching, change tracking, etc..

EF6 will more fully support sprocs for backing of queries, updates, and deletes as well, supporting more of the feature set.

EF is not a magic bullet. It has tradeoffs, and you need to decide whether it's right for you in the circumstances you're going to use it.

FYI, you're absolutely wrong about needing to get an object before updating it, although that's just the simplest way of dealing with it. EF also implements a unit of work pattern, so if you are doing 10 inserts, it's not going to make 10 round trips, it will send them all as a single prepared statement.

Just like you can write bad SQL, you can write bad EF queries. Just because you are good at SQL and bad at EF doesn't mean EF sucks. It means, you aren't an expert in it yet.

So to your question, no. Nobody has ever said using Sprocs is a bad idea. The thing is, in many cases, sprocs are overkill. They also create an artificial separation of your logic into two different subsystems. Writing your queries in C# means you're writing your business logic entirely in one language, which as a lot of maintenance benefits. Some environments need sproc use, some don't..