Why I decided to stick with Linq-to-SQL over Linq-to-Entities

I tried to make the switch... but Linq-to-Entities is just so much more work!

Recently I tried, I really tried to like L2E, but I just can't do it! It adds layer upon layers of extra crap I have to do over L2S while offering little to no improvements over the soon to be deprecated ORM. I am officially sticking with L2S until they get L2E better developed. Now I am hardly the first person to write about this, but I feel my concrete examples are somewhat helpful when trying to decide.

Now I have read a bunch of reason of why not to switch, but I stubbornly went along and tried it myself. I like to dumb down my reasons to simple examples of why I have come to loathe L2E (these are all using the same exact DB):


I'll just show you on this one, it may not seem like a lot, but try it for yourself and see how confusing the syntax can get:


<asp:EntityDataSource ID="edsState" runat="server"
  EntitySetName="state" Select="it.[state_id], it.[state_name]">


<asp:LinqDataSource ID="ldsState" runat="server"
  Select="new (state_id, state_name)" TableName="states" />

Now I don't know about you, but the L2S is just easier to understand. Why the hell do I have to use it.[column_name]? That doesn't make any sense. Not to mention the GUI may as well not be there if you are trying to use automatic Delete, Update or Insert.


This one is HUGE! Let's say you have a Foreign Key relation of state_id that relates to a table states and Primary Key that is also named state_id; here is how you do that in both frameworks:


item itm = new item() { item_name = "party" };
itm.state = db.state.First(s => s.state_id = 5));


item itm = new item() { item_name = "party", state_id = 5 };

Now this is a super abbreviated case, but you can see the huge amount of overhead this can create not to mention programming time. I made these as succinct as I could not to try to advantage one over the other and the L2E is just burdensome. Even if I were to make a simple Get() method for state it would still make another call to the DB when I already know the relation integer!!! What an unnecessary step. And this extra work increases like crazy on large object with multiple relations.

Updating your Model

Now I can't relaly show this as an example, so I will just try to explain it. One big pain (I thought) in L2S was the fact that you had to pull your tables back in to your dbml every time you made a change to the DB (I know there are tools for this, but I am talking stock). L2E had this nifty feature that allowed you to 'refresh from the database' that I thought would actually work. The problem is, that if you change anything with any complexity whatsoever, like a Foreign Key, or even something as simple as changing a data type from VARCHAR to INT it seems to completely break your edmx. And this isn't simple to fix like a dbml, not even close. You can't drag a table into a edmx, and you can't seem to fix it through the refresh so what do you do?
  • Delete your edmx completely
  • Go into your web.config and delete the ConnectionString (unless you want duplicates)
  • Completely remake your edmx
  • Redo any changes in the crappy naming conventions to ALL of your tables that you changed (more on this later)
Wow... all that because I changed a relation? No thank you. Dragging that table into the dbml doesn't seem so bad now.

Relations Part 2

This one isn't as big of a deal, but it is annoying.


item itm = db.item.First(i => i.item_id == 12);
string whatState = itm.stateReference.Value.state_name;


item itm = db.items.First(i => i.item_id == 12);
string whatState = itm.state.state_name;

Just simpler in L2S and you don't have to remember the Load() if you forget, and it's in a lot of code, it can be extremely aggravating wondering why your relation is empty. Not to mention that reference.value syntax in there... more unnecessary (in my eyes) crap.

Relations Part 3

And this was the straw that broke the camels back... I was so used to the simplicity and convenience of L2S that this drove me nuts and was the reason I decided to give up. Relations are used so well with L2S that to show relations in something like a Repeater or GridView is almost trivially easy


Who knows?!


<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

but since L2E requires that wonderful Load() method, it is now not so easy - to tell you the truth, I never figured it out how to make it work, probably have to call something on the RowDataBound event, but at this point I figured what is the point?

I am officially resigning from L2E (for now) and going back to L2S... maybe next go-round L2E won't be such a pain? Isn't new technology supposed to make our lives easier? Going from L2S to L2E is like 'upgrading' from a blender to a hand-mixer; it will get done, it will just take you longer and be more frustrating. Hopefully, considering that is the was MS is heading.

Shout it kick it on DotNetKicks.com

