需要将此查询转换为linq或lambda

Question

这个查询回拉两列一个userid列和一个他们当前月的总销售额列。

我需要添加一个列来对它们进行排序(它将与行#完全相同)

我需要将它从SQL转换为LINQ或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

目前回报:

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

Answer 1

要使用SQL捕获排名索引，您可以简单地修改当前SQL，如下所示:

     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

将产生

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

不确定要在哪个上下文中"转换为LINQ"。如果您的意思是在EF或linq -to- sql上下文中对这两个表进行linq查询，那么这些语句将产生与上面相同的结果:

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

生成一个对象列表:

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