C# linq 语句用法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace StudyLinqExam
{
    class Linq
    {
        static void Main(string[] args)
        {
            List<Student> students = new List<Student>()
           {
            new Student{Sno=101,Sname="李军",Ssex="男",Sbirthday= "1976-02-20 00:00:00.000" ,ClassName= 95033 },
            new Student{Sno=103,Sname="陆君",Ssex="男",Sbirthday= "1974-06-03 00:00:00.000" ,ClassName= 95031 },
            new Student{Sno=105,Sname="匡明",Ssex="男",Sbirthday= "1975-10-02 00:00:00.000" ,ClassName= 95031 },
            new Student{Sno=107,Sname="王丽",Ssex="女",Sbirthday= "1976-01-23 00:00:00.000" ,ClassName= 95033 },
            new Student{Sno=108,Sname="曾华",Ssex="男",Sbirthday= "1977-09-01 00:00:00.000" ,ClassName= 95033 },
            new Student{Sno=109,Sname="王芳",Ssex="女",Sbirthday= "1975-02-10 00:00:00.000" ,ClassName= 95031 },

            };
            List<SC> cs = new List<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},
            };
            List<Course> courses = new List<Course>()
            {
              new Course{CNO="3-105",Cname="PMP项目管理",TNO=804 },
              new Course{CNO="3-245",Cname="C#高级特性",TNO=804 },
              new Course{CNO="6-166",Cname="SOA架构体现",TNO=856 },
              new Course{CNO="9-888",Cname="大实训一",TNO=831 },
            };
            List<Teacher> teachers = new List<Teacher>()
            {
              new Teacher{TNO=804,Tname="李诚",Tsex="男",Tbirthday="1958-12-02 00:00:00.000",Prof="副教授",Depart="计算机系" },
              new Teacher{TNO=825,Tname="王萍",Tsex="女",Tbirthday="1972-05-05 00:00:00.000",Prof="助教",Depart="计算机系" },
              new Teacher{TNO=831,Tname="刘冰",Tsex="女",Tbirthday="1977-08-14 00:00:00.000",Prof="助教",Depart="电子工程系" },
              new Teacher{TNO=856,Tname="张旭",Tsex="男",Tbirthday="1969-03-12 00:00:00.000",Prof="讲师",Depart="电子工程系" },
            };
            //10)利用LINQ查询表达式查询Student对象中的所有记录并输出。(2分)
            var Linq1 = from S in students
                        select S;
            foreach (var item in Linq1)
            {
                Console.WriteLine("学生编号" + (item.Sno).ToString() + "学生姓名" + item.Sname + "性别" + item.Ssex + "出生日期" + item.Sbirthday + "教室号" + item.ClassName);
            }
            //利用LINQ查询表达式查询Student对象中所有记录的Sname、Ssex和Class列并输出
            var Linq2 = from S in students
                        select new
                        {
                            Sname = S.Sname,
                            Ssex = S.Ssex,
                            Class = S.ClassName,
                        };
            foreach (var item in Linq2)
            {
                Console.WriteLine("学生姓名" + item.Sname + "性别" + item.Ssex + "教室号" + item.Class);
            }
            //利用LINQ查询表达式查询教师所在的系即不重复的Depart列并输出。
            var Linq3 = from T in teachers
                        select new
                        {
                            T.TNO,
                            T.Depart,
                        } into Table group Table by Table.Depart into Table1
                        select new {
                            Table1.Key,
                        };
            foreach (var item in Linq3)
            {
                Console.WriteLine(item.Key);
            }
            //利用LINQ查询表达式查询教师所在的系和职称即不重复的Depart、Prof列并输出
            var Linq4 = from T in teachers
                        select new
                        {
                            T.Depart,
                            T.Prof,
                        } into Table
                        group Table by Table.Prof into Table1
                        select new
                        {
                            Table1.Key,
                        };
            foreach (var item in Linq4)
            {
                Console.WriteLine(item.Key);
                foreach (var item1 in Linq3)
                {
                    Console.WriteLine(item1.Key);
                }
            }
            //利用LINQ查询表达式查询Score对象中成绩在80到100之间的所有记录列并输出
            var Linq5 = from Sc in cs
                        where Sc.Score >= 80
                        select new
                        {
                            Sc.SNO,
                            Sc.CNO,
                            Sc.Score,
                        };
            foreach (var item in Linq5)
            {
                Console.WriteLine("编号" + item.SNO + "课程编号" + item.CNO + "成绩" + item.Score);
            }
            //利用LINQ查询表达式查询学生姓名包含王的学生信息并输出
            var Linq6 = from S in students
                        where S.Sname.Contains("王")
                        select S;
            foreach (var item in Linq6)
            {
                Console.WriteLine("学生编号" + (item.Sno).ToString() + "学生姓名" + item.Sname + "性别" + item.Ssex + "出生日期" + item.Sbirthday + "教室号" + item.ClassName);
            }
            //利用LINQ查询表达式查询Score对象中成绩为85，86或88的记录并输出。
            var Linq7 = from Sc in cs
                        where Sc.Score == 85.00 || Sc.Score == 86.00 || Sc.Score == 88.00
                        select Sc;
            foreach (var item in Linq7)
            {
                Console.WriteLine("编号" + item.SNO + "课程编号" + item.CNO + "成绩" + item.Score);
            }
            //利用LINQ查询表达式查询Student对象中95031班或性别为女的同学记录并输出
            var Linq8 = from S in students
                        where S.Ssex == "女" || S.ClassName == 95031
                        select S;
            foreach (var item in Linq8)
            {
                Console.WriteLine("学生编号" + (item.Sno).ToString() + "学生姓名" + item.Sname + "性别" + item.Ssex + "出生日期" + item.Sbirthday + "教室号" + item.ClassName);
            }
            //利用LINQ查询表达式查询以Class降序查询Student对象的所有记录并输出
            var Linq9 = from S in students
                        orderby S.ClassName descending
                        select new
                        {
                            S.Sno,
                            S.ClassName,
                            S.Ssex,
                            S.Sname,
                            S.Sbirthday,
                        }   ;
            foreach (var item in Linq9)
            {
                Console.WriteLine("学生编号" + (item.Sno).ToString() + "学生姓名" + item.Sname + "性别" + item.Ssex + "出生日期" + item.Sbirthday + "教室号" + item.ClassName);
            }
            //利用LINQ查询表达式以Cno升序、Degree降序查询Score对象的所有记录并输出
            var Linq10 = from C in courses
                         orderby C.CNO descending
                         select new
                         {
                             C.CNO,
                             C.Cname,
                             C.TNO,
                         } into Table
                         orderby Table.CNO
                         select new
                         {
                           Table.CNO,
                           Table.Cname,
                           Table.TNO,
                         };
            foreach (var item in Linq10)
            {
                Console.WriteLine("课程编号"+item.CNO+"课程名称"+item.Cname+"教师编号"+item.TNO);
            }
            //利用LINQ查询表达式查询 95031 班的学生人数并输出
            var Linq11 = from S in students
                         where S.ClassName ==95031
                         select S;
            Console.WriteLine(Linq11.Count());
            //利用LINQ查询表达式查询'3-105'号课程的平均分并输出
            var Linq12 = (from sc in cs
                          where sc.CNO == "3-105"
                          select sc.Score
                          ).Average();
            Console.WriteLine(Linq12);
            //利用LINQ连接查询,join查询表达式查询所有学生的Sname、Cno和Degree列并输出
            var Linq13 = from S in students
                         join sc1 in cs on S.Sno equals sc1.SNO
                         join C in courses on sc1.CNO equals C.CNO
                         join T in teachers on C.TNO equals T.TNO
                         select new
                         {
                             S.Sname,
                             sc1.CNO,
                             T.Depart,
                         };
            foreach (var item in Linq13)
            {
                Console.WriteLine("学生姓名"+item.Sname+"课程编号"+item.CNO+"教师系别"+item.Depart);
            }
            //利用LINQ聚合函数查询表达式查询Score对象中的最高分的学生学号和课程号并输出。(5分
            var linq14 = (from S in students
                          join sc1 in cs on S.Sno equals sc1.SNO
                          join C in courses on sc1.CNO equals C.CNO
                          join T in teachers on C.TNO equals T.TNO
                          orderby sc1.Score descending
                          select new
                          {
                              S.Sno,
                              C.CNO,
                          }).First();
            Console.WriteLine(linq14);
            //利用LINQ分组查询查询表达式查询Score对象中至少有5名学生选修的并以3开头的课程的平均分数并输出。
            var Lin0 = (from S in students
                       join sc1 in cs on S.Sno equals sc1.SNO
                       where sc1.CNO.TrimEnd() == "3"
                       select new {
                            sc1.Score,
                         });
            foreach (var item in Lin0)
            {
                Console.WriteLine(item.Score);
            }
                     // 利用LINQ聚合函数查询表达式查询最低分大于70，最高s分小于90的Sno列并输出
                     var Linq15 = (from S in students
                         join sc1 in cs on S.Sno equals sc1.SNO
                         join C in courses on sc1.CNO equals C.CNO
                         join T in teachers on C.TNO equals T.TNO
                         orderby sc1.Score ascending
                         select new
                         {
                             sc1.Score,
                         }).First();
            var Linq16 = (from S in students
                         join sc1 in cs on S.Sno equals sc1.SNO
                         join C in courses on sc1.CNO equals C.CNO
                         join T in teachers on C.TNO equals T.TNO
                         orderby sc1.Score descending
                         select new
                         {
                             sc1.Score,
                         }).First();
            //利用LINQ查询表达式查询统计总成绩大于160的学生并输出
            var Linq17 = from S in students
                         join sc1 in cs on S.Sno equals sc1.SNO
                         select new
                         {
                             S.Sname,
                             sc1.Score,
                         } into Table
                         group Table by Table.Sname into Table2
                         
                         select new
                         {
                             Table2.Key,
                             Cout = Convert.ToDouble(Table2.Select(s => s.Score)),
                         } into Table3
                         where Table3.Cout > 160
                         select Table3;
            foreach (var item in Linq17)
            {
                Console.WriteLine(item.Key);
            } 
            
            Console.ReadKey();
        }
    }
    class Student   //创建Model类：Student。 学生
    {
        public int Sno { get; set; }   //学生编号
        public string  Sname { get; set; } //学生姓名
        public string Ssex { get; set; } //性别
        public string Sbirthday { get; set; } //时间
        public int ClassName { get; set; }  //教室名称
    }
    class SC   //创建Model类：SC。
    {
        public int SNO { get; set; }    //学生
        public string CNO { get; set; }    //课程
        public double Score { get; set; }  //成绩

    }
    class Course   //创建Model类：Course 。
    {
        public string CNO { get; set; }  //课程编号
        public string Cname { get; set; }  //课程名称
        public int TNO { get; set; }  //教师编号
    }
    class Teacher   //创建Model类：Teacher。
    {
        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; } //系别


    }

}

2019-11-07