Webform_LINQ高级查

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