LINQ

LINQ类似于SQL查询语言，通过设定一系列的查询条件对表中数据进行查找和筛选，他分为以下几种语句：

1.筛选：Where ：返回满足条件的元素子集，take：返回前count个元素舍弃其他元素，skip：返回后count个元素，舍弃其他元素，Distinct: 返回不含重复元素的集合。

2.投影：select：根据给定的lamda表达式对每个输入元素进行转换，selectmany：将元素展开并连接成结果序列。

3.连接：join：通过查找策略来匹配满足条件的两个集合中的元素，groupjoin：输出层次结果。

4.排序：orderby，thenby：按升序进行排序，orderbydecending，thenbydecending：按降序进行排序。

5.set运算符：union：并集，intersect：交集，except：差；

6：元素运算符: first: 前n个满足条件的元素，last：后n个满足条件的元素，elementat：第n个元素，count：元素数目，Min：最小值，max：最大值，sum：和，average：平均数。

这次试验我首先先建立三个表，也就是三个类：reserves，boat，sailors

 class Boats
    {
        public int bid { get; set; }
        public string color { get; set; }
        public string bname{ get; set; }
        
        public override string ToString ()
        {
           return string.Format("{0},  {1},  {2}\n", bid.ToString(), color.ToString(), bname.ToString());
        }
        
    }

 class Reserve
    {
        public int sid { get; set; }
        public int bid { get; set; }

        public override string ToString()
        {
            return string.Format( "{0} , {1} , {2}\n",sid.ToString(),bid.ToString());
        }

     }

class Sailors
    {
        public int sid { get; set; }
        public string sname { get; set; }
        public int rank { get; set; }
        public double age { get; set; }

        public override string ToString()
        {
            return string.Format("{0} ,{1} , {2}\n", sid.ToString(), sname.ToString(),rank.ToString(), age.ToString());
        } 
    }

下面表中插入数据

private static List<Sailors> createSailors()
        {
     
            List<Sailors>  sailors = new List<Sailors> {
                new Sailors{ sid = 22,sname = "dustin",rank = 7, age= 45},
                new Sailors{sid = 29,sname = "brustus",rank = 1, age = 33},
                new Sailors{ sid = 31 ,sname = "lubber",rank = 8,age = 56 },
                new Sailors { sid = 32, sname = "andy", rank = 8,age = 26},
                new Sailors { sid = 58, sname = "rusty", rank = 10,age = 35},
                new Sailors { sid = 64 , sname = "horatio", rank = 7,age = 35},
                new Sailors { sid = 71,sname = "zorba", rank = 10, age = 35},
                new Sailors { sid = 74, sname = "horatio", rank = 9,age = 35},
                new Sailors { sid = 85, sname = "art", rank = 3,age = 26 },
                new Sailors { sid = 86,sname = "john", rank =  1,age = 17},
                new Sailors { sid = 95,sname = "bob", rank = 3,age= 64},
                new Sailors { sid = 96,sname = "frodo", rank = 3 , age = 26},
                new Sailors { sid = 98,sname = "tom",rank = 3,age = 17},
            };
            return sailors;
        }
       private static List<Boats> createBoats ()
        {
            List<Boats> boats = new List<Boats> 
           {
               new Boats { bid = 101,color = "red" , bname = "A"},
               new Boats { bid = 102, color = "green", bname = "B"},
               new Boats { bid = 103, color = "blue", bname = "C"},
               new Boats { bid = 104, color = "white", bname = "D"},
               new Boats { bid = 105, color = "red" , bname = "E"},
               new Boats { bid = 106 , color = "blue" , bname = "F"},
               new Boats { bid = 107, color = "green" , bname = "G"},
           };
            return boats;
        }
        private static List<Reserve> createReserve ()
       {
           List<Reserve> reserves = new List<Reserve> 
            {
                new Reserve{ sid = 22,bid = 101 },
                new Reserve { sid = 29,bid = 102},
                new Reserve{ sid = 31,bid = 103 },
                new Reserve{ sid = 22,bid = 104 },
                new Reserve{ sid = 22,bid = 103 },
                new Reserve{ sid = 32,bid = 105 },
                new Reserve{ sid = 32,bid = 106 },
                new Reserve{ sid = 32,bid = 102 },
                new Reserve{ sid = 58,bid = 104 },
                new Reserve{ sid = 64,bid = 105 },
                new Reserve{ sid = 95,bid = 101 },
                new Reserve{ sid = 85,bid = 102 },
                new Reserve{ sid = 22,bid = 101 },
                new Reserve{ sid = 22,bid = 105 },
                new Reserve{ sid = 22,bid = 106 },
                new Reserve{ sid = 22,bid = 107 },
                new Reserve{ sid = 31,bid = 106 },
                new Reserve{ sid = 32,bid = 105 },
                new Reserve{ sid = 64,bid = 103 },
                new Reserve{ sid = 58,bid = 102 },
                new Reserve{ sid = 64,bid = 104 },
                new Reserve{ sid = 64,bid = 105 },
                new Reserve{ sid = 31,bid = 106 },
            };
            return reserves;
       }
    }

1：将船员按级别分组并输出每个组的人数。

 static void Main(string[] args)
        {
            List<Reserve> reserves = new List<Reserve>(createReserve());
            List<Sailors> sailors = new List<Sailors>(createSailors());
            List<Boats> boats = new List<Boats>(createBoats());
            var result = sailors.GroupBy(s => s.rank).Select( s => new { count = s.Count(),rank = s.Key});
            foreach ( var a in result)
            {
                Console.WriteLine( a.rank + ":" +a.count);

            }
         }

2.查找定过船而没有定过红色船的水手信息

 List<Reserve> reserves = new List<Reserve>(createReserve());
            List<Sailors> sailors = new List<Sailors>(createSailors());
            List<Boats> boats = new List<Boats>(createBoats());
            var result = (from reserve in reserves
                          from boat in boats
                          where reserve.bid == boat.bid && boat.color == "red"
                          select new { sid = reserve.sid }).Distinct();
            var result1 = (from sailor in sailors
                           select sailor.sid).Except(from res in result select res.sid);
            var result2 = from sailor in sailors
                           from res1 in result1
                           where sailor.sid == res1
                           select sailor;
            foreach ( var a in result2)
            {
                Console.WriteLine(a.ToString());

            }
         }

3.6.查找年龄最大的水手姓名和年龄

 static void Main(string[] args)
        {
            List<Reserve> reserves = new List<Reserve>(createReserve());
            List<Sailors> sailors = new List<Sailors>(createSailors());
            List<Boats> boats = new List<Boats>(createBoats());
            var result1 = sailors.Max(s => s.age);
            var result2 = from sailor in sailors
                          where sailor.age == result1
                          select new { age = sailor.age, name = sailor.sname };
            foreach ( var a in result2)
            {
                Console.WriteLine(a.name+ " " +a.age);
            }
         }