linq的实例应用

使用控制台应用做以下练习

我们现在有三张表 分别是学生表(Student) ,讲师表(Teacher),课程表(Coures)和成绩表(Sc),它们拥有的字段如下:

讲师表(Teacher):

     public   class Teather
     {
        public int TNO { get; set; }
        public string Tname { get; set; }

        public string Tsex { get; set; }

        public string Tbirthday { get; set; }

        public string Prof { get; set; }
        public string Depart { get; set; }
        public override string ToString()
        {
         return string.Format("TNO={0},Tname='{1}',Tsex='{2}',Tbirthday='{3}',Prof='{4}',Depart='{5}'",this.TNO,this.Tname,this.Tsex,this.Tbirthday,this.Prof,this.Depart);
        }
     }

学生表(Student)

  public class student
    {
        public int Sno { get; set; }
        public string Sname { get; set; }

        public string Ssex { get; set; }

        public string Sbirthday { get; set; }

        public string Class { get; set; }
        public override string ToString()
        {
            return string.Format("Sno={0},Sname={1},Ssex={2},Sbirthday={3},Class={4}", this.Sno, this.Sname, this.Ssex, this.Sbirthday, this.Class);
        }
    }

课程表(Coures)

   public  class Coures
    {
        public string CNO { get; set; }
        public string Cname { get; set; }

        public int TNO { get; set; }

    }

 

成绩表(Sc)

 public   class Sc
    {
        public int SNO { get; set; }
        public string CNO { get; set; }

        public double score { get; set; }

        public override string ToString()
        {
            return string.Format("SNO={0},CNO='{1}',score={2}",this.SNO,this.CNO,this.score);
        }
    }

下面我们依次给它们赋值

  var ArrayStu = new student[]
            {
                new student() { Sno=101,Sname="李军",Ssex="男", Sbirthday="1976-02-20 00:00:00.000",Class="5033"},
                new student() { Sno=103,Sname="陆君",Ssex="男", Sbirthday="1974-06-03 00:00:00.000",Class="95031"},
                new student() { Sno=105,Sname="匡明",Ssex="男", Sbirthday="1975-10-02 00:00:00.000",Class="95031"},
                new student() { Sno=107,Sname="王丽",Ssex="女", Sbirthday="1976-01-23 00:00:00.000",Class="95033"},
                new student() { Sno=108,Sname="曾华",Ssex="男", Sbirthday="1977-09-01 00:00:00.000",Class="95033"},
                new student() { Sno=109,Sname="王芳",Ssex="女", Sbirthday="1975-02-10 00:00:00.000",Class="95031" }
            };

            var ArraySc = new Sc[]
            {
              new Sc() {  SNO=101 ,CNO="3-105",score=64.00 } ,
              new Sc() {  SNO=101 ,CNO="6-166",score=85.00 } ,
              new Sc() {  SNO=103 ,CNO="3-105",score=92.00 } ,
              new Sc() {  SNO=103 ,CNO="3-245",score=86.00 } ,
              new Sc() {  SNO=105 ,CNO="3-105",score=88.00 } ,
              new Sc() {  SNO=105 ,CNO="3-245",score=75.00 } ,
              new Sc() {  SNO=107 ,CNO="3-105",score=91.00 } ,
              new Sc() {  SNO=107 ,CNO="6-166",score=79.00 } ,
              new Sc() {  SNO=108 ,CNO="3-105",score=78.00 } ,
              new Sc() {  SNO=108 ,CNO="6-166",score=81.00 } ,
              new Sc() {  SNO=109 ,CNO="3-105",score=76.00 } ,
              new Sc() { SNO =109 ,CNO="3-245",score=68.00 }

            };

            var ArrayTea = new Teather[]
            {
              new Teather(){  TNO = 804 ,Tname="李诚",Tsex="男",Tbirthday="1958-12-02 00:00:00.000",Prof="副教授",Depart="计算机系"},
              new Teather() { TNO = 825 ,Tname="王萍" ,Tsex="女",  Tbirthday="1972-05-05 00:00:00.000",Prof="助教",Depart="计算机系"},
              new Teather() { TNO = 831 ,Tname="刘冰" ,Tsex="女",  Tbirthday="1977-08-14 00:00:00.000",Prof="助教",Depart="电子工程系" },
              new Teather() { TNO = 856 ,Tname="张旭" ,Tsex="男",  Tbirthday="1969-03-12 00:00:00.000",Prof="讲师",Depart="电子工程系"}

            };

            var ArrayCou = new Coures[]
            {
               new Coures(){ CNO ="3-105",Cname="PMP项目管理", TNO=804} ,
               new Coures(){ CNO="3-245" ,Cname="C#高级特性",  TNO=804} ,
               new Coures(){ CNO="6-166", Cname="SOA架构体现", TNO=856} ,
               new Coures(){ CNO="9-888", Cname="大实训一", TNO=831}
            };

