Linq-to-SQL vs Linq-to-Entities : Revisited

After 2 months of nothing but L2E, I feel I can give a decent comparison

Most likely you have read more than just a few of these comparisons and are tired of the debate. I too disliked L2E from the get-go but decided that my opinion was not totally grounded and that I should really understand L2E before I crap on it, so, I decided to do an entire project at work with it and get down to the nuts and bolts. Now, 2 months, a lot of frustration and some happy customers later, I can give a fully educated opinion on L2E: it is not as bad as I originally thought, in fact, it is pretty sweet in some areas, but still not as easy to use as L2S.

If you have read any of my multiple posts on Entities this will mostly be a re-hash of what I have already said. If not, this is a good summary of my findings and hopefully will help you decide.

dynamic queries

Winner: TIE
Thanks to the geniuses that wrote System.Linq.Dynamic.dll the syntax is *mostly* interchangable between the two languages (which I didn't realize when writing this). If I was forced to pick a winner, it would be L2S as there are more choices for syntax than for L2E, but that can also be viewed as a fault.

linq-to-sql -and- linq-to-entities
var results = db.branch
  .Where("branch_id == @0 || display == @1", 
    new object[] {5, "Hello"});

With L2S, you can do this as well:
var results = db.branch
  .Where("branch_id == 5 || display == \"Hello\"");

working with databound controls

Winner: Linq-to-SQL
This one is a no-brainer. After being lulled into the fact that L2S just works with so many things, I had to work backwards on problems I found with L2E. One annoyance I noticed was that if I was making an update in a GridView, it would error out unless I explicitly had the primary key in the GridView - and if I wanted it hidden, it was necessary to use css; it is documented here.

Another thing I ran across is the fact that you need to use all the required 'Include' statements in order to get a Repeater/GridView to show child values of relationships with Entities where is L2S, you could simply call the relation without any extra work.

multiple databases

Winner: Linq-to-SQL
The choice is quite simple here, as Linq-to-Entities has no native support for multiple databases.

database relation management

Winner: Linq-to-SQL
First of all, simple things such as getting foreign key values with linq-to-entities is much more difficult than in L2S; examples (written as short as I could):

int s_id = dataContext.records
  .First(r => r.record_id == 1).state_id;

int s_id = Convert.ToInt32(
  Entities.records.First(r => r.record_id == 1)

As for putting those relation values into the DB, the clear winner is L2S as well, just take a look at what it takes to set this object 'r' to have a reference to the 'city' which has a key of the integer value of 5:

r.city_id = 5;

r.cityReference.EntityKey = new EntityKey(
  "YourEntitiesNamespace.cities", "city_id", 5);

a few more...

A while back I went over some other reasons L2E annoyed me such as DataSource annoyances, relations (again) and the pain of updating your model.


Linq-to-Entities is fully capable and can do *almost* everything Linq-to-SQL can do, it just takes a bit more code some times; the almost is for multiple database utilization. L2E is a great ORM, but many of us were spoiled with L2S, so it is now up to Microsoft to live up to the expectations that they put out for us with this framework that they are deprecating. I hope and assume it will become a superior ORM, but that is in Microsoft's hands now; for the time being, it is not.

Shout it kick it on

Comments (15) -

  • I decided to use the Entity Framework in my thesis project. It looked pretty good at first, but more I code more frustrated I become.

    I really appreciate Microsoft's intention to abstract data storage, but some times it brings difficulties to coding, for instance the decision to hide foreign keys sure wasn't the best one. Moreover, they are going to bring foreign keys back in the new version that will be shipped with VS2010.

    Then, at first Microsoft did a great job by providing a good support for many-to-many relationships, by hiding junction tables. But how can you add a record to a table that has many-to-many relationship with the EntityDataSource? I don't know... I had to do this in code-behind and as far as I remember I had to make some roundtrips to the database.
  • Nice post..
  • Tried the Entity Framework as well but like Mike said, it becomes frustrating afrter a while.
  • I have been using both linq-to-sql and Entity Framework for quite some time now as my company uses both. I have to say that so far I haven´t found a single reason why I would like any of them over NHibernate (that I use in my private projects). Is there something essential that I have missed or something?
  • Mattias, can you give some reasons why anyone would like NHibernate over the frameworks provided by Microsoft?  Maybe I am missing something as well.
  • First of all it´s a much more mature framework that has a long history in java. Then, in my opinion, it follows the basic rules of OOP allot better then linq-to-sql or Entity Framework does.

    To me it seems that eather of them pretty much just copies the structure of you´r database in to a domain model. In my opinion that is not the point of a ORM. To me a ORM is there to abstract the data access itself away from you´r application and have you create you´r domain model in the way you want it to look without worrying about the persistance. In my opinion NHibernate does this allot better then any other ORM that I have tried.

    Another reason I choose NHibernate is that there is alot of extra tools for the framework that let you work with it in the way you want. There is Fluent NHibernate ( that let you map you´r entities in a more straight forward (in my opinion) fluent way, you have the NHibernate Contrib library ( with Linq-to-NHibernate among other great projects that let you build queries using linq instead of the criteria api or HQL if you like that more.

    Don´t get me wrong, I like trying out new things, and I wouldn´t say that it wasn´t good if I hadn´t tried it out properly. And some day it might mature a bit and work better then any other ORM on the market, and then I will surely switch. But in my opinion it´s not there quite yet, and I think it has quite a long way to go until it reaches the standards of NHibernate.
  • Thanks a lot for the feedback, surely something I need to check out!
  • Some benefits of NHIberante are:
    - use of POCO's, no inheritance or interface required for entities. You can save any class into a database
    - several caching possibilities
    - generation of the database structure from the mapping
    - support for several databases without changing any code
    - many side projects, like fluent nhibernate, nhiberante profiler, nhibernate contrib
    - several ways to query: linq, hql, sql, stored procs, criteria
    - several lazy/eager loading options
    - support for several concurrency strategies out of the box

    Overal NHibernate feels more mature then the new entity framework. It will take microsoft several years to catch up.
  • sorry but I have to say it. EF looks like a crap.

    and now all of you can cover it in some marketing speak, starting how hard they are working on version 2 (which of course will _completely_ brake all your EF projects) which will introduce new concepts (finaly, POCO etc) but will keep the same name...

    but how long should we wait? according to recent blog posts on team blog, it is _almost_ there, just _couple_ of months Smile))

    don't get me wrong, finger crossed that they will make it right this time but why wait till it matures and get popularity (my sane estimation is no sooner than 2010) when as Paco and Mattias said we alreay have NHibernate, which if nothing else follows very nice design practices for DDD.

    And Frans Bauma isn't happy about EF as well, and his oppinion is very valuable, at least for us.
    (and we don't care he sees EF as his competition)
  • IMO the only reason to use EF is because you aren't allowed to use anything else.
  • Subsonic FTW without a second to explain it.

    there are very few things that it does do and if it doesn't its a very easy beast to subdue...
  • Under dynamic SQL I'd also include L2S's ExecuteSql ability, which is an escape hatch to let you work with SQL but not require a stored procedure. E2F adds this in 4.0
  • I'm really tired of Microsoft.

    Since the break between COM and .NET they seem to think that everything goes.

    Two Data technologies conceptual similar (L2S & EF). ONE MUST GO.

    My guess is L2SQL will be discontinued in 2010. No automatic migration tool for EF provided.

    I'm using L2SQL but i'm starting to look at external tools (never used NHibernate - going to look).
    I just want the software to be ugradable without complete rewriting .
    VB6 -> C#/VB.NET(ADO.NET) -> C#/VD.NET(L2SQQL). Every 3 years software is redone to implement MS non-compatible upgrade madness. Thousands of hours.
    Really tired of MS.
  • I believe Microsoft have pretty much said they'll no longer be developing L2S and EF is the strategic direction people should be investing in.

Pingbacks and trackbacks (2)+

Comments are closed