LINQ to SQL vs. Entity Framework

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.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

10 thoughts on “LINQ to SQL vs. Entity Framework

  1. 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.

  2. 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.

  3. Interesting article, but a suggestion, may have been worth including comparison with ADO.Net as well.

    In a recent job interview, I mentioned had done couple of projects using Linq to SQL, and they mentioned that as they had a very large public facing site with personal data, they only used Stored Procedures from a security point of view and therefore didn’t plan to go into either Entity Framework or Linq to SQL.

    I mentioned that you can connect to stored procedures in Linq to SQL, so there was still some benefit, but not as much as using the objects directly. As recent Guathon asked Scott Gutherie the same point and he too admited that the security concerns could be valid.

    Would be good to see something on the security side in a comparison.

  4. Use Subsonic for ORM, It’s quite straight forward and very easy to use, you just need to add few lines and it will generate your DAL in seconds. It has a 1 to 1 mapping to the database, that is it creates a class for each table in the database. I m tring to understand all the buzz about other technologies like ADO.NET, Dataset, Entity Framework, LINQ…I m confused, are they all do the same thing? which is create tour DAL, that is are they all ORM tools?? so what Microsoft has linq to SQL and Entity Framework. that’s whay i still don’t understand..sorry guys I try to know deeply what each technology is for…

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.