这些准备工作都完成之后我们就可以开始做一些linq的练习了

            Console.WriteLine(" 利用LINQ查询表达式查询Student对象中的所有记录并输出。");
            var stuArray1 = from s in ArrayStu select s;
            foreach (var item in stuArray1)
            {
                Console.WriteLine(item.ToString());
            }
            Console.ReadKey();
            Console.WriteLine("");
            Console.WriteLine("利用LINQ查询表达式查询Student对象中所有记录的Sname、Ssex和Class列并输出。");
            var stuArray2 = from s in ArrayStu select new {Sname=s.Sname,Ssex=s.Ssex,Class=s.Class };
            foreach (var item in stuArray2)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            Console.WriteLine("	利用LINQ查询表达式查询教师所在的系即不重复的Depart列并输出。");
            var teaArray1 = (from s in ArrayTea select s.Depart).Distinct();
            foreach (var item in teaArray1)
            {
                Console.WriteLine(item.ToString());
            }
            Console.ReadKey();
            Console.WriteLine("");
            Console.WriteLine("利用LINQ查询表达式查询Score对象中成绩在80到100之间的所有记录列并输出");
            var ScArray1 = from s in ArraySc where s.score >= 80 && s.score <= 100 select s;
            foreach (var item in ScArray1)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式查询学生姓名包含王的学生信息并输出。
            Console.WriteLine("利用LINQ查询表达式查询学生姓名包含王的学生信息并输出");
            var st1 = from s in ArrayStu where s.Sname.Contains('王') select s;
            foreach (var item in st1)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式查询Score对象中成绩为85，86或88的记录并输出。
            Console.WriteLine("利用LINQ查询表达式查询Score对象中成绩为85，86或88的记录并输出");
            var st2 = from s in ArraySc where s.score == 85 || s.score == 86 || s.score == 88 select s;
            foreach (var item in st2)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式查询Student对象中95031班或性别为女的同学记录并输出。
            var st3 = from s in ArrayStu where s.Class == "95031" || s.Ssex == "女" select s;
            foreach (var item in st3)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式查询以Class降序查询Student对象的所有记录并输出。(2分)
            Console.WriteLine("利用LINQ查询表达式查询以Class降序查询Student对象的所有记录并输出");
            var st4 = from s in ArrayStu orderby s.Class descending select s;
            foreach (var item in st4)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式以Cno升序、Degree降序查询Score对象的所有记录并输出。
            Console.WriteLine("利用LINQ查询表达式以Cno升序、Degree降序查询Score对象的所有记录并输出");
            var st5 = from s in ArraySc orderby s.CNO ascending orderby s.score descending select s;
            foreach (var item in st5)
            {
                Console.WriteLine(item.ToString());

            }
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式查询 95031 班的学生人数并输出。
            var st6 = (from s in ArrayStu where s.Class == "95031" select s).Count();
            Console.WriteLine(st6);
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ查询表达式查询'3-105'号课程的平均分并输出
            var st7 = (from s in ArraySc where s.CNO == "3-105" select s.score).Average();
            Console.WriteLine(st7);
            Console.ReadKey();
            Console.WriteLine("");
            //利用LINQ连接查询,join查询表达式查询所有学生的Sname、Cno和Degree列并输出。
            var st8 = from s in ArrayStu
                      join ss in ArraySc on s.Sno equals ss.SNO
                      join sss in ArrayCou on ss.CNO equals sss.CNO
                      select new
                      {
                          s.Sname,
                          ss.score,
                          sss.CNO
                      };
            foreach (var item in st8)
            {
                Console.WriteLine(item.ToString());
            }
            Console.ReadKey();
            Console.WriteLine("");
            Console.ReadKey();