Linq与扩展方法

使用数据集

  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)

小弟刚学，不正确之处请指点。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liuke1987/p/3169391.html