445

How would you rate each of them in terms of:

  1. Performance
  2. Speed of development
  3. Neat, intuitive, maintainable code
  4. Flexibility
  5. Overall

I like my SQL and so have always been a die-hard fan of ADO.NET and stored procedures but I recently had a play with Linq to SQL and was blown away by how quickly I was writing out my DataAccess layer and have decided to spend some time really understanding either Linq to SQL or EF... or neither?

I just want to check, that there isn't a great flaw in any of these technologies that would render my research time useless. E.g. performance is terrible, it's cool for simple apps but can only take you so far.

Update: Can you concentrate on EF VS L2S VS SPs rather than ORM VS SPs. I'm mainly interested by EF VS L2S. But am keen to have them compared against stored procs too since plain SQl is something I know a lot about.

BritishDeveloper
  • 13,219
  • 9
  • 52
  • 62
  • 11
    This is an excellent question in my view. Personally, I have noticed Entity Framework and all similar ORMs out there slower compared to plain/simple ADO.Net code. I did this test 2 years ago and then again a week back. I am not sure about how LINQ to SQL compares with EF. But ADO.Net will always be the best in performance. If you want to save dev time, then Entity Framework is a good tool but definitely not when performance is your primary concern. – Sunil Feb 01 '14 at 05:46
  • 1
    @LukLed What is the reason that less stored procedures are used nowadays? – Timeless Apr 17 '14 at 09:50
  • 2
    @Timeless: There is a trend not to rely on database mechanisms. Every database engine has its own stored procedure language, so there is additional learning. 99.9% of developers can rely on ORMs, that produces quite good code and create SQL automatically. Performance difference is marginal in case of simple CRUD. Stored procedures are harder to develop and maintain. Few years ago, when there were no ORMs and nothing was generated magically and automatically of database. Writing SPs was considered not so time consuming, since it was alternative to writing SQL statements in application. – LukLed Apr 17 '14 at 10:29
  • 4
    @Sunil is correct, though not wordy enough. The issue is that **everybody** thinks their primary concern is app performance. When I talk about apps that require top performance, I think of hardcore C++ MMO's or high-volume customer-facing database transactions in the high millions. You really should focus on object-oriented principles like **maintainability**, **readability**, **persistence ignorance** and **domain logic separation**. Especially when performance increase is minor-at-best or non-existent in many cases. – Suamere Jun 11 '14 at 20:22
  • have a look at http://weblogs.asp.net/fbouma/fetch-performance-of-various-net-orm-data-access-frameworks – Anshul Nigam Jul 27 '15 at 10:59

5 Answers5

441

First off, if you're starting a new project, go with Entity Framework ("EF") - it now generates much better SQL (more like Linq to SQL does) and is easier to maintain and more powerful than Linq to SQL ("L2S"). As of the release of .NET 4.0, I consider Linq to SQL to be an obsolete technology. MS has been very open about not continuing L2S development further.

1) Performance

This is tricky to answer. For most single-entity operations (CRUD) you will find just about equivalent performance with all three technologies. You do have to know how EF and Linq to SQL work in order to use them to their fullest. For high-volume operations like polling queries, you may want to have EF/L2S "compile" your entity query such that the framework doesn't have to constantly regenerate the SQL, or you can run into scalability issues. (see edits)

For bulk updates where you're updating massive amounts of data, raw SQL or a stored procedure will always perform better than an ORM solution because you don't have to marshal the data over the wire to the ORM to perform updates.

2) Speed of Development

In most scenarios, EF will blow away naked SQL/stored procs when it comes to speed of development. The EF designer can update your model from your database as it changes (upon request), so you don't run into synchronization issues between your object code and your database code. The only time I would not consider using an ORM is when you're doing a reporting/dashboard type application where you aren't doing any updating, or when you're creating an application just to do raw data maintenance operations on a database.

3) Neat/Maintainable code

Hands down, EF beats SQL/sprocs. Because your relationships are modeled, joins in your code are relatively infrequent. The relationships of the entities are almost self-evident to the reader for most queries. Nothing is worse than having to go from tier to tier debugging or through multiple SQL/middle tier in order to understand what's actually happening to your data. EF brings your data model into your code in a very powerful way.

4) Flexibility

Stored procs and raw SQL are more "flexible". You can leverage sprocs and SQL to generate faster queries for the odd specific case, and you can leverage native DB functionality easier than you can with and ORM.

5) Overall

Don't get caught up in the false dichotomy of choosing an ORM vs using stored procedures. You can use both in the same application, and you probably should. Big bulk operations should go in stored procedures or SQL (which can actually be called by the EF), and EF should be used for your CRUD operations and most of your middle-tier's needs. Perhaps you'd choose to use SQL for writing your reports. I guess the moral of the story is the same as it's always been. Use the right tool for the job. But the skinny of it is, EF is very good nowadays (as of .NET 4.0). Spend some real time reading and understanding it in depth and you can create some amazing, high-performance apps with ease.

