Hacker News new | past | comments | ask | show | jobs | submit login

Entity Framework still generates pretty crappy looking SQL. It's nice to get a project going, but every time I try to look at why we are having performance issues in production and see the horrible query EF is generating I cry.



While I agree the SQL it produces looks ugly, performance wise I haven't found too many issues (using EF 5 then 6). The few cases where it produced legitimately bad SQL (slow), was due to the developer failing to grasp how queries are built (in particular abusing "in" and having multiple layers of selects instead of just a flat query which could have done the same thing).

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.


No-one ever migrates their db unless they really are in some sort of serious shit. I'll also guess you've never done it because the EF + MySQL = a world of pain. 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. 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.

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.


> No-one ever migrates their db unless they really are in some sort of serious shit.

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.


Indeed for web development tools like MiniProfiler give you access to the queries in the page along with execution time and duplicate query detection. We use it on Oracle with the Devart provider and it's pretty snappy even with the somewhat bonkers looking queries it sometimes generates.

Because in my office the alternative is PL/SQL functions that dynamically build WHERE clauses, and I'll choose EF over that any day.


Given a best-case MySQL database that needs to be migrated elsewhere for data quality or performance reasons, it could be a very straightforward task that any qualified DBA could manage. That isn't the most common case, but it's one you can achieve by intention.


Don't try to read the generated SQL. Debug the query plan directly.


Yes, but using projections and generally avoiding descending deep down object graphs for queries of multiple objects generally yields acceptable performance.




Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: