Hello,

I'm having a serious issue with MySQL and Entity Framework 4.0. I have dropped a Table onto the EF Designer surface, and everything seems OK. However, when I perform a query in the following fashion:

using(entityContext dc = new entityContext()) {
  int numRows = dc.myTable.Count();
}

The query that is generated 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 should be evident, this is an excruciatingly unoptimized query. It is selecting every single row! This is not optimal, nor is it even possible for me to use MySQL + EF at this point.

I have tried both the MySQL 6.3.1 [that was fun to install] and DevArt's dotConnect for MySQL and both produce the same results. This table has 1.5 million records.. and takes 6-11s to execute!

What am I doing wrong ? Is there any way to optimize this [and other queries] to produce sane code like:

SELECT COUNT(*) FROM table

?

Generating the same query using SQLServer takes virtually no time and produces sane code.

Help!

Thanks!

• Matthew