linq的简单查询 和 组合查询

以Car表和Brand表为例，其中Car表的Brand是Brand表的Brandcode。

（1）建立两表的linq（一定要做好主外键关系，），创建之后不用修改，如要添加，另建文件。

（2）Car表的属性扩展（注意partial）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Car 的摘要说明
/// </summary>
public partial class Car
{
    

    public string BrandStr
    {

        get {
            return this.Brand1.Brand_Name;
        
        }
    }

}
View Code

（3）Car表和Brand表的封装方法（模糊查，开头，结尾，个数，最大值，最小值，平均值，求和，升序，降序，分页，组合查询都在CarData里面）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// BrandData 的摘要说明
/// </summary>
public class BrandData
{
    mydbDataContext cnn = null;
    public BrandData()
    {
        cnn = new mydbDataContext();
    }

    public List<Brand> quan()
    {
        return cnn.Brand.ToList();
    
    }

}
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// CarData 的摘要说明
/// </summary>
public class CarData
{
    mydbDataContext cnn = null;
    public CarData()
    {
        cnn = new mydbDataContext();
    }
    /// <summary>
    /// 查询全部信息
    /// </summary>
    /// <returns>泛型集合</returns>
    public List<Car> quan()
    {
        return cnn.Car.ToList();
    }
    /// <summary>
    /// 根据名字模糊查询
    /// </summary>
    /// <param name="name">名称中的某一段字</param>
    /// <returns>泛型集合</returns>
    public List<Car> selectname(string name)
    {
        return cnn.Car.Where(r => r.Name.Contains(name)).ToList();
    }
  /// <summary>
    /// 查询name以n开头的所有数据
  /// </summary>
    /// <param name="n">name以n开头的字</param>
  /// <returns></returns>
    public List<Car> selectstartname(string n)
    {

        return cnn.Car.Where(r => r.Name.StartsWith(n)).ToList();//r.name可以换成别的列
    }
    /// <summary>
    /// 查询name以n结束的所有数据
    /// </summary>
    /// <param name="n">name以n结束的字</param>
    /// <returns></returns>
    public List<Car> selectendname(string n)
    {

        return cnn.Car.Where(r => r.Name.EndsWith(n)).ToList();//r.name可以换成别的列
    }
    /// <summary>
    /// 查询价格最高的那个数
    /// </summary>
    /// <returns>最大数</returns>
    public decimal? selectpricemax()
    {
      return   cnn.Car.Max(r => r.Price);
    }
    /// <summary>
    /// 查询价格最小的那个数
    /// </summary>
    /// <returns>最小数</returns>
    public decimal? selectpricemin()
    {
        return cnn.Car.Min(r => r.Price);
    }
    /// <summary>
    /// 查询价格的平均数
    /// </summary>
    /// <returns>平均数</returns>
    public decimal? selectpriceavg()
    {
        return cnn.Car.Average(r=>r.Price);
    }
    /// <summary>
    /// 查询价格的总数
    /// </summary>
    /// <returns>价格总数</returns>
    public decimal? selectpricesum()
    {
        return cnn.Car.Sum(r=>r.Price);
    }
    /// <summary>
    /// 按照价格升序进行排列的集合
    /// </summary>
    /// <returns>集合</returns>
    public List<Car> order()
    {
        return cnn.Car.OrderBy(r=>r.Price).ToList();//按照价格升序
    }
     
    /// <summary>
    /// 按照价格降序进行排列的集合
    /// </summary>
    /// <returns>集合</returns>
    public List<Car> descorder()
    {
        return cnn.Car.OrderByDescending(r => r.Price).ToList();
    
    }
    /// <summary>
    /// 分页显示
    /// </summary>
    /// <param name="pagecount">每一页的页数</param>
    /// <param name="num">第几页</param>
    /// <returns>集合</returns>
    public List<Car> fenye(int pagecount, int num)
    {
        return cnn.Car.Skip(pagecount * (num - 1)).Take(pagecount).ToList();//cnn.Car表示所有的数据
    }
    /// <summary>
    /// 组合分页查询
    /// </summary>
    /// <param name="clist">所需要分页的集合</param>
    /// <param name="pagecount">每一页的页数</param>
    /// <param name="num">第几页</param>
    /// <returns></returns>
    public List<Car> zufen(List<Car> clist, int pagecount, int num)
    {
      return   clist.Skip(pagecount * (num - 1)).Take(pagecount).ToList();//clist表示所需要分页的集合
    
    }
}
View Code

（4）页面设计

