Need to convert this query to linq or lambda

Question

This query pulls back two columns a userid column and a column of their total sales for the current month.

I need to add a column that ranks them (it would be the exact same as the row #)

And I need to convert this from SQL to LINQ or Lambda.

select pu.userID, SUM(o.OrderTotal) as OrderTotal from ProPit_User pu
inner join SeperateDB.dbo.orders o on pu.salesrepid = o.salesrepid and o.DateCompleted > '2014-05-01' and o.DateCompleted < '2014-05-23'
group by pu.userID
order by SUM(o.OrderTotal) desc

Currently returns:

userID  OrderTotal
340 68992.74
318 49575.05
228 42470.88
278 38196.87
291 36220.52
351 34962.32
422 31764.67
178 31433.41
430 30836.77
212 30375.99

Row is different than Rank, and you should read this article for what you need. http://msdn.microsoft.com/en-us/library/ms189798.aspx As for conversion, not my strong suit. — Jaaz Cole, May 22 '14 at 23:06
With the way they are ordered Row would be equal to rank. IE the first row would be rank 1. etc. I'll take a look at the article as well. — James Wilson, May 22 '14 at 23:49
You have asked the same question (the first part, at least) [here](http://stackoverflow.com/questions/23812621/how-to-grab-the-index-from-a-list-using-linq/23812668#23812668). — Simon Belanger, May 23 '14 at 00:03
Same end result, different way to get there. The question you linked was through trying to get the index of a list. This is being requested through SQL and then being converted into LINQ/Lambda. @SimonBelanger — James Wilson, May 23 '14 at 00:49
@JaazCole also the likely hood two people will have the exact same sales would be too low to worry about it. — James Wilson, May 23 '14 at 01:03

score 1 · Accepted Answer · answered May 23 '14 at 06:11

To capture the ranking index using SQL, you can simply modify your current sql as such:

     SELECT pu.userid, 
            SUM(o.OrderTotal) AS OrderTotal, 
            ROW_NUMBER() OVER (ORDER BY SUM(o.ordertotal) DESC) AS [Rank]
       FROM ProPit_User pu
 INNER JOIN SeperateDB.dbo.Orders o ON pu.salesrepid = o.salesrepid 
        AND o.DateCompleted > '2014-05-01' AND o.DateCompleted < '2014-05-23'
   GROUP BY pu.userid
   ORDER BY SUM(o.OrderTotal) DESC

which will yield

userID  OrderTotal Rank
340     68992.74   1
318     49575.05   2
228     42470.88   3
etc

Not sure in which context you want to 'convert to LINQ'. If you mean a linq query against these two tables within an EF or Linq-to-Sql context, then these statements will yield the same results as above:

var minDate = new DateTime(2014,5,1);
var maxDate = new DateTime(2014,5,23);

-- the linq to sql query:
-- join on salesrepid, group by userid, sum the ordertotals
var dbQuery = ProPit_Users.GroupJoin(
 Orders,
 pu => pu.salesrepid,
 o => o.salesrepid,
 (pu, orders) => new 
    {
      pu.UserId, 
      OrderTotal = orders.Where(o => o.datecompleted > minDate && o.datecompleted. < maxDate )
                         .Sum(o => o.ordertotal)
    }
 )
 .OrderByDescending(row => row.OrderTotal)
 -- materialize the db query
 .ToList();

 -- add ranking to the results of the query
 var userRankings = dbQuery.Select((row, idx) => new { Rank = ++idx, row.UserId, row.OrderTotal });

which will yield a list of objects:

Rank UserID  OrderTotal
1    340     68992.74  
2    318     49575.05  
3    228     42470.88  
etc

I will try this first thing tomorrow. Too sleepy now, but thank you for posting it. I look forward to trying it out! :) — James Wilson, May 23 '14 at 07:19
I can't get this to work. It won't let me use two separate contexts. Each table I need to join is in a separate database. Any idea how to resolve that? — James Wilson, May 27 '14 at 16:11
Ah. The linq I gave you emits sql to join two tables, so that won't work across servers. If you pull both underlying sets of data into two lists, perhaps filtering by the data range first, the query itself will still work as a simple linq to objects statement. — mdisibio, May 27 '14 at 19:27
I put the Orders table into it's own object and then joined them and its working like a charm now. Thanks. — James Wilson, Jun 05 '14 at 17:12

Need to convert this query to linq or lambda

1 Answers1