莱姆达表达试——查询篇

一般查询

db.user.select(u => u); // 不带条件查询

db.user.where(u => true); //不带条件查询

db.user.where(u => u.username == "wjl" || u.username == "hyf"); // 带条件查询  || 表示 “或” && 表示 “且”

db.user.select(u => u.username.endswith("丽")); // 模糊查询 相当于like '%丽'

db.user.select(u => u.username.indexof("丽")); // 模糊查询 相当于like '%丽%'

db.user.select(u => u.username.startswith("丽")); // 模糊查询 相当于like '丽%'

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

var userlist = db.user.where<user>(u => true).orderbydescending(u => u.userid).thenby(u => u.username).skip((pageindex - 1) * pagesize).take(pagesize);

int pageindex; //从第几条开始
if (!int.tryparse(request["pageindex"], out pageindex))
{
pageindex = 1;
}
int rcordcount = db.user.count(); //统计总记录数
int pagesize = 5; //每页要显示的记录条数
int pagecount = convert.toint32(math.ceiling((double)rcordcount / pagesize)); //计算页数

pageindex = pageindex < 1 ? 1 : pageindex; //pageindex不能小于1 和 pageindex 不能大于记录总数
pageindex = pageindex > pagecount ? pagecount : pageindex;

// orderby为升序， orderbydescending为降序 ，thenbydescending与thenby为第二条件排序，skip相当于not in ，take相当于top
var userlist = db.user.where<user>(u => true).orderbydescending(u => u.userid).thenby(u => u.username).skip((pageindex - 1)* pagesize).take(pagesize);