CODEWITHSUNDEEP
c#linqsql-server-2008
georgieboy
georgieboy

Reputation: 155

How to convert SQL into Linq

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;

Upvotes: 0

Views: 70

Answers (1)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

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));

Upvotes: 1

Related Questions