一、linq高级查
1.模糊查(包含)
1 public List<User> Select(string name)
2 {
3 return con.User.Where(r =>r.UserName.Contains(name)).ToList();
4 }
2.查开头
public List<User> Select(string name)
{
return con.User.Where(r => r.UserName.StartsWith(name)).ToList();
}
3.查结尾
public List<User> Select(string name)
{
return con.User.Where(r => r.UserName.EndsWith(name)).ToList();
}
4.个数
clist.Count()
5.最大值:Max(r => r.price);
public decimal? SelectMax()
{
return con.car.Max(r => r.price);
}
6.最小值:Min(r => r.price);
public decimal? SelectMin()
{
return con.car.Min(r => r.price);
}
7.平均值:Average(r => r.price);
public decimal? SelectAvg()
{
return con.car.Average(r => r.price);
}
8.求和:Sum(r => r.price);
public decimal? SelectSum() { return con.car.Sum(r => r.price); }
9.排序:
1、OrderBy 按升序对值进行排序。
2、OrderByDescending 按降序对值进行排序
3、ThenBy 按升序执行次要排序。
4、ThenByDescending 按降序执行次要排序。
(1)升序:OrderBy(r => r.price)
1 public List<car> orderBy()
2 {
3 return con.car.OrderBy(r => r.price).ToList();
4 }
(2)降序:OrderByDescending(r => r.price)
public List<car> orderBy()
{ return con.car.OrderByDescending(r => r.oil).ToList();
}
二、分页与组合查询
1.分页
1 public List<User> Select(int count, int number)
2 {
3 return con.User.Skip(count * (number - 1)).Take(count).ToList();
4 }
public int MaxPageNumber()
{
List<User> list = new UserData().Select();
double nu = list.Count / (Pagecount * 1.0);
int num = Convert.ToInt32(Math.Ceiling(nu));
return num;
}//总页数
void btn_next_Click(object sender, EventArgs e)
{
int NowNumber = Convert.ToInt32(Label2.Text) + 1;
if (NowNumber > MaxPageNumber())
{
return;
}
Repeater1.DataSource = new UserData().Select(Pagecount, NowNumber);
Repeater1.DataBind(); Label2.Text = NowNumber.ToString();
DropDownList2.SelectedValue = NowNumber.ToString(); }//下一页
2.组合查询
1 void Button2_Click(object sender, EventArgs e)
2 {
3 using(WebDataContext con=new WebDataContext()){
4 var All = con.User.AsEnumerable();
5 if (TextBox1.Text.Trim().Length > 0)
6 {
7 var namelist = con.User.Where(r => r.UserName.Contains(TextBox1.Text.Trim()));
8
9 All = All.Intersect(namelist);
10 }
11
12 if (tb_sex.Text.Trim() == "男" || tb_sex.Text.Trim() == "女")
13 {
14 var sexlist = con.User.Where(r =>Convert.ToBoolean(r.Sex)==(tb_sex.Text.Trim()=="男"?true:false));
15
16 All = All.Intersect(sexlist);
17 }
18
19 if (tb_age.Text.Trim().Length > 0)
20 {
21 int nowyear = DateTime.Now.Year;
22 try {
23 int age= Convert.ToInt32(tb_age.Text.Trim());
24 int g = nowyear- age;
25 DateTime d = Convert.ToDateTime(g.ToString() + "-1-1");
26 if (DropDownList3.SelectedValue == ">=")
27 {
28 var agelist = con.User.Where(r => Convert.ToDateTime(r.Birthday) >= d);
29 All = All.Intersect(agelist);
30
31 }
32 else
33 {
34 DateTime dd = Convert.ToDateTime(g.ToString() + "-12-31");
35 var agelist = con.User.Where(r => Convert.ToDateTime(r.Birthday) <= dd);
36 All = All.Intersect(agelist);
37
38 }
39 }
40 catch{
41 }
42 }
43
44
45 Repeater1.DataSource = All;
46 Repeater1.DataBind();
47 }
48 }