LINQ：使用INNER JOIN、Group和SUM

Question

我正在尝试使用LINQ执行以下SQL，最接近的方法是进行交叉连接和求和计算。我知道有更好的编写方式，因此我向栈团队寻求帮助。

SELECT T1.Column1, T1.Column2, SUM(T3.Column1) AS Amount
FROM T1
     INNER JOIN T2
        ON T1.T1ID = T2.T1ID
     INNER JOIN T3
        ON T2.T3ID = T3.T3ID
GROUP BY T1.Column1, T1.Column2

我尝试的是以下的LINQ代码。

var qTotal = from T2 in context.T2
             from T3 in context.T3
             where T3.T3ID == T3.T3ID
             group T3 by T2.T1ID into gT2T3
                  from T1 in context.T1
                  where gT2T3.Key.Equals(T1.T1ID)
                  select new { T1.Column1,T1.Column2,Amount = gT2T3.Sum(t => t.Column1)};

我知道有更好的方法来编写它，只是不知道如何做到，任何帮助都将是极好的！

- Nic

Answer 1

试试这个：

var total = from T1 in context.T1
            join T2 in context.T2 on T1.T2ID equals T2.T2ID
            join T3 in context.T3 on T2.T3ID equals T3.T3ID
            group T3 by new { T1.Column1, T1.Column2 } into g
            select new { 
                Column1 = T1.Column1, 
                Column2 = T2.Column2, 
                Amount = g.Sum(t3 => t3.Column1) 
            };

- Nick Berardi

---

谢谢 Nick，一开始我觉得答案是错误的，但是在重新阅读和再次执行后，我意识到我的初始尝试中有一个错误，而你的答案是准确的。 谢谢！ - Nic

针对更新的.NET版本（4.0及以上），请查看下面twnaing的答案。 - Oskar Lundgren

Answer 2

对我而言（使用4.0），以下内容有效。

var total = from T1 in context.T1
            join T2 in context.T2 on T1.T2ID equals T2.T2ID
            join T3 in context.T3 on T2.T3ID equals T3.T3ID
            group T3 by new { T1.Column1, T1.Column2 } into g
            select new { 
                Column1 = g.Key.Column1, 
                Column2 = g.Key.Column2, 
                Amount = g.Sum(t3 => t3.Column1) 
            };

- Tun

Answer 3

以下代码适用于我：

                          var credit = (from bm in BulkMessage
                          join sms in SMS on bm.BulkMessageId equals sms.BulkMessageId
                          where bm.ProfileId == pid && bm.IsActive == true
                           group sms by sms.SMSCredit into g

                          select new { SMSCredits = g.Sum(s => s.SMSCredit) }).FirstOrDefault();

- Muhammad Awais