ADO.NET EF v LINQ to SQL (take 4)
Sunday, March 1, 2009 at 10:05AM I never thought I would write a 4th part to this saga and war against ADO.NET EF but here I am again. So after getting some real progress on my project I thought I’d try out ADO.NET EF again, give it another chance. I figured maybe the last few times I failed with it, it was just misunderstood. I’m writing this post out of pure anger and disgust I have had to go through with ADO.NET EF.
So I created a branch and started my refactoring. Things were going pretty well since LINQ to SQL and ADO.NET EF have a lot of similarities in how they handle collections and queries. The one thing ADO.NET EF has over LINQ to SQL in my opinion is the fact that you can eager-load on a query context. So instead of having to create a whole new DataContext just to setup eager loading for one query you can just use DB.Products.Include(“Orders”). This is much cleaner and I wish LINQ to SQL supported this.
So, I was able to clean up my queries a bit, it was time to deploy! I deployed to the server only to see…
“LINQ to Entities does not recognize the method 'Double Pow(Double, Double)' method, and this method cannot be translated into a store expression.”
Great, so one of my sorts is for giving each row a score. Then sorting by this score and taking the top N number of rows for the front page and now it doesn’t work. Just the front page, no big deal. Now I thought I would move onto another page.
“LINQ to Entities does not recognize the method 'Boolean Contains[Int32](System.Collections.Generic.IEnumerable`1[System.Int32], Int32)' method, and this method cannot be translated into a store expression.”
Ugh. At this point I’m already frustrated from the lack of basic math translation. Now I cannot use a simple .Contains() method in a query to avoid having to iterate through them all. Again, I guess it was time to move on and be productive. Next page…
“The method 'Single' is not supported by LINQ to Entities. Consider using the method 'First' instead.”
Well, at least they were nice enough to offer a workaround in the error message itself, now that’s quality!
This goes on for about another 4 hours before I finally in utter disgust start letting the expletives fly. Finally time to return to LINQ to SQL. I think it may be time to move on though and give Mindscape’s LightSpeed a try.
The moral of this story – friend’s don’t let friends use EF.
If any of you ADO.NET guys happen to come across this I ask of you, please add this functionality into your framework. Conceptual mapping, table splitting and multi-table inheritance don’t mean a thing if you can’t use some basic LINQ to Entities queries.
ADO.NET EF, LINQ to SQL 
View Printer Friendly Version
Email Article to Friend
Reader Comments (12)
Converting is a pain. But spending all that time in LINQ to SQL just to have to throw it away is going to be more of a pain. If you ever want to move to another database backend, you can't with LINQ to SQL. Your only option is EF.
Anyone converting from EF to L2S would have the same frustrations. Why do I have to use Single when I already know First? They have different ways of doing things. The real shame is that Microsoft implemented all of these as extension methods and you can SEE them all when they are not valid in that context. That pollutes the namespaces and leads to a lot of confusion. Of course the solution is that they will drop the LINQ to SQL stuff in Dot Net 4 so you will only see EF stuff.
Why don't you switch to NHibernate ?
@Jason
I completely agree, but at least when going from EF to L2S people have a choice. Microsoft's technology have always been plagued by ambiguity and with EF and L2S it only enforces that. With .NET 4.0 they L2S team is now in the EF team so hopefully they can rub off some of their expertise onto the EF team.
@Matthieu
I've tried NHibernate and it just felt like a burden to use, maybe I'm spoiled by EF and L2S. If the next version (v2) of EF doesn't work out I'll be using either Mindscape's LightSpeed or NHibernate. Thanks for the tip though!
The methods not supported by the LINQ to Entities provider are well documented. I suggest you check out the following links:
http://msdn.microsoft.com/en-us/library/bb738550.aspx
http://msdn.microsoft.com/en-us/library/bb896317.aspx
As with any technology there is some trade off between flexibility and ease of use. We have had some pretty big battles with EF, but are doing things that would simply not be possible in LINQ to SQL. The learning curve on EF is pretty steep, but it seems to be well worth the investment. Especially with the features being added to V2.
@JB Oh I agree. I think EF has some features I wish LINQ to SQL had. However I'm not going to drop LINQ to SQL like many other public-facing projects (eg StackOverflow.com) when EF is missing some basic features. Having fancy features don't mitigate not having some pretty basic ones.
way off topic ... what do you use for your blog engine? and what theme is this?
I'm using www.squarespace.com and I'm using the Newsroom -> Simple theme that I have modified to my taste.
I had the same problems. The changing the Single to First didn't hurt that much.
There is a fix for contains also. i found this
http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/
but I used this
http://jmcfetridge.blogspot.com/2009/02/contains-in-linq-to-entitys.html
Lightspeed looks good as well.
I gotta reiterate, why not use NHibernate? ...or LLBLGen, or .netTiers, or... the list goes on of superior ORM frameworks.
I'm probably a year or three away from being forced to use EF. Right now, I use one of the frameworks I've just stated. Vastly better than EF at this point.
Learn to generate ADO.NET code the smart way and map it to objects .. visually! Yes, It can be done and has zero learning curve of any new technology or query language, and is clean and efficient.
Read more here:
Learn how to Generate pure ADO.NET the Smart Way
I also am struglling to make the switch from LINQ to SQL into LINQ to Entities, and I found a good solution for the issue of .Single not being supported by L2E. I'm sure you've moved on since you've posted this blog entry, but I'll reply for future searchers.
Instead of using: Data.Single(z => z.Name == "Test");
Use: Data.FirstOrDefault(z => z.Name == "Test");
FirstOrDefault does not throw an error but returns null if the condition is not satisfied, this is perfect for my uses.
I agree that Linq to Sql is stunning when you are using it to retrieve data and do things in the same data context.
but it really get you stuck when you are caching data and doing update in different data context.
EF v4 is still in beta and I think it is not wise to use it in production.
So, I think if developers are choosing ORM for production, maybe both of them are off the list.
After a few weeks on Linq, I think I am going to switch back to ADO.NET or maybe give NHibernate a try.