EF的使用<三>

ExecuteSqlCommand与SqlQuery

    在数据上下文DBModel的实例中有个Database属性，其中有两组方法.ExecuteSqlCommand()和.SqlQuery()。它们都可以执行SQL语句，只不过.ExecuteSqlCommand()是不返回结果的，只返回受影响的行数，所以.ExecuteSqlCommand()更适合执行创建、更新、删除操作。.SqlQuery()则会返回查询到的结果，并将结果保存在数据实体中，所以更适合执行查询操作。

一：使用EF生产的类，其中test表中UserId与user中id是主外键关系

    public partial class test
    {
        public int Id { get; set; }
        public int Num { get; set; }
        public Nullable<int> UserId { get; set; } 
        public virtual user user { get; set; }
    }

    public partial class user
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public user()
        {
            this.test = new HashSet<test>();
        }
    
        public int Id { get; set; }
        public string UserName { get; set; }
        public Nullable<int> Age { get; set; }
        public byte[] Image { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<test> test { get; set; }
    }

    public partial class user_address
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public string Address { get; set; }
    }

二：添加数据

 方法一：    using (ef_testEntities ef = new ef_testEntities())
                {
                    user u = new user();
                    u.Age = 16;
                    u.UserName = "小花-11";
                    u.test = new List<test>()
                    {
                        new test() { Num = 12,user = u}
                    };
                    ef.user.Add(u);
                    ef.SaveChanges();
                }

方法二：     using (ef_testEntities ef = new ef_testEntities())
                {

                    MySqlParameter[] paramers = { new MySqlParameter { ParameterName = "@userid", Value = 8 }, 

new MySqlParameter { ParameterName = "@address", Value = "杭州" } };
                    MySqlParameter[] paramers = { new MySqlParameter("@userid", 20), new MySqlParameter("@address", "beijing") };
                    ef.Database.ExecuteSqlCommand("INSERT into user_address (UserId,Address)VALUES(@userid,@address);", paramers);
                }

                //执行存储过程
方法三：    using (var cmd = ef.Database.Connection.CreateCommand())
                {
                    MySqlParameter[] paramers = { new MySqlParameter { ParameterName = "@puserId", Value = 12 }, new MySqlParameter { ParameterName = "@paddress", Value = "qinghua" } };
                    cmd.CommandText = "address_add";//存储过程名
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(paramers);
                    if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }

三：修改数据

方法一：    using (ef_testEntities ef = new ef_testEntities())
                {
                    var entity = (from d in ef.user where d.Id == 20 select d).Single();
                    var tmodel = new test();
                    tmodel.user = entity;
                    tmodel.Num = 100;
                    tmodel.UserId = entity.Id;
                    entity.test.Add(tmodel);//添加一条外键数据
                    ef.SaveChanges();
                }

方法二：       using (ef_testEntities ef = new ef_testEntities())
                {
                    var entity = ef.user.FirstOrDefault(a => a.Id == 3);
                    entity.UserName = "mysql数据库";//修改字段数据
                    ef.SaveChanges();
                }

4.为避免先查询数据库，可以直接将 被修改的实体对象 添加到 EF中管理(此时为附加状态Attached)，并手动设置其为未修改状态(Unchanged)，
 同时设置被修改的实体对象的包装类对象 对应属性为修改状态。  

方法三：        using (ef_testEntities ef = new ef_testEntities())
                {
                    user us = new user();
                    us.Id = 4;
                    us.UserName = "中文简体";
                    us.Age = 100;
                    ef.Entry<user>(us).State = EntityState.Modified;
                    ef.SaveChanges();
                }

方法四：    using (ef_testEntities ef = new ef_testEntities())
                {
                    MySqlParameter[] paramers = { new MySqlParameter { ParameterName = "@userid", Value = 37 },
 new MySqlParameter { ParameterName = "@id", Value = 7 }, 

new MySqlParameter { ParameterName = "@address", Value = "铜牛文化产业园" } };
                    ef.Database.ExecuteSqlCommand("UPDATE user_address SET UserId=@userid,Address=@address where id=@id;", paramers);
                }

四：删除数据

方法一： using (ef_testEntities ef = new ef_testEntities())
            {
                user_address us = new user_address();
                us.Id = 12;
                ef.Entry<user_address>(us).State = EntityState.Deleted;//通知上下文这条数据被修改了
                ef.SaveChanges();
            }
 

方法二：  using (ef_testEntities ef = new ef_testEntities())
            {
                var pp = ef.user.FirstOrDefault(a => a.Id == 6);
                ef.user.Remove(pp);
                ef.SaveChanges();
            }

方法三   using (ef_testEntities ef = new ef_testEntities())
            {
                ef.Database.ExecuteSqlCommand("delete from user_address where id=11; ");

            }

五：查询数据

       ef_testEntities ef = new ef_testEntities();

      //1、查询一条数据
       var entity = ef.user.FirstOrDefault(a => a.Id == 37);
       var tests = entity?.test;

      //2.查询一条数据
      var model = ef.Database.SqlQuery<user>("select * from user where id=37").FirstOrDefault();
      var testModel = model?.test;
      
      //3、关联表查询[从表数据可以有多条]
      var model1 = (from d in ef.user where d.Id == 37 select d).Single();
      ef.Entry(model1).Collection(a => a.test).Load();
      foreach (var item in ef.test.Local)
      {
          Console.WriteLine(item.Id + "&" + item.Num + "*" + item.UserId);
      }

      //4.分页查询
       var list = ef.user.OrderBy(a => a.Id).Skip(2).Take(3).ToList();