I have a serious problem with MySQL and Entity Framework 4.0. I dropped the table to the surface of EF Designer and everything seems OK. However, when I execute the request as follows:
using(entityContext dc = new entityContext()) { int numRows = dc.myTable.Count(); }
The generated query looks something like this:
SELECT `GroupBy1`.`A1` AS `C1` FROM (SELECT Count(1) AS `A1` FROM (SELECT `pricing table`.`a`, `pricing table`.`b`, `pricing table`.`c`, `pricing table`.`d`, `pricing table`.`e`, `pricing table`.`f`, `pricing table`.`g`, `pricing table`.`h`, `pricing table`.`i` FROM `pricing table` AS `pricing table`) AS `Extent1`) AS `GroupBy1`
As you can see, this is a painfully unoptimized query. He selects every row! This is not optimal, and I can't even use MySQL + EF at this point.
I tried both MySQL 6.3.1 [it was interesting to install] and DevArt dotConnect for MySQL, and both give the same results. This table has 1.5 million records .. and takes 6-11 seconds to complete!
What am I doing wrong? Is there a way to optimize this [and other queries] to create the correct code, for example:
SELECT COUNT(*) FROM table
?
Creating the same query using SQLServer takes virtually no time and produces reasonable code.
Help!
Edit: I would also like to point out that I switched to the DevArt dotConnect MySQL LINQ to SQL driver, and using L2S over EF is 1000000x faster. This also includes queries.
Choosing anything in EF seems to generate completely bonkers requests.
dc.pricing_table.OrderBy(j => ja).Skip(100).Take(100).ToList(); SELECT `Extent1`.`a`, `Extent1`.`b`, `Extent1`.`c`, `Extent1`.`d`, `Extent1`.`e`, `Extent1`.`f`, `Extent1`.`g`, `Extent1`.`h`, `Extent1`.`i` FROM (SELECT `pricing table `.`a`, `pricing table `.`b`, `pricing table `.`c`, `pricing table `.`d`, `pricing table `.`e`, `pricing table `.`f`, `pricing table `.`g`, `pricing table `.`h`, `pricing table `.`i` FROM `pricing table ` AS `pricing table`) AS `Extent1` ORDER BY `a` ASC LIMIT 100,100
Again, a completely basaped invalid request. LIMIT 100 100 is in a completely wrong place. This, of course, will not work for me at all.