0

I am trying to get this sql statement into Linq format with no success. Instead of displaying all data, i want to display ten items from each category. Any help would be great. 

select *
from (
     select *,
             row_number() over(partition by T.category order by T.id desc) as rn
 from clothes as T
 ) as T
where T.rn <= 10;

my attempt at linq is below;

var query= from (subquery) as DB in dataContext.table where DB.rn <=10;
               select DB;
var subquery = row_number() over(partition by DB.id order by DB.category desc) as rn from DB in dataContext.table as DB
                    select DB;
georgieboy
  • 155
  • 1
  • 10
  • possible duplicate of [Row\_number over (Partition by xxx) in Linq?](http://stackoverflow.com/questions/9980568/row-number-over-partition-by-xxx-in-linq) – Chris Jun 08 '15 at 12:25

1 Answers1

1

use GroupBy, Take and a SelectMany should do the trick

var result = dataContext.table.GroupBy(m => m.category)
             .SelectMany(g => g.OrderByDescending(x => x.Id).Take(10));
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122