EDIT: EF 5 simplifies this part a bit with auto-compiled LINQ Queries, but for real high volume stuff, you'll definitely need to test and analyze what fits best for you in the real world.

Callum Watkins
  • 2,844
  • 4
  • 29
  • 49
Dave Markle
  • 95,573
  • 20
  • 147
  • 170
  • 36
    Absolutely brilliant answer. I am now indeed using EF to speedily develop an app. If performance becomes an issue, stored procs will be brought in to refactor the badly performing EF queries. Thanks! – BritishDeveloper Jun 15 '10 at 13:06
  • 17
    @BritishDeveloper: Also, don't forget the power of using views as well. We've had great success with defining a couple of views in our L2S projects and leveraging them where the framework is seeming to write poor queries. That way, we get all of the benefits of using the framework with all of the benefits of writing our own SQL. – Dave Markle Jun 15 '10 at 15:43
  • 5
    I am also using Views ;) More as a work around for the non-cross db limitation of EF. Good idea for use for optimisation though. Thanks – BritishDeveloper Jun 16 '10 at 10:37
  • 5
    Definitely a great response. Just wanted to add an observation I had in my own experience with L2S vs EF4. Swapped from L2S -> EF4 after we realized we may be using several differnet RDMBS... but, while still running MSSQL the performance drop showed mainly in the GUI area of my app. Databinding to the resultset in L2S was much faster than EF4. It's the exact same query on the exact same DB. One thing to note here though is I'm returning 90k+ records so the difference was pretty obvious. Maybe on smaller sets it's not a problem? Not sure how it would scale though with high vol sites... – bbqchickenrobot Jun 25 '10 at 23:43
  • 5
    Good response. I just spent 4 weeks working with LINQ-to-Entities, trying to shoehorn everything into it, before finally realizing that you need native SQL for things like bulk copying, bulk deletion, ultra fast removal of duplicates from a database, etc. Use the right tool for the job, there is no shame in mingling native SQL with the LINQ to Entities framework. – Contango Feb 20 '11 at 16:08
  • 3
    EF is much better to use and keep. Code First is something that .NET was missing. I'd go with SQL for complex scenarios and EF for almost anything else. If there is any performance issue on some specific item, just test that item and check if it's not the source code and if it's really the EF, change to plain SQL. Tip: if possible, keep your SQL string inside a single class and just reference them (so you change the SQL just once and avoid duplicate coding and all). – Anderson Matos Dec 15 '11 at 11:22
  • 1
    This is my favorite line: "The only time I would not consider using an ORM is when you're doing a reporting/dashboard type application where you aren't doing any updating" I build complex reports and dashboards for a living in a 3rd party database we are not allowed to edit any data for, but can add our own reporting sprocs. ORM's do not make any sense for this type of application development, so I am glad to see it mentioned here as not a good fit. – MikeTeeVee May 27 '14 at 03:39
  • One of the very few answers which gives detailed insights whilst keeping you on track. I have seen a few in past where they bombard answers with jargons which don't make sense. Kudos to you though. – roughblot Feb 08 '21 at 03:47
97

Stored procedures:

(+)

  • Great flexibility
  • Full control over SQL
  • The highest performance available

(-)

  • Requires knowledge of SQL
  • Stored procedures are out of source control
  • Substantial amount of "repeating yourself" while specifying the same table and field names. The high chance of breaking the application after renaming a DB entity and missing some references to it somewhere.
  • Slow development

ORM:

(+)

  • Rapid development
  • Data access code now under source control
  • You're isolated from changes in DB. If that happens you only need to update your model/mappings in one place.

(-)

  • Performance may be worse
  • No or little control over SQL the ORM produces (could be inefficient or worse buggy). Might need to intervene and replace it with custom stored procedures. That will render your code messy (some LINQ in code, some SQL in code and/or in the DB out of source control).
  • As any abstraction can produce "high-level" developers having no idea how it works under the hood

The general tradeoff is between having a great flexibility and losing lots of time vs. being restricted in what you can do but having it done very quickly.

There is no general answer to this question. It's a matter of holy wars. Also depends on a project at hand and your needs. Pick up what works best for you.

