实体
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