Webform--LinQ 分页组合查询
一、linq高级查
1.模糊查（字符串包含）
public List<User> Select(string name) { return con.User.Where(r => r.UserName.Contains(name)).ToList(); }
2.查开头（StartsWith,以XX开头）
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)
public List<car> orderBy()
{
return con.car.OrderBy(r => r.price).ToList();
}
（2）降序：OrderByDescending(r => r.price)
public List<car> orderBy()
{
return con.car.OrderByDescending(r => r.oil).ToList();
}
10.
- ListResult = ListA.Distinct().ToList();//去重
- ListResult = ListA.Except(ListB).ToList();//差集
- ListResult= ListA.Union(ListB).ToList(); //并集
- ListResult = ListA.Intersect(ListB).ToList();//交集
二、分页与组合查询
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title></title> <style> .image { width: 30px; height: 30px; position: relative; float: left; margin-top: 5px; } .caozuo { position: relative; float: left; margin-left: 50px; margin-top: 12px; width: 50px; height: 30px; } </style> </head> <body> <form id="form1" runat="server"> <div> 昵称：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 性别：<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 年龄：<select id="DropDownList1" runat="server"> <option>>=</option> <option><</option> </select> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="查询" /><br /> <br /> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table style="width: 100%; text-align: center; background-image: url(images/2.jpg);"> <tr style="color: red; height: 45px; font-size: 24px;"> <td>编号</td> <td>用户名</td> <td>密码</td> <td>昵称</td> <td>性别</td> <td>生日</td> <td>民族</td> <td>班级</td> <td style="text-align: left; width: 180px;"> <div class="caozuo">操作</div> <div class="image"> <a href="Insert.aspx" target="_blank"> <img src="images/4.png" height="30" width="30" /></a> </div> </td> </HeaderTemplate> <ItemTemplate> <tr class="t" style="background-color: white;"> <td><%#Eval("ids") %></td> <td><%#Eval("UserName") %></td> <td><%#Eval("PassWord") %></td> <td><%#Eval("NickName") %></td> <td> <img src="<%#Eval("SexStr") %>" /> </td> <td><%#Eval("Birthday","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("NationName") %></td> <td><%#Eval("ClassName") %></td> <td> <a href="Delete.aspx?i=<%#Eval("UserName") %>">删除</a> <a href="Update.aspx?i=<%#Eval("UserName") %>">修改</a> </td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> <div style="width: 800px; height: 20px; margin: auto"> 当前第【<asp:Label ID="Label" runat="server" Text="Label"></asp:Label>】页 共【<asp:Label ID="Labe2" runat="server" Text="Label"></asp:Label>】页 <asp:LinkButton ID="btn_prev" runat="server" CssClass="wenzi">上一页</asp:LinkButton> <asp:LinkButton ID="btn_next" runat="server" CssClass="wenzi">下一页</asp:LinkButton> </div> </div> </form> </body> </html> <script> var ts = document.getElementsByClassName("t"); for (var i = 0; i < ts.length; i++) { ts[i].onmouseover = function () { if (this.style.backgroundColor == "white") this.style.backgroundColor = "yellow"; }; ts[i].onmouseout = function () { if (this.style.backgroundColor == "yellow") { this.style.backgroundColor = "white"; } }; ts[i].onclick = function () { for (var j = 0; j < ts.length; j++) { ts[j].style.backgroundColor = "white"; } var colorArr = ["0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F"]; var colorVal = "#"; for (i = 0; i < 6; i++) { colorVal += colorArr[Math.round(15 * Math.random())]; } alert(colorVal) this.style.backgroundColor= colorVal; }; } </script>
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { int number = 1; int count=5; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Label.Text = "1"; using (Data0928DataContext con = new Data0928DataContext()) { double zong=Math.Ceiling(con.Users.ToList().Count/(count*1.0)); Labe2.Text=zong.ToString(); Repeater1.DataSource= con.Users.Skip(count * (number - 1)).Take(count).ToList(); Repeater1.DataBind(); } } Button1.Click += Button1_Click; btn_next.Click += btn_next_Click; btn_prev.Click += btn_prev_Click; } //上一页 void btn_prev_Click(object sender, EventArgs e) { int nu = Convert.ToInt32(Label.Text) - 1; Label.Text = nu.ToString(); if (nu < 1) { nu = 1; Label.Text = "1"; return;} chaxun(); } //下一页 void btn_next_Click(object sender, EventArgs e) { int nu = Convert.ToInt32(Label.Text) + 1; Label.Text = nu.ToString(); if (nu > Convert.ToInt32(Labe2.Text)) { nu = Convert.ToInt32(Labe2.Text.Trim()); Label.Text = Labe2.Text.Trim(); return; } chaxun(); } //查询 void Button1_Click(object sender, EventArgs e) { chaxun(); } //查询方法 private void chaxun() { using (Data0928DataContext con = new Data0928DataContext()) { var All = con.Users.AsQueryable();//AsEnumerale也是一个集合，不会占用系统空间，相当于只是把查询字符串给拼接起来，准备好，当执行ToList时才会进行查询 if (TextBox1.Text.Trim().Length > 0)//说明这里填写了内容 { var namelist = con.Users.Where(r => r.NickName.Contains(TextBox1.Text.Trim())); All = All.Intersect(namelist); } if (TextBox2.Text.Trim() == "男" || TextBox2.Text.Trim() == "女") { var sexlist = con.Users.Where(r => Convert.ToBoolean(r.Sex) == (TextBox2.Text.Trim() == "男" ? true : false)); All = All.Intersect(sexlist); } if (TextBox3.Text.Trim().Length > 0) { int nowyear = DateTime.Now.Year; try { int age = Convert.ToInt32(TextBox3.Text.Trim()); int g = nowyear - age; DateTime d = Convert.ToDateTime(g.ToString() + "-1-1"); if (DropDownList1.SelectedIndex == 1) { var agelist = con.Users.Where(r => Convert.ToDateTime(r.Birthday) >= d); All = All.Intersect(agelist); } else { DateTime dd = Convert.ToDateTime(g.ToString() + "-12-31"); var agelist = con.Users.Where(r => Convert.ToDateTime(r.Birthday) <= dd); All = All.Intersect(agelist); } } catch { } } double zong = Math.Ceiling(All.ToList().Count / (count * 1.0));//取上限 Labe2.Text = zong.ToString(); Repeater1.DataSource = All.Skip((Convert.ToInt32(Label.Text.Trim())-1)*count).Take(count); Repeater1.DataBind(); } } }