AGuyCalledGerald
  • 7,882
  • 17
  • 73
  • 120
  • 47
    I don't think the points regarding source control are relevant. The database schema, stored procedures, UDFs etc can all be, and should be under source control. – Lee Gunn Mar 27 '11 at 16:22
  • 3
    Agreed, the source control argument is incorrect. We always store our database creation scripts in svn. How can you even keep the database outside source control when developing a database application? – Wout Jul 29 '12 at 19:30
  • 3
    EF isn't really suitable for high availability and high performance, I'm not a DBA guy but I don't see what EF offers which makes it easier to code. – Jamie Apr 05 '13 at 14:41
  • 4
    So, we're giving up flexibility, full control and performance for "rapid development" and avoiding code changes if DB changes. Sounds like pure laziness to me. – user2966445 Aug 26 '14 at 16:40
  • I can agree that the source control argument may not hold water... I currently keep my database structure maintained in an Sql Project within Visual Studio, so any time I make any changes to any script files, I one-click publish my changes to the database within Sql Server and also push my changes to a Git repo. – Mark Entingh Aug 01 '17 at 19:49
18

your question is basically O/RM's vs hand writing SQL

Using an ORM or plain SQL?

Take a look at some of the other O/RM solutions out there, L2S isn't the only one (NHibernate, ActiveRecord)

http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software

to address the specific questions:

  1. Depends on the quality of the O/RM solution, L2S is pretty good at generating SQL
  2. This is normally much faster using an O/RM once you grok the process
  3. Code is also usually much neater and more maintainable
  4. Straight SQL will of course get you more flexibility, but most O/RM's can do all but the most complicated queries
  5. Overall I would suggest going with an O/RM, the flexibility loss is negligable
Community
  • 1
  • 1
BlackICE
  • 8,816
  • 3
  • 53
  • 91
  • @David Thanks, but it is not ORM vs SQL. I am looking to move to ORM and am wondering which to invest time in learning: EF or L2S (unless they are rubbish compared to Stored Procs) – BritishDeveloper Apr 23 '10 at 13:53
  • 1
    I'd definitely say they're not rubbish compared to stored procs, and a side benefit is that you don't have code spread to the database. Personally I like L2S, but I haven't done much with EF at this point, and it appears that L2EF is going to supplant it, so I'd go EF. Also, once you go Linq, you don't go back. – BlackICE Apr 23 '10 at 14:27
14

LINQ-to-SQL is a remarkable piece of technology that is very simple to use, and by and large generates very good queries to the back end. LINQ-to-EF was slated to supplant it, but historically has been extremely clunky to use and generated far inferior SQL. I don't know the current state of affairs, but Microsoft promised to migrate all the goodness of L2S into L2EF, so maybe it's all better now.

Personally, I have a passionate dislike of ORM tools (see my diatribe here for the details), and so I see no reason to favour L2EF, since L2S gives me all I ever expect to need from a data access layer. In fact, I even think that L2S features such as hand-crafted mappings and inheritance modeling add completely unnecessary complexity. But that's just me. ;-)

Community
  • 1
  • 1
Marcelo Cantos
  • 181,030
  • 38
  • 327
  • 365
  • 1
    L2S is pretty good, but has the downside that Microsoft have stated that they're basically not going to invest much in extending it. You can see the results of this already in the latest version which only has a few bug fixes and some support for SQL Server 2008 datatypes added. – FinnNk Apr 23 '10 at 12:04
  • Agreed, @FinnNk. It's an unfortunate reality that using L2S is somewhat risky due to it's pariah status. But if they really did fob it off completely in favour of L2EF, I strongly suspect there would be a migration path, due the following it still enjoys. – Marcelo Cantos Apr 23 '10 at 12:13
  • 3
    Linq to EF has matured, and now produces SQL as good as L2S (as of .NET 4.0). L2EF is a lot nicer than L2S nowadays, since it at least can update your model as the DB changes, which L2S could never do automatically. I also like the fact that you can map simple M:M relationships with EF as relationships without needing to have an intermediate entity. It makes the code that much cleaner. – Dave Markle Apr 23 '10 at 12:14
  • 2
    Thanks for the update @Dave. I disagree with the M:M comment, however. The schemas I've worked with almost always grow extra attributes on join tables. This induces a structural change to the object model, requiring a lot of code rework. I would much rather deal with the intermediate relation explicitly from the outset. – Marcelo Cantos Apr 23 '10 at 12:26
1

There is a whole new approach that you may want to consider if what you're after is the power and performance of stored procedures, and the rapid development that tools like Entity Framework provide.

I've taken SQL+ for a test drive on a small project, and it is really something special. You basically add what amounts to comments to your SQL routines, and those comments provide instructions to a code generator, which then builds a really nice object oriented class library based on the actual SQL routine. Kind of like entity framework in reverse.

Input parameters become part of an input object, output parameters and result sets become part of an output object, and a service component provides the method calls.

If you want to use stored procedures, but still want rapid development, you might want to have a look at this stuff.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Vincent
  • 842
  • 7
  • 13