使用group / sum / having对LINQ进行SQL查询(SQL query to LINQ using group / sum / having)

 我一直在尝试将以下查询从SQL转换为LINQ，我的查询的主要目的是通过同一个Sender查找两个或更多个事务，其中金额的总和> = 2000 
 
SELECT t.TXN_SENDER_ID,
       s.SEN_FIRSTNAME,
       s.SEN_LASTNAME,
       SUM(t.TXN_AMOUNT)
FROM   TRANSACTIONS t
INNER JOIN SENDERS s ON s.SEN_ID = t.TXN_SENDER_ID
WHERE  t.TXN_DATE BETWEEN Dateadd(YEAR, -3, Getdate()) AND Getdate()
GROUP  BY t.SENDER_ID, s.SENDER_NAME, s.SENDER_LASTNAME
HAVING SUM(t.TXN_AMOUNT) > 2000
 
 这是我尝试过的： 
 
var query = from t in context.TRANSACTIONS
            join s in context.SENDERS on t.TXN_SENDER_ID equals s.SEN_ID
            group new { t, s } by new { t.TXN_SENDER_ID, s.SEN_FIRSTNAME, s.SEN_LASTNAME, t.TXN_AMOUNT } into gr
            where (gr.Sum(x => x.t.TXN_AMOUNT) > 1000)
            select new { gr.Key.TXN_AMOUNT, gr.Key.SEN_FIRSTNAME, gr.Key.SEN_LASTNAME };
 
 提前致谢 

I've been trying to translate following query from SQL to LINQ, main purpose of my query is to find two or more transactions by the same Sender in which the amount's sum is >= 2000 
 
SELECT t.TXN_SENDER_ID,
       s.SEN_FIRSTNAME,
       s.SEN_LASTNAME,
       SUM(t.TXN_AMOUNT)
FROM   TRANSACTIONS t
INNER JOIN SENDERS s ON s.SEN_ID = t.TXN_SENDER_ID
WHERE  t.TXN_DATE BETWEEN Dateadd(YEAR, -3, Getdate()) AND Getdate()
GROUP  BY t.SENDER_ID, s.SENDER_NAME, s.SENDER_LASTNAME
HAVING SUM(t.TXN_AMOUNT) > 2000
 
Here's what I've tried:
 
var query = from t in context.TRANSACTIONS
            join s in context.SENDERS on t.TXN_SENDER_ID equals s.SEN_ID
            group new { t, s } by new { t.TXN_SENDER_ID, s.SEN_FIRSTNAME, s.SEN_LASTNAME, t.TXN_AMOUNT } into gr
            where (gr.Sum(x => x.t.TXN_AMOUNT) > 1000)
            select new { gr.Key.TXN_AMOUNT, gr.Key.SEN_FIRSTNAME, gr.Key.SEN_LASTNAME };
 
Thanks in advance

原文：https://stackoverflow.com/questions/29754327