一、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 }