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

Question

How would you rate each of them in terms of    Performance Speed of development Neat  intuitive  maintainable code Flexibility 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   -

User · Answer

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    Depends on the quality of the O RM solution  L2S is pretty good at generating SQL This is normally much faster using an O RM once you grok the process Code is also usually much neater and more maintainable Straight SQL will of course get you more flexibility  but most O RM s can do all but the most complicated queries Overall I would suggest going with an O RM  the flexibility loss is negligable

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

Examples related to sql

Examples related to linq-to-sql

Examples related to entity-framework

Examples related to ado.net

Examples related to linq-to-entities