一、条件查
public List<Users> ss(string ids)
{
List<Users> ulist=new List<Users>();
声明使用Linq类
using (StudentDataContext con = new StudentDataContext())
{ 创建条件
ulist =con.Users.Where(r=>r.Ids.ToString()==ids).ToList();
}
return ulist;
}
二、高级查
1、以xx开头：
public List<Users> ss(string ids)
{
List<Users> ulist=new List<Users>();
using (StudentDataContext con = new StudentDataContext())
{ //开头方法（“字符串”）
ulist = con.Users.Where(r => r.Username.StartsWith("赵")).ToList();
}
return ulist;
}
2、以xx结尾：
ulist = con.Users.Where(r => r.Username.EndsWith("赵")).ToList();
3、包含xx:
ulist = con.Users.Where(r => r.Username.Contains("赵")).ToList();
4、个数：
（1）有变量接收：ulist.Count;或con.users.Count()
（2）无接受：ulist.Count();
5、最大值：con.Users.Max(r=>r.Ids)
6、最小值：con.Users.Min(r=>r.Ids)
7、平均值：con.Users.Average(r=>r.Ids)
8、求和：con.Users.Sum(r=>r.Ids)
9、排序：
（1）、OrderBy 按升序对值进行排序。 con.Users.OrderBy(r=>r.Ids)
（2）、OrderByDescending 按降序对值进行排序
（3）、ThenBy 按升序执行次要排序。
（4）、ThenByDescending 按降序执行次要排序。
10、分页查询
总共跳转条数 单次显示条数
con.Users.Skip((a - 1) * page).Take(page);
11、组合查询
组合查询是指将前条件查询结果基础上对后集合进行查询
例：
int page = ;
protected void Page_Load(object sender, EventArgs e)
{
string id = Request["id"];
if (!IsPostBack)
{
using (StudentDataContext con = new StudentDataContext())
{
DropDownList1.DataSource = con.Nation.ToList();
DropDownList1.DataTextField = "NationName";
DropDownList1.DataValueField = "NationCode";
DropDownList1.DataBind();
Users u = con.Users.Where(w => w.Ids.ToString() == id).FirstOrDefault();
if (u == null)
{
Label4.Text = "";
}
else
{
TextBox1.Text = u.Username;
TextBox2.Text = u.Password;
TextBox5.Text = u.Password;
TextBox3.Text = u.Nikename;
if (u.Sex == false)
{
RadioButtonList1.Items[].Selected = true;
}
TextBox4.Text = Convert.ToDateTime(u.Birthday).ToString("yyyy年MM月dd日");
foreach (ListItem li in dr_nation.Items)
{
if (li.Value == u.Nation)
li.Selected = true;
}
}
}
}
if (Request.Cookies["name"] != null)
{
string a = HttpUtility.UrlDecode(Request.Cookies["name"].Value, System.Text.Encoding.GetEncoding("utf-8"));
Label1.Text ="你好，"+ a;
}
if (!IsPostBack)
{
using (StudentDataContext con = new StudentDataContext())
{
dr_nation.DataSource = con.Nation.ToList();
dr_nation.DataTextField = "NationName";
dr_nation.DataValueField = "NationCode";
dr_nation.DataBind();
Repeater1.DataSource =con.Users.ToList().Take(page);
Repeater1.DataBind();
Label2.Text = "";
Label3.Text = (Math.Ceiling(Convert.ToDecimal(con.Users.ToList().Count) / page)).ToString();
}
}
Button1.Click += Button1_Click;
bt_first.Click += bt_first_Click;
bt_last.Click += bt_last_Click;
bt_nex.Click += bt_nex_Click;
bt_up.Click += bt_up_Click;
}
void bt_up_Click(object sender, EventArgs e)
{
if (Label2.Text == "")
{
return;
}
using (StudentDataContext con = new StudentDataContext())
{
int a = Convert.ToInt32(Label2.Text) - ;
Repeater1.DataSource = Data(con).Skip((a - ) * page).Take(page);
Repeater1.DataBind();
Label2.Text = Label3.Text;
}
}
void bt_nex_Click(object sender, EventArgs e)
{
using (StudentDataContext con = new StudentDataContext())
{
if (Label2.Text == (Math.Ceiling(Convert.ToDecimal(Data(con).Count) / page)).ToString())
{
return;
}
int a = Convert.ToInt32(Label2.Text) + ;
Repeater1.DataSource = Data(con).Skip((a - ) * page).Take(page);
Repeater1.DataBind();
Label2.Text = Label3.Text;
}
}
void bt_last_Click(object sender, EventArgs e)
{
using (StudentDataContext con = new StudentDataContext())
{
Repeater1.DataSource = Data(con).Skip((Convert.ToInt32(Label3.Text) - ) * page).Take(page);
Repeater1.DataBind();
Label2.Text = Label3.Text;
}
}
void bt_first_Click(object sender, EventArgs e)
{
using (StudentDataContext con = new StudentDataContext())
{
Repeater1.DataSource = Data(con).Take(page);
Repeater1.DataBind();
Label2.Text = "";
Label3.Text = Data(con).Count.ToString();
}
}
void Button1_Click(object sender, EventArgs e)
{
using (StudentDataContext con = new StudentDataContext())
{
Repeater1.DataSource = Data(con).Take(page);
Repeater1.DataBind();
Label2.Text = "";
Label3.Text = Data(con).Count.ToString();
}
}
public List<Users> Data(StudentDataContext con)
{
List<Users> ulist = new List<Users>();
ulist=con.Users.ToList();
if (name.Text.Trim() != "")
{
ulist = ulist.Where(w => w.Nikename.Contains(name.Text.Trim())).ToList();
}
if(Ra_sex.SelectedValue!="null")
{
ulist = ulist.Where(w => w.Sex == Convert.ToBoolean(Ra_sex.SelectedValue)).ToList();
}
ulist = ulist.Where(w => w.Nation.ToString() == dr_nation.SelectedValue).ToList();
Label3.Text = (Math.Ceiling(Convert.ToDecimal(ulist.Count) / page)).ToString();
con.Connection.Close();
return ulist;
}
组合查询后台
<asp:Label ID="Label1" runat="server" Text=""></asp:Label><br />
昵称：<asp:TextBox ID="name" runat="server"></asp:TextBox>
性别：<asp:RadioButtonList ID="Ra_sex" RepeatLayout="Flow" RepeatDirection="Horizontal" runat="server">
<asp:ListItem Text="全部" Value="null" Selected="True"></asp:ListItem>
<asp:ListItem Text="男" Value="true"></asp:ListItem>
<asp:ListItem Text="女" Value="false"></asp:ListItem>
</asp:RadioButtonList>
民族：<asp:DropDownList ID="dr_nation" runat="server"></asp:DropDownList>
<asp:Button ID="Button1" runat="server" Text="查询" />
<table style="width: 100%; background-color: blue; text-align: center">
<tr>
<td>编号</td>
<td>用户名</td>
<td>密码</td>
<td>昵称</td>
<td>性别</td>
<td>生日</td>
<td>民族</td>
<td>操作</td>
</tr> <asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<tr style="background-color: white;">
<td><%#Eval("Ids") %></td>
<td><%#Eval("Username") %></td>
<td><%#Eval("Password") %></td>
<td><%#Eval("Nikename") %></td>
<td><%#Eval("sexstr") %></td>
<td><%#Eval("birthdaystr") %></td>
<td><%#Eval("nationstr") %></td>
<td>
<div id="updatebtn" style="float: left;margin-left:10px">编辑</div>
<div id="deletebtn" style="float: left;margin-left:10px">删除</div>
</td>
</tr>
</ItemTemplate> </asp:Repeater> </table>
当前第[<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>]页
共[<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>]页
<asp:Button ID="bt_first" runat="server" Text="首页" />
<asp:Button ID="bt_up" runat="server" Text="上一页" />
<asp:Button ID="bt_nex" runat="server" Text="下一页" />
<asp:Button ID="bt_last" runat="server" Text="尾页" />
组合查询前台
12、集合交集
结构：集合1.Intersect(集合2).Intersect（集合3）.ToList();
例：
public partial class Default2 : System.Web.UI.Page
{
List<test1> tlist1 = new List<test1>();
List<test1> tlist2 = new List<test1>();
List<test1> tlist3 = new List<test1>(); protected void Page_Load(object sender, EventArgs e)
{
tlist1.Add(new test1() { code = "", name = "张三" });
tlist1.Add(new test1() { code = "", name = "李四" });
tlist1.Add(new test1() { code = "", name = "王五" }); tlist2.Add(new test1() { code = "", name = "张三" });
tlist2.Add(new test1() { code = "", name = "李四" });
tlist2.Add(new test1() { code = "", name = "王五" }); tlist3.Add(new test1() { code = "", name = "张三" });
tlist3.Add(new test1() { code = "", name = "李四" });
tlist3.Add(new test1() { code = "", name = "王五" }); List<test1> ttlist = tlist1.Intersect(tlist2).Intersect(tlist3).ToList(); }
} public class test1
{
public string code { get; set; }
public string name { get; set; }
}
集合交集