使用分組和聚合函式的簡單 sql 到 Linq 查詢

我正在與 linq 戰鬥，試圖學習語法，但我不知道如何執行以下簡單查詢

SELECT DISTINCT
   user.firstname,
   user.lastname,
   COUNT(invoice.amount),
   SUM(invoice.amount)
FROM
   company_user
   INNER JOIN 
       user
   ON 
       company_user.user_id = user.user_id
   INNER JOIN 
       invoice
   ON
       invoice.user_id= invoice.user_id
WHERE 
   company_user.company_id = 1
GROUP BY
   user.firstname,
   user.lastname,
GO

任何將其轉換為 linq 的幫助都會很棒。

您所追求的查詢應該與此非常接近：

var query =
   from cu in company_user
   where cu.company_id == 1
   join u in user on cu.user_id equals u.user_id
   join i in invoice on u.user_id equals i.user_id
   group i.amount by new
   {
       u.firstname,
       u.lastname,
   } into gs
   select new
   {
       firstname = gs.Key.firstname,
       lastname = gs.Key.lastname,
       count = gs.Count(),
       sum = gs.Sum(),
   };

享受！

由於 op 提到了學習語法，這裡有一個流利的版本：

company_user
   .Where(x => x.company_id == 1)
   .Join(
       user,
       x => x.user_id,
       x => x.user_id,
       (o,i) => new { 
           FirstName = i.firstName, 
           LastName = i.lastName,
           InvoiceCount = invoice.Count(y => y.user_id == o.user_id),
           InvoiceSum = invoice.Where(y => y.user_id == o.user_id).Sum(y => y.amount)
       }
   ).GroupBy(x => new { x.FirstName, x.LastName })
   .Distinct()

引用自：https://stackoverflow.com/questions/3955986