莱姆达表达试——查询篇
一般查询
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
selectnew
{
g.Key,
MaxPrice = g.Max(p => p.UnitPrice)
};
//最小值
var q = from p in db.Products
group p by p.CategoryID into g
selectnew
{
g.Key,
MaxPrice = g.Max(p => p.UnitPrice)
};
//平均值
var q = from p in db.Products
group p by p.CategoryID into g
selectnew
{
g.Key,
AveragePrice = g.Average(p => p.UnitPrice)
};
//求和
var q = from p in db.Products
group p by p.CategoryID into g
selectnew
{
g.Key,
TotalPrice = g.Sum(p => p.UnitPrice)
};
//计数
var q = from p in db.Products
group p by p.CategoryID into g
selectnew
{
g.Key,
NumProducts = g.Count()
};
//带条件计数
var q = from p in db.Products
group p by p.CategoryID into g
selectnew
{
g.Key,
NumProducts = g.Count(p => p.Discontinued)
};
高级查询
//in查询var list1 = db.Users.Where(u => newint[] { 1, 2, 3 }.Contains(u.Id));
var list2 = from u in db.Users wherenewint[] { 1, 2, 3 }.Contains(u.Id) select u;
//分页查询,按需查询所要的字段
var list3 = db.Users.Where(u => newint[] { 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
wherenewint[] { 1, 2, 3 }.Contains(u.Id)
orderby u.Id
selectnew
{
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
wherenewint[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
selectnew
{
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()
wherenewint[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
selectnew
{
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);
以上是 莱姆达表达试——查询篇 的全部内容， 来源链接： utcz.com/z/535726.html
