-4

According to this post Why does MYSQL higher LIMIT offset slow the query down? and this article http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ I need a linq to create below query

SELECT  news.*
FROM    (
        SELECT  id
        FROM    news
        WHERE   cat_id= x
        ORDER BY
                id DESC
        LIMIT m, n
        ) o
JOIN    news
ON      news.id = o.id
Community
  • 1
  • 1
Ghooti Farangi
  • 19,926
  • 15
  • 46
  • 61

1 Answers1

0

This should do it. (Skip/Take equals the Limit)

from u in news
join n in
(
    from x in news
    where x.cat_id = 10
    orderby x.Id descending
    select x
).Skip(10).Take(20) on u.Id equals n.Id
select u
Magnus
  • 45,362
  • 8
  • 80
  • 118
  • This linq does not genereate my query. also the result of it is not same as my query – Ghooti Farangi Sep 25 '12 at 10:04
  • Can only test against SQL server 2008 and it generates the correct code. So what does it generate with your provider? – Magnus Sep 25 '12 at 12:39
  • The below query generate in MySQL: SELECT `Extent1`.`Id`, ... FROM `news` AS `Extent1` INNER JOIN (SELECT `Extent2`.`Id`, ... FROM `news` AS `Extent2` ORDER BY `Extent2`.`Id` DESC LIMIT 10,20) AS `Limit1` ON `Extent1`.`Id` = `Limit1`.`Id` – Ghooti Farangi Sep 25 '12 at 14:36
  • How is that different from what you wanted? The `cat_id`? Just add it to the inner query – Magnus Sep 25 '12 at 15:37
  • @GhootiFarangi please be more specific. – Magnus Sep 27 '12 at 13:08