i'm using EF 4.4.20627.0 with MySQL 5.6, MySQL .net connector version 6.6.4
i have a method like this, which generated sql is very very slow(needs more than 1 min)
private List<TNews> GetPagedNews(int pagenum, int pagesize,
AdvSearcherArgs advcArgs, string keyword)
{
var dataSrc = _dbRawDataContext.TNews.Where(x => x.Id>0);
if (!string.IsNullOrWhiteSpace(advcArgs.PMAC))
{
dataSrc = dataSrc.Where(m => m.Pmac == advcArgs.PMAC);
}
if (!string.IsNullOrWhiteSpace(advcArgs.BegineDate))
{
var begin = Convertion.ToDate(advcArgs.BegineDate);
var end = Convertion.ToDate(advcArgs.EndDate);
dataSrc = dataSrc.Where(m => m.PmacDT >=begin && m.PmacDT<end);
}
dataSrc = dataSrc.OrderByDescending(n => n.PmacDT).Skip(pagenum * pagesize).
Take(pagesize);
var cnt = dataSrc.Count();
SetPagerValues(pagenum, pagesize, cnt);
return dataSrc.ToList();
}
the generaed sql like this:
SELECT
`Project1`.*
FROM
(
SELECT
`Extent1`.*
FROM `tnews` AS `Extent1`
WHERE (`Extent1`.`Id` > 0)
AND ((`Extent1`.`PmacDT` >= '2013-01-01 00:00:00 ') AND
(`Extent1`.`PmacDT` < '2013-01-07 00:00:00 '))
) AS `Project1`
ORDER BY
`Project1`.`PmacDT` DESC LIMIT 0,20
/* Affected rows: 0 Found rows: 20 Warnings: 0 Duration for 1 query: 00:01:30 */
if i move order by and limit clause into the brace, this sql will be very fast(costs less than 1 sec):
SELECT
`Project1`.*
FROM
(
SELECT
`Extent1`.*
FROM `tnews` AS `Extent1`
WHERE (`Extent1`.`Id` > 0)
AND ((`Extent1`.`PmacDT` >= '2013-01-01 00:00:00 ') AND
(`Extent1`.`PmacDT` < '2013-01-07 00:00:00 '))
ORDER BY
`PmacDT` DESC LIMIT 0,20
) AS `Project1`
/* Affected rows: 0 Found rows: 20 Warnings: 0 Duration for 1 query: 0.000 sec. */
what does ProjectX and Extent1 mean?
and why entity-framework don't put the orderby * limit x,y outside the real query??
the sql is weird and definitely make the query very slow, i will never write sql like that...So how to make EF generate a CORRECT sql??
any suggestions?
4.4.20627.0, .net connector version6.6.4, i think it is the way the EF grnerate SQL cause this problem, i don't think restart can change any state about this. so i think the point is the way we use LINQ..NET Connector) If you are using MySQL it might be a good idea to upgrade if you want to use entity framework properly :)limitclause(MSSQL doesn't have this);