Lambda表达式之查询篇

Lambda表达式之查询篇

一般查询

 1 db.User.Select(u => u); // 不带条件查询
 2 
 3 db.User.Where(u => true); //不带条件查询
 4 
 5 db.User.Where(u => u.username == "wjl" || u.username == "hyf"); // 带条件查询  || 表示 “或” && 表示 “且”
 6 
 7 db.User.Select(u => u.username.EndsWith("丽")); // 模糊查询 相当于like '%丽' 
 8 
 9 db.User.Select(u => u.username.IndexOf("丽")); // 模糊查询 相当于like '%丽%' 
10 
11 db.User.Select(u => u.username.StartsWith("丽")); // 模糊查询 相当于like '丽%' 
12 
13 db.User.Where( u => (u.username == user.username && u.userpwd == user.userpwd)).Count(); // 计数 返回int类型的数值

聚合函数查询

//最大值
var list = from p in db.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                MaxPrice = g.Max(p => p.UnitPrice)
            };
//最小值
var q = from p in db.Products
        group p by p.CategoryID into g
        select new
        {
            g.Key,
            MaxPrice = g.Max(p => p.UnitPrice)
        };
//平均值
var q = from p in db.Products
        group p by p.CategoryID into g
        select new
        {
            g.Key,
            AveragePrice = g.Average(p => p.UnitPrice)
        };
//求和
var q = from p in db.Products
        group p by p.CategoryID into g
        select new
        {
            g.Key,
            TotalPrice = g.Sum(p => p.UnitPrice)
        };
//计数
var q = from p in db.Products
        group p by p.CategoryID into g
        select new
        {
            g.Key,
            NumProducts = g.Count()
        };
//带条件计数
var q = from p in db.Products
        group p by p.CategoryID into g
        select new
        {
            g.Key,
            NumProducts = g.Count(p => p.Discontinued)
        };

高级查询

//in查询
var list1 = db.Users.Where(u => new int[] { 1, 2, 3 }.Contains(u.Id));
var list2 = from u in db.Users where new int[] { 1, 2, 3 }.Contains(u.Id) select u;

//分页查询,按需查询所要的字段
var list3 = db.Users.Where(u => new int[] { 1, 2, 3 }.Contains(u.Id))
                            .OrderBy(u => u.Id)
                            .Select(u => new
                            {
                                Account = u.Account,
                                Password = u.Password

                            }).Skip(3).Take(5);

var list4 = (from u in db.Users
                where new int[] { 1, 2, 3 }.Contains(u.Id)
                orderby u.Id
                select new
                {
                    Account = u.Account,
                    Pwd = u.Password
                }).Skip(3).Take(5);

//多条件查询的另一种写法
var list5 = db.Users.Where(u => u.Name.StartsWith("小") && u.Name.EndsWith("新"))
        .Where(u => u.Name.EndsWith("新"))
        .Where(u => u.Name.Contains("小新"))
        .Where(u => u.Name.Length < 5)
        .OrderBy(u => u.Id);

//连接查询，inner join
var list6 = from u in db.Users
            join c in db.Companies on u.CompanyId equals c.Id
            where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
            select new
            {
                Account = u.Account,
                Pwd = u.Password,
                CompanyName = c.Name
            };
//连接查询，left join
var list7 = from u in db.Users
            join c in db.Categories on u.CompanyId equals c.Id
            into ucList
            from uc in ucList.DefaultIfEmpty()
            where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
            select new
            {
                Account = u.Account,
                Pwd = u.Password
            };

分页查询，参数的动态改变自己去设置OrderBy为升序， OrderByDescending为降序 ，ThenByDescending与ThenBy为第二条件排序，Skip相当于not in ，Take相当于Top

 1 var userlist = db.User.Where<User>(u => true).OrderByDescending(u => u.userid).ThenBy(u => u.username).Skip((pageindex - 1) * pagesize).Take(pagesize);
 2 
 3 int pageindex; //从第几条开始
 4 if (!int.TryParse(Request["pageindex"], out pageindex))
 5 {
 6 pageindex = 1;
 7 }
 8 int rcordcount = db.User.Count(); //统计总记录数
 9 int pagesize = 5; //每页要显示的记录条数
10 int pagecount = Convert.ToInt32(Math.Ceiling((double)rcordcount / pagesize)); //计算页数
11 
12 pageindex = pageindex < 1 ? 1 : pageindex; //pageindex不能小于1 和 pageindex 不能大于记录总数
13 pageindex = pageindex > pagecount ? pagecount : pageindex;
14 
15 // OrderBy为升序， OrderByDescending为降序 ，ThenByDescending与ThenBy为第二条件排序，Skip相当于not in ，Take相当于Top
16 var userlist = db.User.Where<User>(u => true).OrderByDescending(u => u.userid).ThenBy(u => u.username).Skip((pageindex - 1)* pagesize).Take(pagesize);

欢迎评论留言交流