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

 

 

 

   class Program
    {
        static void Main(string[] args)
        {
            /*
            {
                UserDb db = new UserDb();
                var usersone = db.Users.FirstOrDefault(x => x.Id == 1);
                ///有先读取缓存没有缓存在去数据库读取
                var newusersone = db.Users.Find(1);
                //Find可以使用缓存，优先从内存查找(限于context)
                //但是linq时不能用缓存，每次都是要查询的
                //AsNoTracking() 如果数据不会更新，加一个可以提升性能如果该查询结果不会在进行缓存
                var usersone2 = db.Users.Where(x => x.Id >1).AsNoTracking().ToList();
                var newusersone2 = db.Users.Find(1);
                Console.ReadKey();
            }
            */
            /*  {

             Detached   未由上下文跟踪。
             Unchanged  由上下文跟踪并存在于数据库
             Added      实体将由上下文跟踪，但是在数据库中还不存在。
             Deleted    从数据库中删除。
             Modified   上下文跟踪并存在于数据库中，已修改

             DbContext 有着5种不同的属性对应着5种不同的状态

　　　　　　　　状态跟踪

             UserDb db = new UserDb();
             User user = new User()
             {
                 Name = "张三",
                 Age = 12,
             };
             Console.WriteLine(db.Entry<User>(user).State);
             db.Users.Add(user);
             Console.WriteLine(db.Entry<User>(user).State);
             db.SaveChanges();
             Console.WriteLine(db.Entry<User>(user).State);
             user.Name = "王麻子";
             Console.WriteLine(db.Entry<User>(user).State);
             db.SaveChanges(); 
             Console.WriteLine(db.Entry<User>(user).State);
             db.Users.Remove(user);
             Console.WriteLine(db.Entry<User>(user).State);
             db.SaveChanges();
                

        }  */
            UserDb dbContext = new UserDb();
            {
                //in查询
                var list = dbContext.Users.Where(u => new int[] { 1, 2, 3, 5, 7, 8, 9, 10, 11, 12, 14, 17 }.Contains(u.Id));//in查询
                foreach (var user in list)
                {
                    Console.WriteLine(user.Name);
                }
            }
            { //分页
            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))
                                            .OrderBy(u => u.Id)
                                            .Select(u => new
                                            {
                                                Account = u.Name,
                                                Pwd = u.Age
                                            }).Skip(3).Take(5);//跳过前3个,获取后5个


            }
            {
                //like 查询
                var list = dbContext.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);

            }
            {
                //内连接
                var list = from u in dbContext.Users
                           join c in dbContext.Companies on u.CompanyId equals c.Id
                           where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
                           select new
                           {
                               Account = u.Age,
                               Pwd = u.CompanyId,
                               CompanyName = c.Name
                           };//).Skip(3).Take(5);
            }
            {
                //左连接
                var list = from u in dbContext.Users
                           join c in dbContext.Companies 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.Age,
                               Pwd = u.Name
                           };
            }
            {
                {
                    {
                        DbContextTransaction trans = null;
                        try
                        {
                            trans = dbContext.Database.BeginTransaction();
                            string sql = "Update [User] Set Name='小新' WHERE Id=@Id";
                            SqlParameter parameter = new SqlParameter("@Id", 1);
                            dbContext.Database.ExecuteSqlCommand(sql, parameter);
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            if (trans != null)
                                trans.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            trans.Dispose();
                        }
                    }
                    {
                        DbContextTransaction trans = null;
                        try
                        {
                            trans = dbContext.Database.BeginTransaction();
                            string sql = "SELECT * FROM [User] WHERE Id=@Id";
                            SqlParameter parameter = new SqlParameter("@Id", 1);
                            List<User> userList = dbContext.Database.SqlQuery<User>(sql, parameter).ToList<User>();
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            if (trans != null)
                                trans.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            trans.Dispose();
                        }
                    }
                }
            }
            Console.ReadKey();
        }
    }