<%@ 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>
</head>
<body>
    <form id="form1" runat="server">
        名称<asp:TextBox ID="name_text" runat="server"></asp:TextBox>
        品牌<asp:DropDownList ID="brand_dd" runat="server"></asp:DropDownList>
        价格<asp:DropDownList ID="pri_dd" runat="server">
            <asp:ListItem>&gt;=</asp:ListItem>
            <asp:ListItem>&lt;=</asp:ListItem>
        </asp:DropDownList><asp:TextBox ID="price_text" runat="server"></asp:TextBox>
        <asp:Button ID="enter_btn" runat="server" Text="确定" />
        <br />
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
        <asp:Repeater ID="Repeater1" runat="server">
            <HeaderTemplate>
                <table style="100%; background-color:blue;">
                <tr>
                     <td>序号</td>
                    <td>名称</td>
                    <td>品牌</td>
                    <td>时间</td>
                    <td>油耗</td>
                    <td>马力</td>
                    <td>价格</td>
                    <td>照片</td>
                </tr>
            </HeaderTemplate>
            <ItemTemplate>
                 <tr style="background-color:gray" class="a">
                     <td><%#Container.ItemIndex+1 %></td>
                    <td><%#Eval("Name") %></td>
                    <td><%#Eval("BrandStr") %></td>
                    <td><%#Eval("Time","{0:yyyy年MM月dd日}") %></td>
                    <td><%#Eval("Oil") %></td>
                    <td><%#Eval("Powers") %></td>
                    <td><%#Eval("Price") %></td>
                    <td><%#Eval("Pic") %></td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table>
            </FooterTemplate>
        </asp:Repeater>
        当前第【<asp:Label ID="numLabel" runat="server" Text=""></asp:Label>】页，共【<asp:Label ID="sumLabel" runat="server" Text=""></asp:Label>】页 &nbsp;&nbsp;&nbsp;
        <asp:LinkButton ID="previewButton" runat="server">上一页</asp:LinkButton>&nbsp;
        <asp:LinkButton ID="nextButton" runat="server">下一页</asp:LinkButton> &nbsp;&nbsp;&nbsp;
        跳转到第<asp:DropDownList ID="page_dd" runat="server" AutoPostBack="true"></asp:DropDownList>页
    </form>
</body>
</html>
<script>
    var all = new Array();
    all =document.getElementsByClassName("a");
    
    for (var i = 0; i < all.length; i++)
    {
        var old;
        all[i].onmouseover = function ()
        {
            old = this.style.backgroundColor;
            this.style.backgroundColor = "yellow";
        }
        all[i].onmouseout = function () {
           
            this.style.backgroundColor = old;
        }
    }


</script>
View Code

