求一个linq语句怎么写

[likedefly]

原表：
login Goods Price Qty Bos Date

meimei 铜 2.12 2 B 2012-12-03
meimei 铜 2.12 5 S 2012-12-03
meimei 铜 2.14 2 S 2012-12-03
meimei 铝 4.63 2 B 2012-12-03
meimei 铝 4.62 2 B 2012-12-03
gege 金 432 6 S 2012-12-03

结果：

login Goods TotalPrice TotalQty BQty SQty Date

meimei 铜 6.38 9 2 7 2012-12-03
meimei 铝 9.25 4 4 0 2012-12-03
gege 金 432 6 0 6 2012-12-03

求高效的linq语句，请大虾帮忙

[机器人]

就是按照日期 Goods, Date GroupBy 取 Sum 。。。

[likedefly]

login Goods Price date group by取sum，同时对Qty根据Bos（login Goods Price date这几个字段依然有效）求和。

[骑着蜗牛去爬山]

var query = from t in dc.Class1 group t by new { t.login, t.Goods } into g select new { login = g.Key.login, Goods = g.Key.Goods, TotalPrice = g.Sum(x => x.Price), TotalQty = g.Sum(x => x.Qty), BQty = g.Sum(x => x.Qty.ToString().Trim() == "B" ? 1 : 0), SQty = g.Sum(x => x.Qty.ToString().Trim() == "S" ? 1 : 0), Date = g.Max(x => x.Date) };

[你的选择B]

手写，仅供参考

            using (NorthwindEntities entity = new NorthwindEntities())
            {
                var result = from u in entity.table
                             group u by new { u.login, u.Goods, u.Date } into temp
                             select new
                             {
                                 login = temp.Key.login,
                                 Goods = temp.Key.Goods,
                                 TotalPrice = temp.Sum(c => c.Price),
                                 TotalQty = temp.Sum(c => c.Qty),
                                 BQty = temp.GroupBy(c => c.Bos).Where(c => c.Key == "B").Count(),
                                 SQty = temp.GroupBy(c => c.Bos).Where(c => c.Key == "S").Count(),
                                 Date = temp.FirstOrDefault().Date
                             };
            }

[你的选择B]

或者内部没必要再进行一次分组 直接查出count即可 修改如下：

                var result = from u in entity.table
                             group u by new { u.login, u.Goods, u.Date } into temp
                             select new
                             {
                                 login = temp.Key.login,
                                 Goods = temp.Key.Goods,
                                 TotalPrice = temp.Sum(c => c.Price),
                                 TotalQty = temp.Sum(c => c.Qty),
                                 BQty = temp.Where(c => c.Key == "B").Count(),
                                 SQty = temp.Where(c => c.Key == "S").Count(),
                                 Date = temp.FirstOrDefault().Date
                             };

[你的选择B]

失误，用错了函数： 1：如下

                var result = from u in entity.table
                             group u by new { u.login, u.Goods, u.Date } into temp
                             select new
                             {
                                 login = temp.Key.login,
                                 Goods = temp.Key.Goods,
                                 TotalPrice = temp.Sum(c => c.Price),
                                 TotalQty = temp.Sum(c => c.Qty),
                                 BQty = temp.Where(c => c.Key == "B").Sum(c => c.Qty),
                                 SQty = temp.Where(c => c.Key == "S").Sum(c => c.Qty),
                                 Date = temp.FirstOrDefault().Date
                             };

2：如下

                using (NorthwindEntities entity = new NorthwindEntities())
                {
                    var result = from u in entity.table
                                 group u by new { u.login, u.Goods, u.Date } into temp
                                 select new
                                 {
                                     login = temp.Key.login,
                                     Goods = temp.Key.Goods,
                                     TotalPrice = temp.Sum(c => c.Price),
                                     TotalQty = temp.Sum(c => c.Qty),
                                     BQty = temp.GroupBy(c => c.Bos).Where(c => c.Key == "B").Sum(c => c.Qty),
                                     SQty = temp.GroupBy(c => c.Bos).Where(c => c.Key == "S").Sum(c => c.Qty),
                                     Date = temp.FirstOrDefault().Date
                                 };
                }

[threenewbee]

http://bbs.csdn.net/topics/370024120