【linq to sql学习笔记】查询（上）

之前有简单试验过linq的增删改，查询稍微有点复杂，打算用两篇来写，这篇主要写单表查询。

表结构:

1.查询所有

public static void GetUser()
        {
            using (var context = new UserDataContext())
            { 
                var query_User=context.UserInfo;
                var user = from u in query_User
                           select u;
                var user_ = query_User;
                Console.WriteLine("----------linq查询结果------------");
                foreach (var item in user)
                {
                    Console.WriteLine("编号：{0}   姓名：{1}  年龄:{2}",item.UserNum,item.UserName,item.Age);
                }
                Console.WriteLine();
                Console.WriteLine("----------lambda查询结果------------");
                foreach (var item in user_)
                {
                    Console.WriteLine("编号：{0}   姓名：{1}  年龄:{2}", item.UserNum, item.UserName, item.Age);
                }
            }
        }

结果：

2.查询编号等于9527

var user = from u in query_User
                           where u.UserNum=="9527"
                           select u;
var user_ = query_User.Where(o=>o.UserNum=="9527");

3.查询Age大于10，并且性别=1

  var user = from u in query_User
                           where u.Gender==1 && u.Age>10
                           select u;
  var user_ = query_User.Where(o=>o.Gender==1 && o.Age>10);

4.按年龄降序

 var user = from u in query_User
                           where u.Age>10
                           orderby u.Age descending
                           select u;
 var user_ = query_User.Where(o=>o.Age>10).OrderByDescending(o=>o.Age);

5.姓名包含“虎”

 var user = from u in query_User
                           where u.UserName.Contains("虎")
                           orderby u.Age descending
                           select u;
 var user_ = query_User.Where(o=>o.UserName.Contains("虎")).OrderByDescending(o=>o.Age);

6.姓名第二个字是“小”

var user = from u in query_User
                           where SqlMethods.Like(u.UserName, "_小_")
                           orderby u.Age descending 
                           select u;

7.用户总数

 var user = (from u in query_User
                           orderby u.Age descending 
                           select u).Count();
 var user_ = query_User.OrderByDescending(o=>o.Age).Count();

8.年龄平均数

 var user = (from u in query_User
                           orderby u.Age descending 
                           select u.Age).Average();
 var user_ = query_User.Average(o=>o.Age);

9.别名

var user = (from u in query_User
                            orderby u.Age descending
                            let c="我叫"+u.UserName
                            select c);