1.查询：
Linq:
var ss = from r in db.student select r;
Lamda:
var ss = db.student;
SQL:
select * from student
2.条件查询：
Linq
var ss = from r in db.student where r.s_number>10 select r;
Lamda
var ss = db.student.Where(r => r.s_number > 10);
SQL:
select * from student where number>10
3.简单的计算：
Linq：
//最大的s_number
var ss = (from r in db.student select r).Max(r=>r.s_number);
//最小的s_number
var ss = (from r in db.student select r).Min(r => r.s_number);
//student数据总数
var ss = (from r in db.student select r).Count();
//s_number的和
var ss = (from r in db.student select r).Sum(r => r.s_number);
Lamda：
//最大的s_number
var ss = db.student.Max(p=>p.s_number);
//最小的s_number
var ss = db.student.Min(p => p.s_number);
//student数据总数
var ss = db.student.Count();
//s_number的和
var ss = db.student.Sum(p => p.s_number);
SQL:
//最大的s_number
select max(s_number) from student
//最小的s_number
select min(s_number) from student
// student数据总数
select count(1) from student
//s_number的和
select sum(s_number) from student
4.排序order by asc /desc(正序/倒序)
默认按照 升序 进行排序
Linq:
var ss = from r in db.student
where r.s_number > 10
orderby r.s_number ascending select r; //正序
var ss = from r in db.student
where r.s_number > 10
orderby r.s_number descending select r; //倒序
Lamda:
//正序 var ss = db.student.OrderBy(s => s.s_number).Where(s => s.s_number > 10); //倒序 var ss = db.student. OrderByDescending (s => s.s_number).Where(s => s.s_number > 10);
SQL:
//正序 select * from student where s_number>10 order by s_number asc //倒序 select * from student where s_number>10 order by s_number desc
5.top（3）取前三条
Linq:
List<student> ss = (from r in db.student select r).Take(3).ToList();
Lamda:
List<student> ss = db.student.Take(3).ToList();
SQL:
select top(3)* from student
6.分页查询( skip()跳过数据条数，Take()获取数据条数 )
Linq：
List<student> ss = (from r in db.student
where r.s_number > 10
orderby r.s_number descending
select r).Skip(9).Take(10).ToList();//跳过9条数据，取第10条数据及以后的数据共10条
Lamda：
List<student> ss = db.student.OrderByDescending(r => r.s_number).Where(r=>r.s_number>10).Skip(9).Take(10).ToList();
//跳过9条数据，取第10条数据及以后的数据共10条
SQL:
select * from (select row_number() over(order by s_number asc) as rownumber,*
from student) as t where rownumber>9 and rownumber<=20
7.模糊查询,like’% %’
Linq:
List<student> ss = (from r in db.student where r.s_name.Contains("李") select r).ToList();
Lamda:
List<student> ss = db.student.Where(r => r.s_name.Contains("李")).ToList();
SQL:
select * from student where s_name like '%李%'
8.分组group by（聚合函数查询）
Linq:
var ss = from r in db.student
orderby r.s_number descending
group r by r.s_number into n
select new
{
n.Key,
Sum= n.Sum(r => r.s_number),
Max = n.Max(r => r.s_number),
Min = n.Min(r => r.s_number)
};
Lamda：
var ss = db.student.OrderByDescending(s => s.s_number).GroupBy(p => p.s_number);
SQL:
select s_name,name_min=MIN(s_number),name_max=MAX(s_number) from student group by s_name
9.连接查询（多表查询）
INNER JOIN 与 JOIN 是相同的
只能使用 equals 不能用 =,==,等于 等表示。
Linq：
var ss = from r in db.student
join w in db.grade on r.s_number equals w.g_number
orderby r.s_number descending //降序
select r;
Lamda：
（1）
List<grade> ss = db.login.Join(db.grade, l => l.s_number, g => g.s_number, (l, g) => new grade
{
s_number = l.s_number,
g_number=g.s_number
}) .ToList();
（2）
List<login> ss = db.login.Join(db.grade, l => l.s_number, g => g.s_number, (l, g) => l).OrderByDescending(l=>l.s_number).ToList() ;
SQL:
select s. * from student as s join grade as g on s.c_number =g.s_number order by s.c_number desc
10.SQL中的in
Linq:
var ss = from s in db.student
where (new int?[] { 9, 10, 11 }).Contains(s.s_number)
select s;
Lamda：
List<int> wherelist = new List<int>() { 9,10,11};
var s1 = db.student.Where(s => wherelist.Contains(s.s_number));
SQL:
select * from student where s_number in(9,10,11) 等同于: select * from student where s_number =9 or s_number=10 or s_number=11
有错误会及时修改。