Comments (10) -

  • I agree completely.  I tried the L2E and don't like.  My scenarios are actually a little more niche...read-only mini data marts for Business Intelligence.  L2S not only is easier to use in this scenario it performs better.

    Like you mention, until EF gets better I am sticking with L2S for now.
  • Wholeheartedly agree on your points. LINQ to SQL isn't perfect but it's a very good ORM tool and for 90% of cases it does what you need. I spent a lot of time upgrading my app to use L2S and was rather annoyed at MS' approach.
  • Same goes for me.  We've developed our entire new product on L2S, and I'm not about to mention to my boss that we should abandon L2S to go to EF.  Now if they only would listen in Redmond ...
  • Thanks for posting about your experiences.  I'm glad that you have been having some success with L2S, and I'll encourage you to continue using it if it works for you.

    As I look over the specific struggles you've run into, though, it seems like it might be useful for me to give a little more info about EF and future plans.  I'm not trying to argue you into liking the EF, but I hope that you will see we have been listening and are trying ot make the EF into the product that is what you would want to use...

    The reason for the it. syntax in your first sample is that the EF already defines a rich text-based query language (Entity SQL) which has carefully thought-through rules and extensive functionality.  The string language used in the LINQDataSource is pseudo linq.  It has some of the linq capabilities but not all of them plus a number of limitations.  We wanted the Entity Data Source control (which needs text based queries) to allign with the long-term strategy for text based queries in the EF.  So there are some things to learn, but if you learn a little about it, you might find that it's not that bad (the square brackets, for instance, are optional--just generated by the tools for some corner cases, etc.)

    For your second case, you don't actually have to retrieve the entity for the other side with another query to the database.  The syntax is a little onerous, but you can change the second line in your code above to

    itm.stateRefrence.EntityKey = new EntityKey("states", "state_id", 5);

    Which will setup the relationship without requiring a roundtrip to the server.  In .net 4.0 we're working on making this syntax much more natural--probably achieving the same syntax as with L2S.

    Your third point about refreshing the model from the database is something which I think you will find works much nicer in VS 2010.

    For your fourth point, in .net 4.0 we are adding a simple option which you can configure on your ObjectContext which will turn on implicit lazy loading just like L2S so that should get better.

    And for your last point, not only will there be the option in .net 4.0 for lazy loading, but also I will mention that with the current release, the way I would recommend handling that situation is to change your initial query to have an Include statement which will bring back the related entities all in one round trip when you first retrieve the primary entities--this will give you better performance and allow the simple binding syntax.  (You can do the same eager loading trick with L2S today, by the way.)  When you use implicit lazy loading, then every related entity in your repeater will make another roundtrip to the DB.  It's easy to code but the perf is not great.  In any case, though, our intent is to make the EF in its next release (.net 4.0/VS2010) address each of these issues.

    Hope that helps some.

    - Danny
  • All of the things you mention are really simply handled in the current release of EF v1.0. You just need to learn a little more about how it works. I'll agree that L2S is more intuitive though, and it can be quite a steep learning curve for EF. But in my limited experience L2S will lead you to a dead end when you try start wanting to do something even trivially complicated in your mapping or query optimisation.

  • Thanks for the input guys, really good stuff.

    Dan, I really appreciate you taking the time to write in here. I am sure I am going to move to EF, and I have high hopes, I think I might just wait until 4.0.

    It's great to know that MS hears the little guy.
  • LINQ To SQL is really simple to use. Entity Framework offers very great features, but often you you come across some pitfalls when doing relatively simple things.

    By the way, I wonder if anybody thinks that LinqDataSource and EntityDataSource are good things. Personally, I think we shouldn't clutter our ASPX files with data retrieving logic, for this reason I prefer to stick to ObjectDataSource.
  • BTW, if you want to add some fields to your schema in L2S, you can actually just pick the extra fields from your server browser, drag them onto the dbml surface, this will create a duplicate of your existing table.

    After that you select the only fields you've dragged from your duplicate table, copy and paste them into your main table. Delete the duplicate.

    Try it.

  • What is the right order to install SQl server and visual studio?
  • Thanks for posting about your experiences. It's very useful. Emo Haircuts
Comments are closed