O/RM EF6(2)高级查询篇

  1    class Program
  2     {
  3         static void Main(string[] args)
  4         {
  5             /*
  6             {
  7                 UserDb db = new UserDb();
  8                 var usersone = db.Users.FirstOrDefault(x => x.Id == 1);
  9                 ///有先读取缓存没有缓存在去数据库读取
 10                 var newusersone = db.Users.Find(1);
 11                 //Find可以使用缓存，优先从内存查找(限于context)
 12                 //但是linq时不能用缓存，每次都是要查询的
 13                 //AsNoTracking() 如果数据不会更新，加一个可以提升性能如果该查询结果不会在进行缓存
 14                 var usersone2 = db.Users.Where(x => x.Id >1).AsNoTracking().ToList();
 15                 var newusersone2 = db.Users.Find(1);
 16                 Console.ReadKey();
 17             }
 18             */
 19             /*  {
 20 
 21              Detached   未由上下文跟踪。
 22              Unchanged  由上下文跟踪并存在于数据库
 23              Added      实体将由上下文跟踪，但是在数据库中还不存在。
 24              Deleted    从数据库中删除。
 25              Modified   上下文跟踪并存在于数据库中，已修改
 26 
 27              DbContext 有着5种不同的属性对应着5种不同的状态
 28 
 29 　　　　　　　　状态跟踪
 30 
 31              UserDb db = new UserDb();
 32              User user = new User()
 33              {
 34                  Name = "张三",
 35                  Age = 12,
 36              };
 37              Console.WriteLine(db.Entry<User>(user).State);
 38              db.Users.Add(user);
 39              Console.WriteLine(db.Entry<User>(user).State);
 40              db.SaveChanges();
 41              Console.WriteLine(db.Entry<User>(user).State);
 42              user.Name = "王麻子";
 43              Console.WriteLine(db.Entry<User>(user).State);
 44              db.SaveChanges(); 
 45              Console.WriteLine(db.Entry<User>(user).State);
 46              db.Users.Remove(user);
 47              Console.WriteLine(db.Entry<User>(user).State);
 48              db.SaveChanges();
 49                 
 50 
 51         }  */
 52             UserDb dbContext = new UserDb();
 53             {
 54                 //in查询
 55                 var list = dbContext.Users.Where(u => new int[] { 1, 2, 3, 5, 7, 8, 9, 10, 11, 12, 14, 17 }.Contains(u.Id));//in查询
 56                 foreach (var user in list)
 57                 {
 58                     Console.WriteLine(user.Name);
 59                 }
 60             }
 61             { //分页
 62             var list = dbContext.Users.Where(u => new int[] { 1, 2, 3, 5, 7, 8, 9, 10, 11, 12, 14, 18, 19, 20, 21, 22, 23 }.Contains(u.Id))
 63                                             .OrderBy(u => u.Id)
 64                                             .Select(u => new
 65                                             {
 66                                                 Account = u.Name,
 67                                                 Pwd = u.Age
 68                                             }).Skip(3).Take(5);//跳过前3个,获取后5个
 69 
 70 
 71             }
 72             {
 73                 //like 查询
 74                 var list = dbContext.Users.Where(u => u.Name.StartsWith("小") && u.Name.EndsWith("新"))
 75                                                    .Where(u => u.Name.EndsWith("新"))
 76                                                    .Where(u => u.Name.Contains("小新"))
 77                                                    .Where(u => u.Name.Length < 5)
 78                                                    .OrderBy(u => u.Id);
 79 
 80             }
 81             {
 82                 //内连接
 83                 var list = from u in dbContext.Users
 84                            join c in dbContext.Companies on u.CompanyId equals c.Id
 85                            where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
 86                            select new
 87                            {
 88                                Account = u.Age,
 89                                Pwd = u.CompanyId,
 90                                CompanyName = c.Name
 91                            };//).Skip(3).Take(5);
 92             }
 93             {
 94                 //左连接
 95                 var list = from u in dbContext.Users
 96                            join c in dbContext.Companies on u.CompanyId equals c.Id
 97                            into ucList
 98                            from uc in ucList.DefaultIfEmpty()
 99                            where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
100                            select new
101                            {
102                                Account = u.Age,
103                                Pwd = u.Name
104                            };
105             }
106             {
107                 {
108                     {
109                         DbContextTransaction trans = null;
110                         try
111                         {
112                             trans = dbContext.Database.BeginTransaction();
113                             string sql = "Update [User] Set Name=‘小新‘ WHERE Id=@Id";
114                             SqlParameter parameter = new SqlParameter("@Id", 1);
115                             dbContext.Database.ExecuteSqlCommand(sql, parameter);
116                             trans.Commit();
117                         }
118                         catch (Exception ex)
119                         {
120                             if (trans != null)
121                                 trans.Rollback();
122                             throw ex;
123                         }
124                         finally
125                         {
126                             trans.Dispose();
127                         }
128                     }
129                     {
130                         DbContextTransaction trans = null;
131                         try
132                         {
133                             trans = dbContext.Database.BeginTransaction();
134                             string sql = "SELECT * FROM [User] WHERE Id=@Id";
135                             SqlParameter parameter = new SqlParameter("@Id", 1);
136                             List<User> userList = dbContext.Database.SqlQuery<User>(sql, parameter).ToList<User>();
137                             trans.Commit();
138                         }
139                         catch (Exception ex)
140                         {
141                             if (trans != null)
142                                 trans.Rollback();
143                             throw ex;
144                         }
145                         finally
146                         {
147                             trans.Dispose();
148                         }
149                     }
150                 }
151             }
152             Console.ReadKey();
153         }
154     }