Linq中的高级用法

实体

Commodity： class Commodity { public int Id { get; set; } public string Name { get; set; } public string Price { get; set; } public int Num { get; set; } } Customer： class Customer { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Sex { get; set; } public int Age { get; set; } } ShoppingCart: class ShoppingCart { public int CommodityId { get; set; } public int CustomerId { get; set; } public int Num { get; set; } }

初始数据

Customer[] customers = new Customer[]{ new Customer{Id=000001,FirstName="李",LastName="逍遥",Age=18,Sex="男"}, new Customer{Id=000002,FirstName="赵",LastName="灵儿",Age=16,Sex="女"}, new Customer{Id=000003,FirstName="林",LastName="月如",Age=18,Sex="女"}, new Customer{Id=000004,FirstName="阿",LastName="奴",Age=17,Sex="女"} }; Commodity[] commodities = new Commodity[]{ new Commodity{Id=1,Name="金创药",Price="100",Num=999}, new Commodity{Id=2,Name="花露水",Price="150",Num=999}, new Commodity{Id=3,Name="腊肉",Price="55",Num=999}, new Commodity{Id=4,Name="盐巴",Price="20",Num=999}, new Commodity{Id=5,Name="黑玉断续膏",Price="1000",Num=100}, new Commodity{Id=6,Name="九花玉露丸",Price="2000",Num=100} }; ShoppingCart[] shoppingCart = new ShoppingCart[]{ new ShoppingCart{CommodityId=1,CustomerId=000001,Num=100}, new ShoppingCart{CommodityId=2,CustomerId=000001,Num=100}, new ShoppingCart{CommodityId=1,CustomerId=000002,Num=50}, new ShoppingCart{CommodityId=2,CustomerId=000002,Num=100}, new ShoppingCart{CommodityId=4,CustomerId=000003,Num=20}, new ShoppingCart{CommodityId=3,CustomerId=000003,Num=23}, new ShoppingCart{CommodityId=5,CustomerId=000003,Num=1}, new ShoppingCart{CommodityId=6,CustomerId=000003,Num=2} };

分组（为多个字段进行分组）

var data3 = from sp in shoppingCart.AsEnumerable<ShoppingCart>() group sp by new { sp.CommodityId, sp.CustomerId } into cc select new { CommodityTypeNum = cc.Count<ShoppingCart>(), CommodityId=cc.Key.CommodityId, CustomerId=cc.Key.CustomerId }; Console.WriteLine("Three Search is Begin"); foreach (var tmp in data3) { Console.WriteLine(tmp.CommodityTypeNum); } Console.WriteLine("\t\n\t\n");

执行结果：

Three Search is Begin

1

1

1

1

1

1

1

1

分组（对不同表中的字段进行分组）

var data1 = from customer in customers.AsEnumerable<Customer>() join shoppingcart in shoppingCart.AsEnumerable<ShoppingCart>() on customer.Id equals shoppingcart.CustomerId join commodity in commodities.AsEnumerable<Commodity>() on shoppingcart.CommodityId equals commodity.Id group new { customer,shoppingcart,commodity} by customer.FirstName+customer.LastName into shopDetails select new { CustomerName = shopDetails.Key, ItemNum = shopDetails.Count(), Items = from t in shopDetails select new { CommodityName=t.commodity.Name, CommodityPrice=t.commodity.Price, Num=t.shoppingcart.Num } }; foreach (var tmp in data1) { Console.WriteLine(string.Format("CustomerName:{0},CommodityId:{1}" , new object[] { tmp.CustomerName, tmp.ItemNum})); foreach (var tmp2 in tmp.Items) { Console.WriteLine(string.Format("\tCommodityName:{0} ,CommodityPrice:{1},Num:{2}", tmp2.CommodityName, tmp2.CommodityPrice , tmp2.Num)); } }

执行结果：

CustomerName:李逍遥,CommodityId:2

CommodityName:金创药,CommodityPrice:100,Num:100

CommodityName:花露水,CommodityPrice:150,Num:100

CustomerName:赵灵儿,CommodityId:2

CommodityName:金创药,CommodityPrice:100,Num:50

CommodityName:花露水,CommodityPrice:150,Num:100

CustomerName:林月如,CommodityId:4

CommodityName:盐巴,CommodityPrice:20,Num:20

CommodityName:腊肉,CommodityPrice:55,Num:23

CommodityName:黑玉断续膏,CommodityPrice:1000,Num:1

CommodityName:九花玉露丸,CommodityPrice:2000,Num:2

左/右连接

var data2 = from c in customers.AsEnumerable() join sc in shoppingCart.AsEnumerable() on c.Id equals sc.CustomerId into tmpGroup //这行代码是右连接的关键 from leftjoindata in tmpGroup .DefaultIfEmpty( new ShoppingCart { CommodityId = -1, CustomerId = -1, Num = -1 } ) select new { CustomerName=c.FirstName+c.LastName, CommodityId=leftjoindata.CommodityId, CommodityNum=leftjoindata.Num }; foreach (var tmp in data2) { Console.WriteLine( string.Format("CustomerName:{0} ,CommodityId:{1} ,CommodityNum:{2}" , tmp.CustomerName , tmp.CommodityId , tmp.CommodityNum)); }

执行结果：

CustomerName:李逍遥,CommodityId:1,CommodityNum:100

CustomerName:李逍遥,CommodityId:2,CommodityNum:100

CustomerName:赵灵儿,CommodityId:1,CommodityNum:50

CustomerName:赵灵儿,CommodityId:2,CommodityNum:100

CustomerName:林月如,CommodityId:4,CommodityNum:20

CustomerName:林月如,CommodityId:3,CommodityNum:23

CustomerName:林月如,CommodityId:5,CommodityNum:1

CustomerName:林月如,CommodityId:6,CommodityNum:2

CustomerName:阿奴,CommodityId:-1,CommodityNum:-1