1) All my code lives in Visual Studio with compile-time type-checking. No maintaining stored procedures outside of my main codebase, no mucking about with strings. And because Entity Framework puts the Select after the From clause, I even get good intellisense on column-names.
2) I can quickly shift code into being in-memory queries from database queries.
3) While it's a little muddy mixing SQL and C#, there are places where C#/EF's syntax is so much more brief and clean since it's hyper-aware of relationships. For example, instead of writing 2 joins to get the country of a user, I can say User.Centre.Country.
Of course, the Entity Framework is adding a layer of complexity to my work - you still have to know what's going on in SQL, and that mental overhead is a downside. But it pays off.
SQL is a brilliant language, and that's why it's stood the test of time in spite of its advanced age.
But seriously, it has a lot of bad flaws - it's often difficult to make reusable components in SQL like you can in other platforms.
The underlying relational algebra is brilliant. That's why I like ORMs - they admit that SQL is ideologically beautiful, but the SQL language itself could use some loving.
I will say EF requires extra training/knowledge. So it isn't a "free" framework. However being able to migrate your entire code base from one database to another only changing a single annotation in the Code First classes is pretty nice. As is being able to generate your databases via the initialisers.
Is it for every project? No. However for "classical" database projects where you're going to be writing a bunch of CRUD and shoving it into C# classes, it is very helpful.
I'm also guessing John's problem with that query is that it's a simple aggregate query in SQL, there's no good reason to use the ORM, mainly because you have no idea what convoluted SQL it's going to spit out. There's only downsides with no upsides.
I've been involved in several migrations. All of which were planned over years in some cases. There was no "serious shit" at any point in the process.
One popular reason for migrations is licensing costs (e.g. escaping Oracle or IBM), or due to mergers where the other company had a different database system and they wanted to consolidate both technology but also expertise.
> I'll also guess you've never done it because the EF + MySQL = a world of pain.
I've never done what? I've never done EF with MySQL. I never claimed I did however.
I've used EF with other database systems including Oracle 11g, MS SQL (several), and other integrated it with some smaller database systems that had EF providers available.
> MySQL does not like nested queries, the EF uses them like they're crack, one of the reasons the SQL it produces is so hard to read.
You, the developer, formulate the queries. If you're seeing excess nesting then examine how you're doing things.
> I'd guess that the EF + [any db that's not MS SQL] probably suffers from similar "holy shit why did the DB just die, oh it's the EF" problem.
That's not been my experience. While I have found some other EF providers "limited" compared to Microsoft's provider, most of the problems encountered were due to underlying bad configuration or bad entity layout rather than EF itself. I will say Code First is significantly better than EDMX-style modelling. In particular in larger projects (the Visual Studio designer kind of sucks).
> mainly because you have no idea what convoluted SQL it's going to spit out.
It is very easy to see exactly what EF spits out. You can even pipe that output all over the place (e.g. error system, performance logs, etc). There's no excuse for being ignorant of what EF is doing under the hood anymore.
Because in my office the alternative is PL/SQL functions that dynamically build WHERE clauses, and I'll choose EF over that any day.
I think that you're confusing LINQ with the ORM.
LINQ is a language feature, Language INtegrated Query. It operates on IEnumerable<T>. They introduced a new interface, IQueryable<T> which extends IEnumerable<T> with Expression. Basically it exposes your "query" as an Expression Tree - this can then be used by QueryProviders to do cool stuff like generate SQL.
A number of ORMs implement LINQ QueryProviders; Entity Framework, NHibernate, BLToolkit are all good examples.
EntityFramework, like a lot of fully-featured ORMs suffers from all of the traditional ORM problems: runtime performance is sub-optimal, it's query heavy and if you use the visual designer it encourages bad development practices.
On the other hand it allows people with very little knowledge to be productive; for small-scale projects the bang-for-buck is hard to compete against.