5

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".

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
SF Developer
  • 5,244
  • 14
  • 60
  • 106
  • You don't need to load first. You can just set state to `Modified`. –  Jun 07 '13 at 06:33
  • 1
    Why limit to only one approach? I like my LINQ2SQL (think "mini EF") - and I also like what SQL Server can do. While LINQ2SQL (or any ORM, really) can make a number of tasks easy (and do them sufficiently well), it can't always do everything best. I use SPs, Triggers, Hierarchy IDs, Views, etc. Just use them *as appropriate* and/or *where is a measurable advantage*. If the EF (or whatever ORM) is used behind a DAL then any of the gotchas (and there are always some!) are easier to handle. – user2246674 Jun 07 '13 at 06:34
  • I agree with you ...but what I don't understand is how to use Stored Proc using DbContext (see this) http://stackoverflow.com/questions/16977509/dbcontext-vs-objectcontext-using-stored-procedures – SF Developer Jun 07 '13 at 06:37

2 Answers2

8

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..

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • 1
    Are you sure about this ", 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.", i was under impression that EF does not support command batching? – Jurica Smircic Jun 07 '13 at 13:07
  • So what you are saying is that it makes sense to MIX both techniques. Is that right? – SF Developer Jun 10 '13 at 18:20
6

This has been asked and answered many times. Like this one.

There will always be pros and cons to both. It's just a matter of what is important to you. Do you just need simple CRUD operations (one at a time)? I would probably use ORMs. Do you do bulk DB operations? Use SPs. Do you need to do rapid development? Use ORMs. Do you need flexibility such that you need full control over SQL? Use SP.

Also, take note that you can reduce the number of DB trips your context in EF does. You can try to read more about different types of EF loading. Also, calling SPs is possible in EF. Data read using SP & Add/Update using SP.

Community
  • 1
  • 1
aiapatag
  • 3,355
  • 20
  • 24