使用数据集
1 /// <summary> 2 /// 库房信息类 3 /// </summary> 4 public class Kfxx 5 { 6 /// <summary> 7 /// 库房代码 8 /// </summary> 9 public string kfdm { get; set; } 10 /// <summary> 11 /// 库房名称 12 /// </summary> 13 public string kfmc { get; set; } 14 /// <summary> 15 /// 使用状态：0有效 1无效 16 /// </summary> 17 public int jlzt { get; set; } 18 } 19 /// <summary> 20 /// 帐类信息类 21 /// </summary> 22 public class Zlxx 23 { 24 /// <summary> 25 /// 库房代码 26 /// </summary> 27 public string kfdm { get; set; } 28 /// <summary> 29 /// 帐类代码 30 /// </summary> 31 public string zldm { get; set; } 32 /// <summary> 33 /// 帐类名称 34 /// </summary> 35 public string zlmc { get; set; } 36 /// <summary> 37 /// 金额 38 /// </summary> 39 public decimal je { get; set; } 40 } 41 /// <summary> 42 /// 统计期信息类 43 /// </summary> 44 public class Tjqxx 45 { 46 /// <summary> 47 /// 库房代码 48 /// </summary> 49 public string kfdm { get; set; } 50 /// <summary> 51 /// 帐类代码 52 /// </summary> 53 public string zldm { get; set; } 54 /// <summary> 55 /// 统计期 56 /// </summary> 57 public string ny { get; set; } 58 /// <summary> 59 /// 开始日期 60 /// </summary> 61 public DateTime ksrq { get; set; } 62 /// <summary> 63 /// 结束日期 64 /// </summary> 65 public DateTime jsrq { get; set; } 66 /// <summary> 67 /// 记账标志 0未记账 1已记账 68 /// </summary> 69 public int jzbz { get; set; } 70 /// <summary> 71 /// 金额 72 /// </summary> 73 public decimal je { get; set; } 74 } 75 76 /// <summary> 77 /// 数据定义 78 /// </summary> 79 public class DataSource 80 { 81 public static List<Kfxx> kfxx { get; private set; } 82 public static List<Kfxx> kfxx2 { get; private set; } 83 public static List<Zlxx> zlxx { get; private set; } 84 public static List<Tjqxx> tjqxx { get; private set; } 85 static DataSource() 86 { 87 kfxx = new List<Kfxx> 88 { 89 new Kfxx{kfdm="500",kfmc="总务科",jlzt=0}, 90 new Kfxx{kfdm="503",kfmc="设备科",jlzt=0}, 91 new Kfxx{kfdm="600",kfmc="供应室",jlzt=0}, 92 new Kfxx{kfdm="500",kfmc="总务科",jlzt=1} 93 }; 94 kfxx2 = new List<Kfxx> 95 { 96 new Kfxx{kfdm="888",kfmc="Union库房",jlzt=0} 97 }; 98 zlxx = new List<Zlxx> 99 { 100 new Zlxx{kfdm="500",zldm="01",zlmc="医用材料",je=100}, 101 new Zlxx{kfdm="500",zldm="02",zlmc="一次性材料",je=200}, 102 new Zlxx{kfdm="503",zldm="03",zlmc="器械材料",je=300}, 103 new Zlxx{kfdm="503",zldm="04",zlmc="无统计期",je=400} 104 }; 105 tjqxx = new List<Tjqxx> 106 { 107 new Tjqxx{kfdm="500",zldm="01",ny="201306",ksrq=Convert.ToDateTime("2013-06-01 00:00:00"),jsrq=Convert.ToDateTime("2013-06-30 23:59:59"),jzbz=1,je=100}, 108 new Tjqxx{kfdm="500",zldm="01",ny="201307",ksrq=Convert.ToDateTime("2013-07-01 00:00:00"),jsrq=Convert.ToDateTime("2013-07-31 23:59:59"),jzbz=0,je=200}, 109 new Tjqxx{kfdm="500",zldm="02",ny="201307",ksrq=Convert.ToDateTime("2013-07-01 00:00:00"),jsrq=Convert.ToDateTime("2013-07-31 23:59:59"),jzbz=0,je=300}, 110 new Tjqxx{kfdm="503",zldm="03",ny="201307",ksrq=Convert.ToDateTime("2013-07-01 00:00:00"),jsrq=Convert.ToDateTime("2013-07-31 23:59:59"),jzbz=0,je=400} 111 }; 112 } 113 }
1.简单查询
var Query = from a in DataSource.kfxx where a.kfdm=="500" select new { a.kfdm, a.kfmc };
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Where(p => p.kfdm == "500");
2.distinct,first,last,skip,take,single
var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Distinct(); var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).First(); var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Last(); var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Skip(2); var Query = DataSource.kfxx.Select(p => new { p.kfdm, p.kfmc }).Take(3); var Query = DataSource.kfxx.Where(p => p.kfdm == "503").Select(p => new { p.kfdm, p.kfmc }).Single();
3.排序
var Query = from a in DataSource.kfxx orderby a.kfdm descending, a.jlzt select new { a.kfdm, a.jlzt };
var Query = DataSource.kfxx.OrderByDescending(p => p.kfdm).ThenBy(p => p.jlzt).Select(p => new { p.kfdm, p.jlzt });
4.Join
4.1 inner join
var Query = from a in DataSource.kfxx from b in DataSource.zlxx where a.jlzt == 0 && a.kfdm==b.kfdm orderby a.kfdm descending, a.jlzt select new { a.kfdm, a.kfmc, b.zldm, b.zlmc };
var Query = (DataSource.zlxx.Join(DataSource.kfxx.Where(q => q.jlzt == 0), p => p.kfdm, q => q.kfdm, (p, q) => new { q.kfdm, q.kfmc, p.zldm, p.zlmc, q.jlzt })) .OrderByDescending(s => s.kfdm).ThenBy(s => s.jlzt) .Select(t => new { t.kfdm, t.kfmc, t.zldm, t.zlmc });
4.2 left join
var Query = from a in DataSource.kfxx from b in DataSource.zlxx join c in DataSource.tjqxx on new { kfdm = b.kfdm, zldm = b.zldm } equals new { kfdm = c.kfdm, zldm = c.zldm } into gg from g in gg.DefaultIfEmpty() where a.jlzt == 0 && a.kfdm == b.kfdm && (g == null ? 0 : g.jzbz) == 0 //orderby a.kfdm descending, a.jlzt select new { a.kfdm, a.kfmc, b.zldm, b.zlmc, ny = g == null ? "" : g.ny, ksrq = g == null ? "" : g.ksrq.ToString(), jsrq = g == null ? "" : g.jsrq.ToString() };
var Query = (DataSource.zlxx.Join(DataSource.kfxx.Where(q => q.jlzt == 0), p => p.kfdm, q => q.kfdm, (p, q) => new { q.kfdm, q.kfmc, p.zldm, p.zlmc })) .GroupJoin(DataSource.tjqxx.Where(tjq=>tjq.jzbz==0), zl => new { zl.kfdm, zl.zldm }, tjq => new { tjq.kfdm, tjq.zldm }, (zl, tjq) => new { zl = zl, tjq = tjq }) .SelectMany(group => group.tjq.DefaultIfEmpty(), (group, tjq) => new { group.zl.kfdm, group.zl.kfmc, group.zl.zldm, group.zl.zlmc, ny = tjq == null ? "" : tjq.ny, ksrq = tjq == null ? "" : tjq.ksrq.ToString(), jsrq = tjq == null ? "" : tjq.jsrq.ToString() } );
5.聚合(count,sum,max,min,avg)
var Query = from a in DataSource.kfxx join b in DataSource.zlxx on a.kfdm equals b.kfdm into gg where a.jlzt == 0 from g in gg.DefaultIfEmpty() group g by a.kfdm into gg1 where gg1.Sum(p => p == null ? 0 : p.je)>500 select new { kfdm = gg1.Key, zlcount = gg1.Count(p => (p == null ? "" : p.zldm) != ""), zje = gg1.Sum(p => p == null ? 0 : p.je), je_min = gg1.Min(p => p == null ? 0 : p.je), je_max = gg1.Max(p => p == null ? 0 : p.je), je_avg = gg1.Average(p => p == null ? 0 : p.je) };
var Query = DataSource.kfxx.Where(g => g.jlzt == 0).GroupJoin(DataSource.zlxx, kf => kf.kfdm, zl => zl.kfdm, (kf, zl) => new { kfdm = kf.kfdm, zlcount = zl.DefaultIfEmpty().Count(p => (p == null ? "" : p.zldm) != ""), zje = zl.DefaultIfEmpty().Sum(p => p == null ? 0 : p.je), je_min = zl.DefaultIfEmpty().Min(p => p == null ? 0 : p.je), je_max = zl.DefaultIfEmpty().Max(p => p == null ? 0 : p.je), je_avg = zl.DefaultIfEmpty().Average(p => p == null ? 0 : p.je) }).Where(g=>g.zje>500);
6.其他
6.1 Union
var Query = DataSource.kfxx.Union(DataSource.kfxx2);
6.2 自定义聚合Aggregate
//自定义求平均为例 var Query = DataSource.kfxx.Where(g => g.jlzt == 0).GroupJoin(DataSource.zlxx, kf => kf.kfdm, zl => zl.kfdm, (kf, zl) => new { kfdm = kf.kfdm, je_avg0 = zl.DefaultIfEmpty().Average(p => p == null ? 0 : p.je), je_avg1 = zl.DefaultIfEmpty().Aggregate(new Zlxx {je=0}, (a, b) => { return new Zlxx { je = (a == null ? 0 : a.je) + (b == null ? 0 : b.je) }; }, c => new Zlxx { je = (c == null ? 0 : c.je )/ (zl.DefaultIfEmpty().Count(p => (p == null ? "" : p.zldm) != "") == 0 ? 1 : (zl.DefaultIfEmpty().Count(p => (p == null ? "" : p.zldm) != ""))) }).je.ToString("C") });
源代码(http://download.csdn.net/detail/lk13962517093/5692997)
小弟刚学，不正确之处请指点。