Entity Framework VS LINQ to SQL VS ADO.NET with stored procedures?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

http://stackoverflow.com/questions/494816/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

Answer 4

First off, if you're starting a new project, go with Entity Framework on .NET 4.0 - it now generates much better SQL (more like Linq to SQL does) and is easier to maintain and more powerful than Linq to SQL. 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.

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

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.

Answer 5

I think EF is considered to be more suitable for Enterprise solutions when LINQ to SQL is better for smaller solutions.

I like EF for its support of Stored Procedures for inserting/updating/deletion of entities. (I'm using this T4 template to generate them from the .edmx file)

Answer 6

You can't beat pure ADO.NET (with stored procedures) in terms of perfromance. Check performance scorecard here: http://ormbattle.net/