EntityFramework EF状态跟踪和各种查询

 //EntityState 四种状态
    //1.Detached 实体跟上下文没有关系
    //2.Detached 监听中 但是无修改
    //3.Added 插入数据
    //4.Modified 更新数据
    //5.MDeleted 删除数据
    public class EFStateTest
    {
        public static void Show()
        {
            try
            {
                Llogin llogin = new Llogin()
                {
                    id1 = 8,
                    name1 = "zhangsan",
                    nameCH = "张三",
                    psw = "/7k8sVej5hE=",
                    qx = "系统管理员",
                    department = "01",
                    TPFW = "0",
                    DepartNumber = "01"
                };
                {
                    using (CodeFirst context = new CodeFirst())
                    {
                        llogin.nameCH = "李四";
                        Console.WriteLine(context.Entry<Llogin>(llogin).State); //实体跟context没关系 Detached
                        context.SaveChanges();  //Detached啥事儿不发生

                        context.Set<Llogin>().Add(llogin);
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);//状态变为 Added
                        context.SaveChanges();//插入数据(自增主键在插入成功后，会自动赋值过去)
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);// Unchanged（监听，但是没变化）

                        llogin.nameCH = "李四123";
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);//状态变为 Modified
                        context.SaveChanges();  //更新数据库，因为状态是Modified
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);// Unchanged（监听，但是没变化）

                        context.Set<Llogin>().Remove(llogin);
                        Console.WriteLine(context.Entry<Llogin>(llogin).State); //状态变为 MDeleted
                        context.SaveChanges();//删除数据，因为状态是Deleted
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);//Detached已经从内存移除了
                    }
                }
                {
                    using (CodeFirst context = new CodeFirst())
                    {
                        llogin.nameCH = "李四897";
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);  //实体跟context没关系 Detached
                        context.Entry<Llogin>(llogin).State = EntityState.Modified;//全字段更新
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);  //状态变为 Modified
                        context.SaveChanges();//更新数据库，因为状态是Modified
                    }
                }
                {
                    using (CodeFirst context = new CodeFirst())
                    {
                        Console.WriteLine(context.Entry<Llogin>(llogin).State); //实体跟context没关系 Detached
                        context.Set<Llogin>().Attach(llogin); //使用Attach增加监听
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);// Unchanged（正在监听，但是没变化）
                        llogin.nameCH = "李四897000"; //只更新这个字段
                        Console.WriteLine(context.Entry<Llogin>(llogin).State);//状态变为 Modified
                        context.SaveChanges();//更新数据库
                    }
                }

                {
                    //Find可以使用缓存，优先从内存查找(限于context)
                    //AsNoTracking() 返回数据不会缓存，加一个可以提升性能
                    using (CodeFirst context = new CodeFirst())
                    {
                        var login = context.Set<Llogin>().Where(l => l.id1 < 7).ToList();
                        //var login = context.Set<Llogin>().Where(l => l.id1 < 7).AsNoTracking().ToList();
                        var login1 = context.Set<Llogin>().Find(2);     //从缓存中查找
                        var login2 = context.Set<Llogin>().Find(8);     //缓存中没有再从数据库中查询
                        var login3 = context.Set<Llogin>().FirstOrDefault(l => l.id1 == 2);//从数据库查询
                    }
                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }
        }
    }

public class EFQueryTest
    {
        public static void Show()
        {
            using (CodeFirst dbContext = new CodeFirst())
            {
                //in查询 
                {
                    var list = dbContext.L_login.Where(u => new int[] { 1, 2, 3 }.Contains(u.id1));//in查询
                    foreach (var user in list)
                    {
                        Console.WriteLine(user.nameCH);
                    }
                }
                {
                    //没有任何差别，只有写法上的熟悉
                    var list = from u in dbContext.L_login
                               where new int[] { 1, 2, 3 }.Contains(u.id1)
                               select u;

                    foreach (var user in list)
                    {
                        Console.WriteLine(user.nameCH);
                    }
                }

                //分页

                {
                    var list = dbContext.L_login.Where(u => new int[] { 1, 2, 3, 4, 5 }.Contains(u.id1))
                                              .OrderBy(u => u.id1)
                                              .Select(u => new
                                              {
                                                  name = u.nameCH,
                                                  Pwd = u.psw
                                              }).Skip(3).Take(5);
                    foreach (var user in list)
                    {
                        Console.WriteLine(user.name);
                    }
                }

                {
                    var list = (from u in dbContext.L_login
                                where new int[] { 1, 2, 3, 4, 5 }.Contains(u.id1)
                                orderby u.id1
                                select new
                                {
                                    name = u.nameCH,
                                    Pwd = u.psw
                                }).Skip(2).Take(3);

                    foreach (var user in list)
                    {
                        Console.WriteLine(user.name);
                    }
                }
                //模糊查询
                {
                    var list = dbContext.L_login.Where(u => u.nameCH.StartsWith("孔") && u.nameCH.EndsWith("颖"))
                                               .Where(u => u.nameCH.EndsWith("颖"))
                                               .Where(u => u.nameCH.Contains("孔新颖"))
                                               .Where(u => u.nameCH.Length < 5)
                                               .OrderBy(u => u.id1);

                    foreach (var user in list)
                    {
                        Console.WriteLine(user.nameCH);
                    }
                }
                //join 
                {
                    var list = (from a in dbContext.L_login
                                join b in dbContext.L_DepartMent on a.DepartNumber equals b.DepartNumber
                                where new int[] { 1, 2, 3, 4, 6, 7, 8 }.Contains(a.id1)
                                select new
                                {
                                    name = a.nameCH,
                                    department = b.DepartMent,
                                    id = a.id1
                                }).OrderBy(u => u.id);
                    foreach (var item in list)
                    {
                        Console.WriteLine("{0} {1}", item.name, item.department);
                    }
                }
                {
                    var list = from a in dbContext.L_login
                               join b in dbContext.L_DepartMent on a.DepartNumber equals b.DepartNumber
                               into abList
                               from ab in abList.DefaultIfEmpty()
                               where new int[] { 1, 2, 3, 4, 6, 7, 8 }.Contains(a.id1)
                               select new
                               {
                                   name = a.nameCH,
                                   department = ab.DepartMent,
                                   pws = a.psw
                               };
                    foreach (var item in list)
                    {
                        Console.WriteLine("{0} {1} {2}", item.name, item.department, item.pws);
                    }
                }

            }

            //sql语句查询
            using (CodeFirst dbContext = new CodeFirst())
            {
                {
                    DbContextTransaction trans = null;
                    try
                    {
                        trans = dbContext.Database.BeginTransaction();
                        string sql = "Update [L_login] Set Name='张三' WHERE Id=@Id";
                        SqlParameter parameter = new SqlParameter("@Id", 3);
                        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 [L_login] WHERE Id=@Id";
                        SqlParameter parameter = new SqlParameter("@Id", 3);
                        List<Llogin> userList = dbContext.Database.SqlQuery<Llogin>(sql, parameter).ToList<Llogin>();
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        if (trans != null)
                            trans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        trans.Dispose();
                    }
                }
            }
        }
    }