I love ORM technology. I use a lot of it when building applications. I never did get completely in love with Linq To Sql though. I’ve been using it a lot to teach ORM fundamentals just because the learning curve is really low. It just hasn’t appealed to me for production systems.
In my current project I’ve had reason to revisit the reasons why Linq to Sql failed to appeal me and this is the top 10 list.
10) A lot of the generated T-SQL queries are unnecessary complex. Take a look at the query generated by a simple “firstName like ‘%a%’” kind of query:
SELECT [t0].[id], [t0].[firstname], [t0].[lastname], [t0].[streetaddress], [t0].[city], [t0].[zipcode], [t0].[email] FROM [dbo].[Director] AS [t0] WHERE ( (CASE WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0 ELSE CHARINDEX(@p0, [t0].[firstname]) - 1 END)) > @p1
9) Associations between entities in most cases means keys has to be expressed twice in the model (once in the child entity and once in the parent entity). This leads to awkward entity design.
8) There is no batch fetch or batch insert/update for when you want to send a lot of rows to the database in one go. This seriously cripples Linq To Sql’s ability to handle anything but smaller object graphs.
7) Linq To Sql lacks the ability to do many-to-many relationships.
6) Inheritance is limited to one of the least useful scenarios; discriminator columns.
5) You can’t break up a big table into a nice object graph. There is no support for components so you can’t have a table that look like this:
And have an entity that looks like this:
4) You can’t map your own value objects. So instead of a reusable class like this:
public class Director { ... public Email Email { get; set; } ... }
You need to do this:
public partial class Director { ... partial void OnEmailChanging(string value) { _validator.ValidateEmail(value); } ... }
3) There is insufficient support for loading N+1 relationships. Linq To Sql only supports joins of 1+1 level graphs, for N+1 it starts throwing out selects. I have blogged about this before: Linq To Sql: Support for Eager Loading, Really?
2) It’s table driven and enforces the database design upon your entity model, leaving you no room for clever modeling in code that aren’t available in relational table structure models.
1) Microsoft has announced that Entity Framework is their recommended data access strategy.
http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx
So to sum up, Linq To Sql just don’t meet my needs for a competent ORM that allows me to model my application as I wish. It doesn’t give me the high performance from the data access I want either.
If the limitations I see don’t worry you are aren’t applicable in your projects, by all means use it.
#1 by arjuns on September 1st, 2009
Nice post,
Nice to read your blog.
#2 by Frans Bouma on September 17th, 2009
10) ok that one’s bad :D
9) unavoidable
8) sqlbulkcopy (see also Ayende’s blog about batching in NH during the ormbattle.net crap). Batching isn’t for o/r mapping: the more you try to batch the less you have to work with: per-entity functionality, per-entity error reporting, it’s all out the window
5&4 are the same argument really: one could see a second entity on the same table as a valuetype as it’s never really a separate entity, it always depends on the main entity it has a 1:1 relationship with.
point 2 is interesting. Care to explain 2? :) I mean, if you mean other things than ‘I can’t define a base class for the l2s entities…’
#3 by KristoferA on September 17th, 2009
Would you mind sharing the Linq query that generated the SQL listed in #10? I have never see anything like that come out of L2S…
#4 by Patrik Löwendahl on September 26th, 2009
:
9) It isn’t, NPersist solves it automagically for instance.
8) Yes, for huge inserts SqlBulkCopy is the way to go, but you still want an option to batch smaller sets as well, both for inserts, updates and selects.
5&4) Not really, 4 is about using your own data types, the second is about using value objects that are more complex. Both NPersist and NHibernate implement them as separate solutions.
2) It’s about inheritence, it’s about many-to-many, it’s about splitting tables into separate entities, it’s about joining tables into one enity etc etc. LTS as a whole enforces you to model your classes as an exact copy of the tables you have in the database. Thus you will make compromises in your usage of OO techniques to facilitate the relational model in your code.
OO and relational models are different, they have different strenghts and weaknesses. OO handles code better then relational does and relational stores and queries better then OO. In this setting the ORM are supposed to help us use OO fully in the code while still modelling the data storage using relational models. LTS don’t allow this.
#5 by Patrik Löwendahl on September 26th, 2009
@ kristoferA:
var query = from item in ctx.items
where item.name.indexof(’a') > 0
select item;
EF 4.0 properly translates this to a like, LTS and EF 1 doesn’t. NHibernate and NPersist uses a Like operator instead.
This is just the simplest of many examples where the LTS query engine takes a very naïve approach to the queries generated. Which results in sprocs.