1、一般查询

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类型的数值

2、聚合函数查询

//最大值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)        };

3、高级查询

//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 joinvar 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 joinvar 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
     
      (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相当于Top16 var userlist = db.User.Where
      
       (u => true).OrderByDescending(u => u.userid).ThenBy(u => u.username).Skip((pageindex - 1)* pagesize).Take(pagesize);