First off, if you're starting a new project, use Entity Framework ("EF") instead of Linq to SQL because it now generates far better SQL (more like Linq to SQL does) and is easier to maintain ("L2S"). Since.NET 4.0 has been released, I believe Linq to SQL to be an old technology. MS has been quite transparent about not pursuing additional L2S development.
1) Performance
Answering this is challenging. You will discover that the performance of all three technologies is roughly comparable for the majority of single-entity operations (CRUD). To use EF and Linq to SQL to their greatest potential, you do need to be familiar with how they operate. You might wish to have EF/L2S "compile" your entity query for high-volume actions like polling queries so that the framework doesn't have to keep regenerating the SQL, else scalability problems may arise. (view changes)
Because you don't have to marshal the data over the wire to the ORM to execute updates, raw SQL or a stored procedure will always perform better than an ORM solution for bulk updates if you're changing enormous volumes of data.
2) Speed of Development
In most cases, EF will outperform raw SQL/stored procs in terms of development speed. In order to avoid synchronisation problems between your object code and your database code, the EF designer can (upon request) update your model from your database as it changes. The only situations in which I wouldn't think about utilizing an ORM are when you're building a reporting or dashboard application without doing any updating, or when you're building an application just to do database maintenance tasks on raw data.
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
Avoid falling prey to the false choice between employing stored procedures or an ORM. Both can be used together in one application, and you should. Large-scale activities should be performed in stored procedures or SQL (which the EF can really call), but the majority of your middle-tier requirements should be met by EF. You might decide to write your reports using SQL. The lesson of the narrative is, I suppose, still the same as it always has been. Use the proper equipment for the task. But the short of it is that EF is excellent right now (as of .NET 4.0).
I hope this helps you.
If you need to know more about SQL, it is recommended to go for the SQL Course today.
