Entity Framework + MySQL - Why is the performance so terrible?

Question

When I decided to use an OR/M (Entity Framework for MySQL this time) for my new project I was hoping it would save me time, but I seem to have failed it (for the second time now).

Take this simple SQL Query

SELECT * FROM POST ORDER BY addedOn DESC LIMIT 0, 50 

It executes and gives me results in less than a second as it should (the table has about 60,000 rows).

Here's the equivalent LINQ To Entities query that I wrote for this

var q = (from p in db.post
            orderby p.addedOn descending
             select p).Take(50);

    var q1 = q.ToList(); //This is where the query is fetched and timed out

But this query never even executes it times out ALWAYS (without orderby it takes 5 seconds to run)! My timeout is set to 12 seconds so you can imagine it is taking much more than that.

• Why is this happening?
• Is there a way I can see what is the actual SQL Query that Entity Framework is sending to the db?
• Should I give up on EF+MySQL and move to standard SQL before I lose all eternity trying to make it work?

I've recalibrated my indexes, tried eager loading (which actually makes it fail even without the orderby clause)

Please help, I am about to give up OR/M for MySQL as a lost cause.

Answer 1

All my research finally culminated into the conclusion that while EF in general is bad for performance, MySql+EF is downright shoddy. SO's choice of L2S over EF is a good move and if I had access to a MS Sql database instead of MySQL I would have moved in that direction too.

Unfortunately I am stuck with MySql cause it's free and that has forced me to give up EF. I am now back to hard-coding my SQL queries the old, tested, efficient way and the results are good.

I gave MYSql + EF a pass, but I would love to hear from people who have successfully used it in a non-trivial project.