Linq to SQL, Linq to Entites or Ado.net for web 2.0 project

[mudskipper]

I stand corrected. Now I'm confused what LINQ technology it was I used in the past since it definitely wasn't against MSSQL!

Linq can be used to query all sorts of things. But Linq to SQL talks to SQL Server.

[bilgehan]

Thanks for responses especially Gentile for the comprehensive answer..

I agree changing database in the future will be hassle. I think it's best to with MySql from the beginning as the MSSQL price is too high for me.

As Linq to SQL is out of question, I think using ADO.NET with stored procedures versus Linq to Entities will perform better. As I am a C developer writing more code in favor of better performance is ok for me. After the answers I am inclined to use MVC+ADO.NET.

[woohoo]

Gentile has given a pretty comprehensive answer, though I would like to add a suggestion. If performance is paramount then PetaPoco or Dapper may be something that you should look at.

They are called micro-orms but at heart they are data mappers, so think ado.net but you cut out a lot of the boiler plate work of mapping the results to an object. I'm sure they do a great deal more but that's how I think about them in my brain.

EF is pretty good and in the latest version they have improved performance a fair bit, take a look at this Improve Performance with Entity Framework 5

[LumbaBread]

I would stay away from any ORM to be honest. Yes they may save time in the beginning as you don't have to write most of the DAL, but in the end you will hit performance issues or bugs which are not always easy to work around. The SQL these ORMs generate is generally horrific.

If performance is important I would definitely go with ADO .NET. Yes you will spend more time writing your DAL, but if you're any good with SQL you will get the best performance out of it.

If you're talking about a small project with a relatively small database then any ORM will do and will probably save you a bit of time.

Now let me qualify this statement a little bit by looking at the actual ORMs discussed in the initial post.

Linq To Sql has a nasty habit of saving connections strings in the settings file which is then built into the dll. If you don't realize this, you will happily build your application, deploy it only to find out it doesn't work and the context can't connect to the database. Of course there are work-arounds . Generally I make sure the connection string is not saved anywhere apart from web.config and make sure the context is initialized against that connection string. As for Linq to Sql not being supported by Microsoft that's a non-issue. It certainly has a place and its mature enough to perform well in specific scenarios.

Entity framework does not have this particular issue and it shines if you have a simple database with a few tables. The convenience of having everything built for you and accessed using Linq is great. That being said, if you have a highly normalized database with lots of tables and the data you need usually comes from quite a few tables put together then the sql Entity Framework and Linq generate is so bad and slow that all the initial convenience is down the drain and your application is useless.

Bottom line, each of them has a role. All you need to do is understand what that role is, and how to play to each tool's strengths and more importantly when not to use it. The classes generated by these ORMs are partial classes which is of course a great benefit. You can usually replace code you don't like with your own and that helps. I've done this with L2S, not with EF though. My main issue is that when you try to do something which is not 99% basic and simple, that's when you run into trouble and start discovering bugs. The time it takes to fix these bugs or find other ways of doing things usually outweighs any initial benefit you might have had.

If you plan to use these through a WCF service, you can, but you must be sure you return DTO ( Data Transfer Objects ) not the ones in your context. If you are clever you can write a very nice service, abstract your DAL away and if you ever need to change to something else, you can, simply by implementing the DAL's interface.

[bilgehan]

Cheers lads. I eliminated ORMs completely. But definitely will have a look at dapper, learned that it is used at stackoverflow.com too.