Deploying RANK OVER SQL in C # LINQ

Question

I need to implement the following T-SQL statement ....

RANK() OVER (PARTITION BY a.CategoryKey ORDER BY (x.Rate * @BASE_RATE ) DESC )as Rank 

... in C # LINQ. So far I have come up with something like ....

 var rank = data.GroupBy(d => d.CategoryKey) .Select(group => group.OrderByDescending(g => g.Rate * @BAES_RATE) 

I think this will give me every rank section sorted by * BASE_RATE speed. But I really need a separate rank for one row, and this is a subquery in a larger result. So the really complete SQL query I'm working from is something like ....

 SELECT a.Rate, a.CategoryKey, a.ID, . . . RANK() OVER (PARTITION BY a.CategoryKey ORDER BY (x.Rate * @BASE_RATE ) DESC )as Rank FROM data 

Answer 1

Unless you need exact Rank semantics (i.e. related series). You can use an index with selected projections.

 var rank = data.GroupBy(d => d.CategoryKey) .SelectMany(g => g.OrderByDescending(y => y.Rate * @BAES_RATE) .Select((x,i) => new{g.Key, Item=x, Rank=i+1})) 

Otherwise, you can see this answer