使用Let临时局部聚集变量
在使用Lambea函数的时候,我们常常想在查询的过程中使用一些临时变量来存储索引值,平均数或者最大,最小值等,LINQ中提供了let关键字来解决这个问题:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
//-----------------------------------------------------------
// All Rights Reserved , Copyright (C) 2010 ,黄聪 , Ltd .
//-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//构造测试数据
List<int> list = Enumerable.Range(0, 20).ToList();
//获取索引值为偶数并且大于10的数,其中用let临时变量存储索引值
var query = from u in list let index = list.IndexOf(u) where u > 10 && index % 2 == 0 select u;
Console.WriteLine("获取索引值为偶数并且大于10的数,其中用let临时变量存储索引值");
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
}
// All Rights Reserved , Copyright (C) 2010 ,黄聪 , Ltd .
//-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//构造测试数据
List<int> list = Enumerable.Range(0, 20).ToList();
//获取索引值为偶数并且大于10的数,其中用let临时变量存储索引值
var query = from u in list let index = list.IndexOf(u) where u > 10 && index % 2 == 0 select u;
Console.WriteLine("获取索引值为偶数并且大于10的数,其中用let临时变量存储索引值");
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
}
运行结果:
下面再来个难一点的查询,用于查询相应货物的销售量和销售额,这类查询在一般的办公系统中常常会用到,我们就看看如何用LINQ来进行简洁的查询吧:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
//-----------------------------------------------------------
// All Rights Reserved , Copyright (C) 2010 ,黄聪 , Ltd .
//-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
List<货物> goods = new List<货物>();
goods.Add(new 货物 { Id = 1, Name = "盐焗鸡", Price = 12.5M });
goods.Add(new 货物 { Id = 2, Name = "冰花酸", Price = 21M });
goods.Add(new 货物 { Id = 3, Name = "里脊肉", Price = 33.5M });
goods.Add(new 货物 { Id = 4, Name = "红豆沙", Price = 2.5M });
goods.Add(new 货物 { Id = 5, Name = "糖桂花", Price = 15M });
goods.Add(new 货物 { Id = 6, Name = "面条", Price = 12M });
List<销售> sales = new List<销售>();
sales.Add(new 销售 { Id = 1, 货物Id = 1, Count = 3, 销售日期 = new DateTime(2010, 5, 1) });
sales.Add(new 销售 { Id = 2, 货物Id = 2, Count = 8, 销售日期 = new DateTime(2010, 5, 1) });
sales.Add(new 销售 { Id = 3, 货物Id = 4, Count = 2, 销售日期 = new DateTime(2010, 5, 1) });
sales.Add(new 销售 { Id = 4, 货物Id = 1, Count = 5.5, 销售日期 = new DateTime(2010, 5, 2) });
sales.Add(new 销售 { Id = 5, 货物Id = 2, Count = 8.2, 销售日期 = new DateTime(2010, 5, 2) });
sales.Add(new 销售 { Id = 6, 货物Id = 3, Count = 2, 销售日期 = new DateTime(2010, 5, 2) });
sales.Add(new 销售 { Id = 7, 货物Id = 6, Count = 55, 销售日期 = new DateTime(2010, 5, 3) });
sales.Add(new 销售 { Id = 8, 货物Id = 2, Count = 12, 销售日期 = new DateTime(2010, 5, 4) });
sales.Add(new 销售 { Id = 9, 货物Id = 4, Count = 4.5, 销售日期 = new DateTime(2010, 5, 4) });
sales.Add(new 销售 { Id = 10, 货物Id = 5, Count = 3, 销售日期 = new DateTime(2010, 5, 4) });
sales.Add(new 销售 { Id = 11, 货物Id = 3, Count = 6.5, 销售日期 = new DateTime(2010, 5, 4) });
//统计所以货物的销售额,要求输出格式为: 货物名称 单价 销量 总额
var query = from g in goods
let saleCount = sales.Where(s => s.货物Id == g.Id).Sum(s => s.Count)
select new
{
货物名称 = g.Name,
单价 = g.Price,
销量 = saleCount,
总额 = (decimal)saleCount * g.Price
};
Console.WriteLine("货物名称\t单价\t销量\t总额");
Console.WriteLine("----------------------------------------------------");
foreach (var q in query)
{
Console.WriteLine("{0}\t\t{1}\t{2}\t{3}", q.货物名称, q.单价, q.销量, q.总额);
}
}
}
public class 货物
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
public class 销售
{
public int Id { get; set; }
public int 货物Id { get; set; }
public double Count { get; set; }
public DateTime 销售日期 { get; set; }
}
}
// All Rights Reserved , Copyright (C) 2010 ,黄聪 , Ltd .
//-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
List<货物> goods = new List<货物>();
goods.Add(new 货物 { Id = 1, Name = "盐焗鸡", Price = 12.5M });
goods.Add(new 货物 { Id = 2, Name = "冰花酸", Price = 21M });
goods.Add(new 货物 { Id = 3, Name = "里脊肉", Price = 33.5M });
goods.Add(new 货物 { Id = 4, Name = "红豆沙", Price = 2.5M });
goods.Add(new 货物 { Id = 5, Name = "糖桂花", Price = 15M });
goods.Add(new 货物 { Id = 6, Name = "面条", Price = 12M });
List<销售> sales = new List<销售>();
sales.Add(new 销售 { Id = 1, 货物Id = 1, Count = 3, 销售日期 = new DateTime(2010, 5, 1) });
sales.Add(new 销售 { Id = 2, 货物Id = 2, Count = 8, 销售日期 = new DateTime(2010, 5, 1) });
sales.Add(new 销售 { Id = 3, 货物Id = 4, Count = 2, 销售日期 = new DateTime(2010, 5, 1) });
sales.Add(new 销售 { Id = 4, 货物Id = 1, Count = 5.5, 销售日期 = new DateTime(2010, 5, 2) });
sales.Add(new 销售 { Id = 5, 货物Id = 2, Count = 8.2, 销售日期 = new DateTime(2010, 5, 2) });
sales.Add(new 销售 { Id = 6, 货物Id = 3, Count = 2, 销售日期 = new DateTime(2010, 5, 2) });
sales.Add(new 销售 { Id = 7, 货物Id = 6, Count = 55, 销售日期 = new DateTime(2010, 5, 3) });
sales.Add(new 销售 { Id = 8, 货物Id = 2, Count = 12, 销售日期 = new DateTime(2010, 5, 4) });
sales.Add(new 销售 { Id = 9, 货物Id = 4, Count = 4.5, 销售日期 = new DateTime(2010, 5, 4) });
sales.Add(new 销售 { Id = 10, 货物Id = 5, Count = 3, 销售日期 = new DateTime(2010, 5, 4) });
sales.Add(new 销售 { Id = 11, 货物Id = 3, Count = 6.5, 销售日期 = new DateTime(2010, 5, 4) });
//统计所以货物的销售额,要求输出格式为: 货物名称 单价 销量 总额
var query = from g in goods
let saleCount = sales.Where(s => s.货物Id == g.Id).Sum(s => s.Count)
select new
{
货物名称 = g.Name,
单价 = g.Price,
销量 = saleCount,
总额 = (decimal)saleCount * g.Price
};
Console.WriteLine("货物名称\t单价\t销量\t总额");
Console.WriteLine("----------------------------------------------------");
foreach (var q in query)
{
Console.WriteLine("{0}\t\t{1}\t{2}\t{3}", q.货物名称, q.单价, q.销量, q.总额);
}
}
}
public class 货物
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
public class 销售
{
public int Id { get; set; }
public int 货物Id { get; set; }
public double Count { get; set; }
public DateTime 销售日期 { get; set; }
}
}
运行结果:
小结
在复杂查询中let临时局部变量能帮你很好的解决多表查询中需要临时存放一些聚合数据的问题,在此用一个小小示例演示给大家看,权当抛石引玉,如果哪位大侠有更好的解决方法,请告诉小弟,我也希望能够能有更大的突破~~
作者:黄聪
出处:http://www.cnblogs.com/huangcong/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
出处:http://www.cnblogs.com/huangcong/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。