linq小结

普通查询

var query = from s in context.Student select s;

//投影列
var query = from s in context.Student select new { s.Id, s.StudentName };

//起别名
var query = from a in context.Student select new { 姓名 = a.StudentName, 性别 = a.Sex };

排序

                //单字段排序
                var query = from s in context.Student orderby s.Id ascending select s;
                // 多字段排序
                var query2 = (from s in context.Student
                              orderby s.Id ascending, s.StudentName descending
                              select s);// descending:降序

分组

                var query = from s in context.Student group s by s.ClassId;  //该行会报错'Client side GroupBy is not supported.'，需要先取出数据再进行分组
                var query = context.Student.ToList().GroupBy(x => x.ClassId);
                foreach (var item in query)
                {
                    foreach (var data in item)
                    {
                        Console.WriteLine(data.StudentName + "-----" + item.Key);

                    }
                }
//-----------------------------------------------------------------------------------------------------------------------------
                var query = from s in context.Student
                            group s by s.ClassId into gs
                            select new
                            {
                                classId = gs.Key, // ClassId: 分组的键,
                                count = gs.Count()
                            };
                foreach (var g in query)
                {
                    Console.WriteLine($"班级：{g.classId},数量：{g.count}");
                }

模糊查询

                //模糊查询
                // like '%任%'
                var query = from s in context.Student where s.StudentName.Contains("任") orderby s.Id ascending select s;
                // like '任%'
                var query2 = from s in context.Student where s.StudentName.StartsWith("任") orderby s.Id ascending select s;
                // like '%任'
                var query3 = from s in context.Student where s.StudentName.EndsWith("任") orderby s.Id ascending select s;

分页

                //分页 skip((pageIndex-1)*pageSize).Take(pageSize)
                var query = from s in context.Student.Skip((1 - 1) * 3).Take(3) select s;

连接查询

                //内连接

                //select 
                //Student.StudentName,Student.Birthday,Student.Sex,Score.Course,Score.Degree
                //from Student
                //inner join Score
                //on Student.Id = Score.StudentId

                var query = from s in context.Student
                            join sc in context.Score
                            on s.Id equals sc.StudentId
                            select
                            new
                            {
                                s.Id,
                                s.StudentName,
                                s.Birthday,
                                s.Sex,
                                s.ClassId,
                                sc.Course,
                                sc.Degree
                            };


                //左连接
                var query2 = from s in context.Student
                             join sc in context.Score on s.Id equals sc.StudentId into temp
                             from t in temp.DefaultIfEmpty()
                             select new
                             {
                                 s.Id,
                                 s.StudentName,
                                 s.Birthday,
                                 s.Sex,
                                 s.ClassId,
                                 t.Course,
                                 t.Degree
                             };