Linq（高级查询）

一、条件查

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 = 3;

    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[1].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 = "1";
                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 == "1")
        {
            return;
        }
        using (StudentDataContext con = new StudentDataContext())
        {
            int a = Convert.ToInt32(Label2.Text) - 1;
            Repeater1.DataSource = Data(con).Skip((a - 1) * 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) + 1;
            Repeater1.DataSource = Data(con).Skip((a - 1) * 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) - 1) * 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 = "1";
            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 = "1";
            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=" 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 = "001", name = "张三" });
        tlist1.Add(new test1() { code = "002", name = "李四" });
        tlist1.Add(new test1() { code = "003", name = "王五" });

        tlist2.Add(new test1() { code = "001", name = "张三" });
        tlist2.Add(new test1() { code = "004", name = "李四" });
        tlist2.Add(new test1() { code = "005", name = "王五" });

        tlist3.Add(new test1() { code = "001", name = "张三" });
        tlist3.Add(new test1() { code = "006", name = "李四" });
        tlist3.Add(new test1() { code = "003", name = "王五" });

        List<test1> ttlist = tlist1.Intersect(tlist2).Intersect(tlist3).ToList();

    }
}

public class test1
{
    public string code { get; set; }
    public string name { get; set; }
}