linq语句如何写

求一个linq语句怎么写
原表：
loginGoodsPriceQtyBosDate

meimei铜2.122B2012-12-03
meimei铜2.125S2012-12-03
meimei铜2.142S2012-12-03
meimei铝4.632B2012-12-03
meimei铝4.622B2012-12-03
gege金4326S2012-12-03

结果：

loginGoodsTotalPriceTotalQtyBQtySQtyDate

meimei铜6.389272012-12-03
meimei铝9.254402012-12-03
gege金4326062012-12-03

求高效的linq语句，请大虾帮忙
[最优解释]
失误，用错了函数：
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
                                 };
                }

[其他解释]
就是按照日期 Goods, Date GroupBy 取 Sum 。。。
[其他解释]
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)
                        };
[其他解释]
手写，仅供参考

            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
                             };
            }

[其他解释]
或者内部没必要再进行一次分组
直接查出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
                             };

[其他解释]
http://bbs.csdn.net/topics/370024120

查看更多 下一篇