LINQ to SQL vs. Entity Framework

Wednesday, April 04 2007 - Data Access

A recent LINQ forum question asked about the differences between Linq to SQL and the Entity Framework.

This is the response that I wrote and thought I would surface it in my blog:

 Besides that Linq to SQL is only for SQL () one of the big differences is that the EDM is more flexible and more loosely coupled because of the 3-tiered model (conceptual layer, source schema and the mapping layer in between).  For example, you could have one conceptual layer and then multiple mapping layers that point to mulitple databases. In LINQ to SQL, your dbml properties are tightly bound directly to one particular field in a table.

While these are not in the March 2007 CTP, EDM is getting the ability to build views of the conceptual layer as well as to write stored procedures in the mapping layer. These are really cool features. I don't believe you can do this with Linq to SQL, but a) I could be mistaken and b) that may be something that is forthcoming.

In addition to Linq to Entities, Entity SQL can be used to query entities. This can be either through the object services API or the Entity CLient (the one which gives you connections/commands and results in a dbDataReader). Entity SQL, while not as elegant as using the strongly typed LINQ, has the advantage of enabling dynamic queries, since you use a string to build a query, much like TSQL.

Both Linq to SQL and EDM allow inheritance and extending the code generated class with addtional partial classes. EF allows many to many relationships. I believe that LINQ to SQL will NOT be getting this by RTM.

These are just a few points and hardly exhaustive. But to me they are the low hanging fruit.

Tagged as:

#1 Vikas Tyagi on 7.01.2009 at 1:14 AM

Hmmmm was a good article but somethins were missing.. but still worth reading...

#2 One more techie on 10.21.2009 at 5:24 AM

Thanks for this article Julie. Nice and concise, talks about what exactly is needed.

Cheers,

S

#3 JoelMMCC on 11.02.2009 at 5:45 PM

On the other hand, LINQ-to-SQL actually works. EF produces horribly inefficient (and in many cases flat-out incorrect) SQL for the same LINQ queries that LINQ-to-SQL produces nice, efficient queries (see: social.msdn.microsoft.com/.../3a7f7141-9243-4 for more info).

LINQ-to-SQL Visual Designer has always had an “Auto-Sync” property that tells LINQ how to handle Identity, Default-valued, Computed, and other automatically generated columns. It has simple, easy-to-understand value names such as “OnInsert”, “OnUpdate”, and “Always”. It’s automatically generated for Identity columns, and easy to set through the Property Window for other cases, and remains set through most changes to the model.

EF, on the other hand, has the horribly named “StoreGeneratedPattern” property intended to do the same thing. Its values are “Identity” (= “OnInsert” which is appropriate not only for true Identity columns, but also for, for instance, “Created” columns that default to the current database server date and time of INSERT — but you’d never know that from the property name, and it isn’t automatically generated in those cases) and “Computed” (analogous to “Always” [though I may be wrong about that] — again, suitable for more than just Computed columns, not that you’d know that from the name) — there appears to be no equivalent to “OnUpdate” (unless “Computed” = “OnUpdate” instead of “Always” but that makes less sense — which seems to be the modus operandi of the EF team, though).

Worse still, the Property was completely inaccessible from the Entity Model Designer as of VS2008 SP1. To use it for column in which it wasn’t auto-generated (any appropriate column other than a true Identity or Computed column, respectively), you had to close the Entity Model Designer, then re-open the .EDML using Open With XML Editor, then HAND-ADD the “StoreGeneratedPattern="Identity"” (or “…="Computed"”) attribute to the entries for those columns in the SSDL section, then declare the “annotation” namespace (if not already declared) and add it to the CDSL section, with “annotation:StoreGeneratedPattern="Identity"” annotations on the corresponding entries of the CDSL section!

And if you updated the Model from the Database, you’ve got the fun of doing that all AGAIN!! Each and every time!

In VS2010 as of Beta 2, “StoreGeneratedPattern” DOES exist in the Designer, but it doesn’t fully work as it should. It ONLY puts the annotation in the CDSL section, which only affects how a database would be generated from the model, not how LINQ handles the model! If you forget to MANUALLY add the attribute (not annotation!) to the corresponding columns in the SSDL each and every time you regenerate, then LINQ-to-Entities will irrevocably CORRUPT THE DATA in those columns, wiping out what they should be and replacing them with NULLs (if the column is Nullable) or some minimum value (e.g. "0001-01-01 00:00:00.0000" for a non-nullable DateTime column for a “Created” column)!

I just got word from the EF team at Microsoft’s MSDN forum that they have NO INTENTION of fixing this before VS 2010 ships, and that it will have to wait for the next major version!

EF is just plan Not Ready for Prime Time, and won’t be for years to come.

#4 jiang on 11.20.2009 at 9:22 AM

Don't do any of those things linq or EF, just write your own ado.net code and CodeSmith-automate the rest. Otherwise, you will scream hell as the endless upgrades and fixes break and break your code over and over again. Just say no to ORM.

#5 Jim on 1.05.2010 at 3:58 PM

Very informative, still trying to wrap my head around EF and Linq so this is a great start.

<a href="http://www.cuyx.com">Jim</a>

#6 dp.bldr on 1.30.2010 at 1:09 PM

Julie and the commenter Joel--good start. Maybe you 2 should collaborate and rewrite this once 2010 is out.

Leave a Comment