[c#]转换 Sql 查询到 linq 总和用分组依据

标签: Linq SQLServer C#
发布时间: 2017/2/28 22:43:29
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我新的 linq 可以任何机构请出去帮我转换后对 linq 查询。

   SELECT A.Name,
   Sum(C.MoneyIN) - Sum(C.MoneyOut) AS Balance
   FROM   Customers A
   JOIN   Banks B ON A.Id = B.id
   JOIN   BankTransactions C ON B.Id = C.BankID
   GROUP  BY A.Name

我试着将它转换成 linq

var query =  from c in db.Customers
                     join b in db.Banks on c.Id equals b.CustomerId
                     join bt in db.BankTransactions on b.Id equals bt.BankId                         
                     group c by c.Name into Cust                         
                     select new
                     {
                         Name = c.Name,
                         Balance = Cust.Sum(bt.MoneyIn) - Cust.Sum(bt.MoneyOut) 
                     };

谁能告诉我在哪里我错了吗?

在此先感谢。

解决方法 1:

试试︰

var query =  from c in db.Customers
                     join b in db.Banks on c.Id equals b.CustomerId
                     join bt in db.BankTransactions on b.Id equals bt.BankId                         
                     group bt by c.Name into Cust                         
                     select new
                     {
                         Name = Cust.Name,
                         Balance = Cust.Sum(x => x.MoneyIn) - Cust.Sum(x => x.MoneyOut) 
                     };

看来问题是在最后一个选择你用 c.Name 但应 Cust.Name 作为你已经执行分组。

更新 1。

Cust.Sum(bt.MoneyIn) => Cust.Sum(x => x.MoneyIn)

更新 2。

此外你按错了道具进行分组。如果你想要适用于你需要组由它喜欢你 BankTransactions 的总和︰

bt的 c.Name 分组 Cust

所以工作示例可能看起来像︰

public class Customer
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

        public class Bank
        {
            public int CustomerId { get; set; }
            public int Id { get; set; }
        }

        public class BankTransaction
        {
            public int BankId { get; set; }
            public int MoneyIn { get; set; }
            public int MoneyOut { get; set; }
        }

和编译的查询︰

var customers = new List<Customer>();
            var banks = new List<Bank>();
            var bankTransactions = new List<BankTransaction>();

            var result = from c in customers
                join b in banks on c.Id equals b.CustomerId
                join bt in bankTransactions on b.Id equals bt.BankId                         
                group bt by c.Name into Cust
                select new { Name = Cust.Key, Balance = Cust.Sum(x => x.MoneyIn) - Cust.Sum(x => x.MoneyIn) };
赞助商