（5）C#端代码

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 pagecount = 3;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            List<Car> list = new CarData().quan();
            Repeater1.DataSource = new CarData().fenye(pagecount, 1); ;
            Repeater1.DataBind();


            List<Brand> blist = new BrandData().quan();
            brand_dd.Items.Add(new ListItem("全部","null"));
            for(int i=0;i<blist.Count;i++)
            {
            brand_dd.Items.Add(new ListItem(blist[i].Brand_Name,blist[i].Brand_Code));
            }

            numLabel.Text = "1";
            sumLabel.Text = pagemax(list, pagecount).ToString();
            for (int i = 1; i <= int.Parse(sumLabel.Text); i++)
            {
                page_dd.Items.Add(new ListItem(i.ToString(),i.ToString()));
            }
        }
        enter_btn.Click += enter_btn_Click;//查询按钮点击事件
        nextButton.Click += nextButton_Click;//下一页的点击事件
        previewButton.Click += previewButton_Click;//上一页的事件
        page_dd.SelectedIndexChanged += page_dd_SelectedIndexChanged;//跳转到第几页事件
    }

    void page_dd_SelectedIndexChanged(object sender, EventArgs e)//跳转到第几页事件
    {
        int c = int.Parse(page_dd.SelectedValue);
        using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法
        {
            int aa = int.Parse(page_dd.SelectedValue);
            numLabel.Text = aa.ToString();//第几页中的页数
            var all = cnn.Car.AsEnumerable();//相当于拼接sql语句
            if (name_text.Text.Trim().Length > 0)
            {
                var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim()));
                all = all.Intersect(nlist);//取all集合和nlist集合的公共部分
            }
            if (brand_dd.SelectedValue == "null")
            { }
            else
            {

                var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue);
                all = all.Intersect(blist);
            }
            if (price_text.Text.Trim().Length > 0)
            {

                if (pri_dd.SelectedIndex == 0)
                {

                    var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
                else
                {
                    var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
            }
            Repeater1.DataSource = new CarData().zufen(all.ToList(), pagecount,aa);
            Repeater1.DataBind();


        }
        
    }

    void previewButton_Click(object sender, EventArgs e)//上一页的事件
    {
        using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法
        {
            int a = int.Parse(numLabel.Text) -1;//哪一页的页数
            if (a <= 0)//判断有没有到最后一页
            {
                return;
            }
            numLabel.Text = a.ToString();//第几页中的页数
            var all = cnn.Car.AsEnumerable();//相当于拼接sql语句
            if (name_text.Text.Trim().Length > 0)
            {
                var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim()));
                all = all.Intersect(nlist);//取all集合和nlist集合的公共部分
            }
            if (brand_dd.SelectedValue == "null")
            { }
            else
            {

                var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue);
                all = all.Intersect(blist);
            }
            if (price_text.Text.Trim().Length > 0)
            {

                if (pri_dd.SelectedIndex == 0)
                {

                    var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
                else
                {
                    var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
            }
            Repeater1.DataSource = new CarData().zufen(all.ToList(), pagecount, a);
            Repeater1.DataBind();
        }
    }

    void nextButton_Click(object sender, EventArgs e)//下一页的点击事件
    {
        using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法
        {
            int a = int.Parse(numLabel.Text) + 1;//哪一页的页数
            if (a > int.Parse(sumLabel.Text))//判断有没有到最后一页
            {
                return;
            }
            numLabel.Text = a.ToString();//第几页中的页数
            var all = cnn.Car.AsEnumerable();//相当于拼接sql语句
            if (name_text.Text.Trim().Length > 0)
            {
                var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim()));
                all = all.Intersect(nlist);//取all集合和nlist集合的公共部分
            }
            if (brand_dd.SelectedValue == "null")
            { }
            else
            {

                var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue);
                all = all.Intersect(blist);
            }
            if (price_text.Text.Trim().Length > 0)
            {

                if (pri_dd.SelectedIndex == 0)
                {

                    var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
                else
                {
                    var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
            }
            Repeater1.DataSource = new CarData().zufen(all.ToList(),pagecount,a);
            Repeater1.DataBind();
        

        }

    }

    void enter_btn_Click(object sender, EventArgs e)//查询按钮点击事件
    {
        using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法
        {
            page_dd.Items.Clear();
            var all = cnn.Car.AsEnumerable();//相当于拼接sql语句
            if (name_text.Text.Trim().Length>0)
            {
                var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim()));
                all = all.Intersect(nlist);
            }
            if (brand_dd.SelectedValue == "null")
            { }
            else
            {

                var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue);
                all = all.Intersect(blist);
            }
            if (price_text.Text.Trim().Length > 0)
            {
                
                if (pri_dd.SelectedIndex == 0)
                {

                    var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
                else
                {
                    var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim()));
                    all = all.Intersect(plist);
                }
            }

            Repeater1.DataSource = new CarData().zufen(all.ToList(), pagecount, 1);
            Repeater1.DataBind();
            sumLabel.Text = pagemax(all.ToList(), pagecount).ToString();
            numLabel.Text = "1";
            for (int i = 1; i <= pagemax(all.ToList(), pagecount); i++)
            {
                page_dd.Items.Add(new ListItem(i.ToString(),i.ToString()));
            }
        }
        
        //if (name_text.Text.Trim() == "" && brand_dd.SelectedValue=="null" && price_text.Text.Trim() == "")//如果没有条件就查询全部
        //{
        //    Repeater1.DataSource = new CarData().quan();
        //    Repeater1.DataBind();
        //    return;
        //}
        
            //Repeater1.DataSource = new CarData().selectname(name_text.Text.Trim());//根据name中的XXX模糊查询
            // Repeater1.DataSource = new CarData().selectstartname(name_text.Text.Trim());//name以XXX开头来查询
            //Repeater1.DataSource = new CarData().selectendname(name_text.Text.Trim());//name以XXX结束来查询
            //Label1.Text = new CarData().selectendname(name_text.Text.Trim()).Count.ToString();//获取符合条件的个数。其实就是根据模糊查询查出来的集合的子集个数。  
            //Repeater1.DataSource = new CarData().descorder();//按照价格降序进行排列
            //Label1.Text = new CarData().selectpricemax().ToString();//查询价格最大的那个数
            //Label1.Text = new CarData().selectpriceavg().ToString();//查询价格的平均数
            //Label1.Text = new CarData().selectpricesum().ToString();//查询价格的总数
            //Repeater1.DataBind();

    }
    public int pagemax(List<Car> clist,int pagecount)//求最大页数
    {
        int a = clist.Count;
      return Convert.ToInt32(Math.Ceiling(a / (pagecount * 1.0)));
    
    }





}
View Code

完！！

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wwz-wwz/p/6040671.html