Linq 2 SQL or Linq Entities

Question

I am starting to design a new application and what I am wondering is peoples opinions on Linq2SQL or Linq2Entities and what they feel is the better technology for rapid development.

I am also doing some research into ADO.net data services.

Answer 1

I would say that for a simple to moderate database schema, Linq2SQL works very well and is easier to set up and use. This is what I use for my ORM with some small adjustments through partial classes to support validation and authorization/auditing. I use the DBML designer and add my tables/relations. I change the DataContext to make it abstract and build a concrete implementation that allows me to provide implementations of my table-valued functions/stored procedures (which map into the data context as methods) that provide hooks for auditing and authorization. I implement partial methods on the entity classes for OnValidate and OnLoad to do both validation and authorization at the table level. I find that this is pretty much all I need. Lately, I've been defining an interface and wrapper for the concrete data context as well to allow me to mock it out in my unit tests.

Answer 2

I'm a big fan of LINQ to SQL if you meet the following design requirements:

• MS SQL Server as the DB engine
• RAD development
• 1 - 1 class mapping is all that's required

I've not done a whole lot of work with Entity Framework but from what I know and what I have done is that it doesn't have as-good performance when generated from the same database as LINQ to SQL uses.

The lower performance is due to the nature of Entity Framework, it uses ADO rather than specific providers for the database server you're using.

Answer 3

My vote goes to Linq-to-SQL. It fits your rapid development scenario; easy to get started in, easy to use. In addition it generates good/efficient SQL queries from Linq expressions.

Linq-to-Entities is clunky, if you try to use any of the 'advanced' features that is supposed to set it aside from L2S then you will have to start editing your EDMX model file using an XML editor (you'll soon run into 'limitations' in the designer where the only workaround/solution recommended by Microsoft is to use an XML editor to hand-crank the EDMX). On top of that it tends to generate really poor/inefficient SQL queries.

Microsoft says that the next version of Entity Framework will be a lot better and will support all the goodies of L2S. However the next version won't be released anytime soon so until then L2S is your best bet.

Answer 4

Linq to SQL is a dead end these days because Microsoft is not going to update it anymore. I used it for a little while for my own projects, but I found it underpowered in comparison to real SQL. Of course it might seem easier to run in the short run, but over the course of your application's development cycle you will find that you enjoy the power of SQL more.

I think LINQ TO SQL should only be adopted by those who are severly dependent on the framework's abstraction layers and don't have the time/energy/desire to pursue SQL.

One more thing that you should remember is that the extra layer between SQL and your application has its own cost. The speed difference isn't something that you'll easily notice, but it's there.

Personally I recommend that someone who's starting out should graduate straight to SQL, and give LINQ to SQL a miss.

Answer 5

I'd recommend looking at non-microsoft solutions for ORM. nHibernate is a great solution which has all the goodies of both of those frameworks plus more. Yes its a steeper learnign curve but fluent nHibernate helps with that. Its well worth the effort.

Answer 6

No one can say You defenitly what is better.

Both techs have a problems(NHibernate have a problems too).

I'm using Linq-to-SQL and preatty happy with it. For my opinion problems in Linq-to-SQL is less than in EF ^_^.

Answer 7

For use with ADO.NET Data Services (which you mention), Entity Framework is the one that works out of the box. If you want to update data with LINQ-to-SQL (via ADO.NET Data Services), you need to do some work to implement IUpdatable. Luckily I've been blogging about that this week.

My overall thoughts between the two are covered here, but I've softened a little since then, see here.

Basically, at the moment I prefer LINQ-to-SQL, but I expect EF to be more usable in the next version. Hence why I am working to get LINQ-to-SQL working with ADO.NET Data Services.

Answer 8

I think Linq 2 Sql is an excellent choice. A few points:

• It is really fast, I remember reading a blog post over at Rico Mariani's Performance Tidbits during L2S beta, where he measured it to be almost as fast as plain old ADO.Net, and that was during its beta.
• You can do both linq queries as well as work with stored procedures and good old sql if you like that, and still get the data to object mapping done for you.
• The fact that Stackoverflow uses L2S proves that it can work on a large scale website.
• It is much more lightweight than the Entity Framework, which is good and bad depending on what you need. In general, if your needs are not super advanced, you can usually work around any issue pretty quick.

Answer 9

Yes, agreed with Slace.

Just be careful on the framework you do choose, to ensure it meets all your needs.

For instance, I recently gutted out Entity Framework from a work project after working with it pretty solidly over the last couple of weeks, as it did not facilitate my needs, mainly due to:-

1. The things you can't do in Linq to Entities (such as mapping to .net enum types (grr) and the aggravation of receiving 'NotSupportedException' at nearly every turn if you try to get fancy in your linq query statement by calling on function or method calls (see link)).
2. Lack of native Lazy Loading (I understand there is tools such as EF Lazy LoadGen to facilitate this, but it was not something I wanted to encorporate).

Other than that, commands and the framework seemed straight forward and neat, and the reason I went with EF was:

1. I believed EF was targeted more for enterprise development and thought L2S was more for hobbyists and was a limited framework. However with further understanding and personally, not needing anything in EF I couldn't do with L2S, I am happy with L2S. Especially if it suits stackoverflow, scalability and efficiency is covered for me.
2. Option for multiple DBMS' (I'm yet to see this in action however)
3. It was rumored Microsoft was dropping support and investment on Linq to SQL.
4. I love the fact you can update tables and DB changes within the EF .edmx without having to delete the existing schema model (which you are forced to do in Linq to SQL). Albeit, not super annoying unless you've customised any properties in your L2S schema (.dbml).

Further reading (another SO post):
http://stackoverflow.com/questions/252683/is-linq-to-sql-doa

I would love to choose EF, I really do not know what to make of the L2S vs. EF debarcle, and if L2S really is a dead duck, shrug. my main gripe admittedly with EF is the NotSupportedException's - I could get around lazy loading if I could perform method calls in linq without getting this...

Answer 10

We thought L2S was great until we tried to do updates. Then it was pathetic. My colleague was doing most of this work while I did other things. He talked about EF and the ways in which excessive configurability made it messy to use.

I suggested that since we target MSSQL and that isn't going to change, he could hack out all the database provider abstraction stuff. Some time later he told me it was a good suggestion and his code was much simpler and less fiddly to maintain.

---

I'm curious as to the basis on which this answer has been voted down. It describes actual experience, and it discusses an alternate strategy and the relative success of that approach. Down-voting is for answers that are misleading, factually incorrect or just plain trolling.

As it happens I have changed my position on the whole EF vs L2S thing, but that doesn't change the fact that down-voting something merely because it expresses an opinion different from your own is infantile and thoroughly counter to the spirit of StackOverflow.

Answer 11

This is a pretty old question, but the LinqToSql cheerleading of the top voted answers concerns me. There are significant drawbacks to LinqToSql.

Do not use the Visual Studio 2008 LinqToSql O/R Designer

The drawbacks of adopting Linq To Sql

That said, there are significant drawbacks to EntityFramework as well.

There are much, much better options available (NHibernate being the all around best option right now).