自己练习的
switch (productDataAnalysisQuery.DataType) { case 1: var data = (from hp in GPEcontext.hbl_product join hcim in from hci in GPEcontext.hbl_change_info where hci.LotNum == 0 && hci.ChangeData < 0 && hci.CreatedTime >= beginDate && hci.CreatedTime <= endDate group hci by new { hci.ProductId } into G select new { ProductId = G.Key.ProductId, Amount = G.Sum(item => Math.Abs(item.ChangeData.Value)) } on hp.id equals hcim.ProductId orderby hcim.Amount descending select new ProductDataAnalysisodel { ProductName = hp.ProductName, ProductCode = hp.ProductCode, Amount = hcim.Amount }).Take(20); pageModel.Models = data.FindBy(item => true, productDataAnalysisQuery.PageNo, productDataAnalysisQuery.PageSize, out total, item => item.Amount, false); break; case 2: var data2 = (from hp in GPEcontext.hbl_product join hcim in from hci in GPEcontext.hbl_change_info where hci.LotNum == 0 && hci.ChangeData < 0 && hci.CreatedTime >= beginDate && hci.CreatedTime <= endDate group hci by new { hci.ProductId } into G select new { ProductId = G.Key.ProductId, Amount = G.Sum(item => Math.Abs(item.ChangeData.Value)) } on hp.id equals hcim.ProductId orderby hcim.Amount select new ProductDataAnalysisodel { ProductName = hp.ProductName, ProductCode = hp.ProductCode, Amount = hcim.Amount }).Take(20); pageModel.Models = data2.FindBy(item => true, productDataAnalysisQuery.PageNo, productDataAnalysisQuery.PageSize, out total, item => item.Amount, false); break; case 3: var data3 = (from hp in GPEcontext.hbl_product join hcim in from hci in GPEcontext.hbl_change_info where hci.LotNum == 0 && hci.ChangeData < 0 && hci.CreatedTime >= beginDate && hci.CreatedTime <= endDate group hci by new { hci.ProductId } into G select new { ProductId = G.Key.ProductId, Amount = G.Count() } on hp.id equals hcim.ProductId orderby hcim.Amount descending select new ProductDataAnalysisodel { ProductName = hp.ProductName, ProductCode = hp.ProductCode, Amount = hcim.Amount }).Take(20); pageModel.Models = data3.FindBy(item => true, productDataAnalysisQuery.PageNo, productDataAnalysisQuery.PageSize, out total, item => item.Amount, false); break; case 4: var data4 = (from hp in GPEcontext.hbl_product join hcim in from hci in GPEcontext.hbl_change_info where hci.LotNum == 0 && hci.ChangeData < 0 && hci.CreatedTime >= beginDate && hci.CreatedTime <= endDate group hci by new { hci.ProductId } into G select new { ProductId = G.Key.ProductId, Amount = G.Count() } on hp.id equals hcim.ProductId orderby hcim.Amount select new ProductDataAnalysisodel { ProductName = hp.ProductName, ProductCode = hp.ProductCode, Amount = hcim.Amount }).Take(20); pageModel.Models = data4.FindBy(item => true, productDataAnalysisQuery.PageNo, productDataAnalysisQuery.PageSize, out total, item => item.Amount, false); break; case 5: var data5 = (from hp in GPEcontext.hbl_product join hs in from hps2 in GPEcontext.hbl_product_stock join p in from hps in GPEcontext.hbl_product_stock group hps by new { hps.ProductID } into G select new { ProductId = G.Key.ProductID, CreatedTime = G.Max(p => p.CreatedTime) } on new { A = hps2.ProductID, B = hps2.CreatedTime } equals new { A = p.ProductId, B = p.CreatedTime } select new { ProductId = hps2.ProductID, Amount = hps2.Stock } on hp.id equals hs.ProductId orderby hs.Amount descending select new ProductDataAnalysisodel { ProductName = hp.ProductName, ProductCode = hp.ProductCode, Amount = hs.Amount.Value }).Take(20); pageModel.Models = data5.FindBy(item => true, productDataAnalysisQuery.PageNo, productDataAnalysisQuery.PageSize, out total, item => item.Amount, false); break; case 6: var data6 = (from hp in GPEcontext.hbl_product join hs in from hps2 in GPEcontext.hbl_product_stock join p in from hps in GPEcontext.hbl_product_stock group hps by new { hps.ProductID } into G select new { ProductId = G.Key.ProductID, CreatedTime = G.Max(p => p.CreatedTime) } on new { A = hps2.ProductID, B = hps2.CreatedTime } equals new { A = p.ProductId, B = p.CreatedTime } select new { ProductId = hps2.ProductID, Amount = hps2.Stock } on hp.id equals hs.ProductId orderby hs.Amount select new ProductDataAnalysisodel { ProductName = hp.ProductName, ProductCode = hp.ProductCode, Amount = hs.Amount.Value }).Take(20); pageModel.Models = data6.FindBy(item => true, productDataAnalysisQuery.PageNo, productDataAnalysisQuery.PageSize, out total, item => item.Amount, false); break; }
Linq使用Group By 1 1.简单形式: var q = from p in db.Products group p by p.CategoryID into g select g; 语句描述:Linq使用Group By按CategoryID划分产品。 说明:from p in db.Products 表示从表中将产品对象取出来。group p by p.CategoryID into g表示对p按CategoryID字段归类。其结果命名为g,一旦重新命名,p的作用域就结束了,所以,最后select时,只能select g。 2.最大值 var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, MaxPrice = g.Max(p => p.UnitPrice) }; 语句描述:Linq使用Group By和Max查找每个CategoryID的最高单价。 说明:先按CategoryID归类,判断各个分类产品中单价最大的Products。取出CategoryID值,并把UnitPrice值赋给MaxPrice。 3.最小值 var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, MinPrice = g.Min(p => p.UnitPrice) }; 语句描述:Linq使用Group By和Min查找每个CategoryID的最低单价。 说明:先按CategoryID归类,判断各个分类产品中单价最小的Products。取出CategoryID值,并把UnitPrice值赋给MinPrice。 4.平均值 var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, AveragePrice = g.Average(p => p.UnitPrice) }; 语句描述:Linq使用Group By和Average得到每个CategoryID的平均单价。 说明:先按CategoryID归类,取出CategoryID值和各个分类产品中单价的平均值。 5.求和 var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, TotalPrice = g.Sum(p => p.UnitPrice) };
今天遇到了一个问题,操作一个DataTable,DataTable的表结构如下:
- Id,Name,Math,Chinese
此表对应的数据如下图:
要求合并后的效果如下图:
自己就想到了for循环的实现办法,也想用Linq可是不知道怎么写。后来msdn了一下,写出了如下的代码:
var query = from c in ds.Tables[0].AsEnumerable() group c by c.Field<int>("Name") into s select new { ID = s.Select(m => m.Field<int>("ID").ToString()), Name = s.Select(m => m.Field<string>("Name").ToString()), Math = s.Sum(p => p.Field<decimal>("Math")), Chinese = s.Sum(p => p.Field<decimal>("Chinese")) };
//法一:
DataTable tbl = tableA1.Clone(); query.ToList().ForEach(p => tbl.Rows.Add(p.pingming, p.guige,p.biaohao,p.shuliang));
//法二:
DataTable dtss = new DataTable();
dtss.Columns.Add(new DataColumn("ID", typeof(string)));
dtss.Columns.Add(new DataColumn("Name", typeof(string)));
dtss.Columns.Add(new DataColumn("Math", typeof(string)));
dtss.Columns.Add(new DataColumn("Chinese", typeof(string)));
query.ToList().ForEach(p => dtss.Rows.Add(p.ID, p.Name, p.Math, p.Chinese ));
注意:linq结果可以用AsEnumerable立刻执行查询以及ToList ToArray转换成数组/集合