MVC学习一：EF

2018-06-23 22:29:41来源：未知阅读 ()

一.EF修改和删除的多种方法

方法1：官方推荐先查询在修改或者删除

1 var student = db.Students.FirstOrDefault(t => t.Id == Mid);
2 student.Name = "修改后";//修改值数据
3 db.SaveChanges();//保存 
4 db.Students.Remove(student);//移除对象
5 db.SaveChanges();//保存

方法2：先附加到ef实体对象然后在移除

1 Students s = new Students();
2 s.Id = int.Parse(id);
3 db.Students.Attach(s);//将实体附加到 上下文中 ；
4 db.Students.Remove(s);
5 if (db.SaveChanges() >= 1)
6 {
7     //成功
8 }

方法3：添加到ef实体对象然后标记删除

1 Students s = new Students();
2 s.Id = int.Parse(id);
3 db.Entry<Students>(s).State = System.Data.EntityState.Deleted;//标记删除
4 int num = db.SaveChanges();

方法4：修改实体

 1 //这里需要关闭验证 不然会报错
 2 db.Configuration.ValidateOnSaveEnabled = false;
 3 DbEntityEntry dbS = db.Entry<Students>(s);
 4 dbS.State = System.Data.EntityState.Unchanged;      //清楚所有字段标记  
 5 dbS.Property("Gender").IsModified = true;//标记修改字段 
 6 int num = db.SaveChanges();
 7 db.Configuration.ValidateOnSaveEnabled = true;//打开验证
 8 if (num >= 1)
 9 {
10     //修改成功
11 }

二.标准查询where 在不同场景实现方法不一样

1 //场景1：ef的数据集 使用where 是在 IQueryable 的扩展方法
2 //场景2：List集合的数据集 使用where 是在 IEnumerable 的扩展方法

三.include

场景1：自动连接查询【无Include】

1 //1.自动 连接查询【无Include】
2 var stu1 = db.Students.Where(t => t.Classes.CName == "zha");
3 var cname = stu1.FirstOrDefault();

 1 SELECT TOP (1) 
 2 [Extent1].[Id] AS [Id], 
 3 [Extent1].[CId] AS [CId], 
 4 [Extent1].[Name] AS [Name], 
 5 [Extent1].[Gender] AS [Gender], 
 6 [Extent1].[IsDel] AS [IsDel], 
 7 [Extent1].[AddTime] AS [AddTime]
 8 FROM  [dbo].[Students] AS [Extent1]
 9 INNER JOIN [dbo].[Classes] AS [Extent2] ON [Extent1].[CId] = [Extent2].[CID]
10 WHERE 'zha' = [Extent2].[CName]

场景2：手动内连接查询【有Include】

1 //2手动 内连接查询【有Include】
2 var stu2 = db.Students.Include("Classes").Where(t => t.Id == 11);
3 var ccc = stu2.FirstOrDefault().Classes.CName;

 1 SELECT 
 2 [Limit1].[Id] AS [Id], 
 3 [Limit1].[CId] AS [CId], 
 4 [Limit1].[Name] AS [Name], 
 5 [Limit1].[Gender] AS [Gender], 
 6 [Limit1].[IsDel] AS [IsDel], 
 7 [Limit1].[AddTime] AS [AddTime], 
 8 [Extent2].[CID] AS [CId1], 
 9 [Extent2].[CName] AS [CName], 
10 [Extent2].[CCount] AS [CCount], 
11 [Extent2].[CImg] AS [CImg], 
12 [Extent2].[CIsDel] AS [CIsDel], 
13 [Extent2].[CAddTime] AS [CAddTime]
14 FROM   (SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[CId] AS [CId], [Extent1].[Name] AS [Name], [Extent1].[Gender] AS [Gender], [Extent1].[IsDel] AS [IsDel], [Extent1].[AddTime] AS [AddTime]
15     FROM [dbo].[Students] AS [Extent1]
16     WHERE 11 = [Extent1].[Id] ) AS [Limit1]
17 LEFT OUTER JOIN [dbo].[Classes] AS [Extent2] ON [Limit1].[CId] = [Extent2].[CID]

场景3：无连接查询分两次sql查询【无Include】

1 //3.无连接查询 分两次sql查询【无Include】
2 var stucc = db.Students.Where(t => t.Id == 11);
3 var zz = stucc.FirstOrDefault().Classes.CName;

 1 SELECT TOP (1) 
 2 [Extent1].[Id] AS [Id], 
 3 [Extent1].[CId] AS [CId], 
 4 [Extent1].[Name] AS [Name], 
 5 [Extent1].[Gender] AS [Gender], 
 6 [Extent1].[IsDel] AS [IsDel], 
 7 [Extent1].[AddTime] AS [AddTime]
 8 FROM [dbo].[Students] AS [Extent1]
 9 WHERE 11 = [Extent1].[Id];
10 
11 SELECT 
12 [Extent1].[CID] AS [CID], 
13 [Extent1].[CName] AS [CName], 
14 [Extent1].[CCount] AS [CCount], 
15 [Extent1].[CImg] AS [CImg], 
16 [Extent1].[CIsDel] AS [CIsDel], 
17 [Extent1].[CAddTime] AS [CAddTime]
18 FROM [dbo].[Classes] AS [Extent1]
19 WHERE [Extent1].[CID] = @EntityKeyValue1

四.skip take

1 //Skip 跳过序列中指定数量的元素，然后返回剩余的元素。
2 //Take 从序列的开头返回指定数量的连续元素。
3 //根据这两个方法可以 实现 分页效果

五.反射获取实例属性

1 //利用反射 获取 类 对象 的所有公共 属性 默认是[GetProperties(BindingFlags.Instance | BindingFlags.Public)]
2 var listPro = typeof(Students).GetProperties().ToList();
3 object obj1 = new object();
4 object obj2 = new object();
5 listPro.ForEach(l =>
6 {
7     var newValue = l.GetValue(obj1); //获得某个对象的属性值
8     l.SetValue(obj2, newValue);//修改一个对象的属性 值
9 });

六.EF DLL数据访问帮助父类

  1 using _00EFModel;
  2 using System;
  3 using System.Collections.Generic;
  4 using System.Linq;
  5 using System.Linq.Expressions;
  6 using System.Reflection;
  7 using System.Text;
  8 using System.Threading.Tasks;
  9 
 10 namespace _00EFDLL
 11 {
 12     /// <summary>
 13     /// 数据访问 父类  
 14     /// </summary>
 15     public class BaseDLL<Tclass> where Tclass : class,new()
 16     {
 17         /// <summary>
 18         /// 数据访问上下文
 19         /// </summary>
 20         _00EFModel.Entities db = new _00EFModel.Entities();
 21 
 22         #region 1.0添加数据 + Add(Tclass model)
 23         /// <summary>
 24         /// 1.0添加数据
 25         /// </summary>
 26         /// <param name="model"></param>
 27         public void Add(Tclass model)
 28         {
 29             db.Set<Tclass>().Add(model);
 30         }
 31         #endregion
 32 
 33         #region  2.0 删除方法1 删除给定的对象 +Del(Tclass model)
 34         /// <summary>
 35         /// 2.0 删除方法1 删除给定的对象
 36         /// </summary>
 37         /// <param name="model"></param>
 38         public void Del(Tclass model)
 39         {
 40             //将实体 添加到上下文
 41             db.Set<Tclass>().Attach(model);
 42             //把实体 标记为删除
 43             db.Set<Tclass>().Remove(model);
 44         }
 45         #endregion
 46 
 47         #region  2.1 删除方法2 根据条件删除对象 +Del(Expression<Func<Tclass, bool>> delWhere)
 48         /// <summary>
 49         /// 2.1 删除方法2 根据条件删除对象 
 50         /// </summary>
 51         /// <param name="delWhere"></param>
 52         public void Del(Expression<Func<Tclass, bool>> delWhere)
 53         {
 54             //查询所有满足条件的实体对象
 55             var modelS = db.Set<Tclass>().Where(delWhere).ToList();
 56             modelS.ForEach(m =>
 57             {
 58                 //附加到 上下文
 59                 db.Set<Tclass>().Attach(m);
 60                 //标记为 删除状态
 61                 db.Set<Tclass>().Remove(m);
 62             });
 63         }
 64         #endregion
 65 
 66         #region 3.0 修改方法1 修改某个实体的 某些属性 +Up(Tclass model, params string[] strparams)
 67         /// <summary>
 68         /// 3.0 修改方法1 修改某个实体的 某些属性(根据id修改)【*用这个需要注意关闭检查】
 69         /// </summary>
 70         /// <param name="model"></param>
 71         /// <param name="strparams">可变参数</param>
 72         public int Up(Tclass model, params string[] strparams)
 73         {
 74             //关闭检查
 75             db.Configuration.ValidateOnSaveEnabled = false;
 76             //附加到上下文
 77             var m = db.Entry<Tclass>(model);
 78             //把全部属性标记为 没有修改
 79             m.State = System.Data.Entity.EntityState.Unchanged;
 80             for (int i = 0; i < strparams.Length; i++)
 81             {
 82                 //标记要修改的属性
 83                 m.Property(strparams[i]).IsModified = true;
 84             }
 85             int num = db.SaveChanges();
 86             //打开检查
 87             db.Configuration.ValidateOnSaveEnabled = true;
 88             return num;
 89         }
 90         #endregion
 91 
 92         #region 3.1 修改方法2 根据条件 修改指定的 属性 值 +Up(Expression<Func<Tclass, bool>> upWhere, Tclass model, params string[] strparame)
 93         /// <summary>
 94         /// 3.1 修改方法2 根据条件 修改指定的 属性 值
 95         /// </summary>
 96         /// <param name="upWhere"></param>
 97         /// <param name="model"></param>
 98         /// <param name="strparame"></param>
 99         public void Up(Expression<Func<Tclass, bool>> upWhere, Tclass model, params string[] strparame)
100         {
101             //查询出满足条件的所有实体
102             var modelS = db.Set<Tclass>().Where(upWhere).ToList();
103             //利用反射 获取 类 对象 的所有公共 属性 默认是[GetProperties(BindingFlags.Instance | BindingFlags.Public)]
104             var listPro = typeof(Tclass).GetProperties().ToList();
105             // 属性对象 键值对
106             List<PropertyInfo> dic = new List<PropertyInfo>();
107             listPro.ForEach(l =>
108             {
109                 for (int i = 0; i < strparame.Length; i++)
110                 {
111                     //循环 判断 添加 需要修改的 属性对象
112                     if (l.Name == strparame[i].Trim())
113                     {
114                         dic.Add(l);
115                         break;
116                     }
117                 }
118             });
119 
120             if (dic.Count > 0)//判断 属性对象集合  是否 有 数据
121             {
122                 foreach (var property in dic)
123                 {
124                     //取 传过来的对象 里面的值
125                     var newValue = property.GetValue(model);
126                     foreach (var mymodel in modelS)
127                     {
128                         //修改到 对象集合
129                         property.SetValue(mymodel, newValue);
130                     }
131                 }
132             }
133         }
134         #endregion
135 
136         #region  4.0 查询方法 +GetList<Tkey>(Expression<Func<Tclass, bool>> strWhere, Expression<Func<Tclass, Tkey>> strOrederBy = null, bool order = true)
137         /// <summary>
138         /// 4.0 查询方法 
139         /// </summary>
140         /// <typeparam name="Tkey"></typeparam>
141         /// <param name="strWhere">查询条件</param>
142         /// <param name="strOrederBy">排序条件</param>
143         /// <param name="order">是否升序</param>
144         /// <returns></returns>
145         public List<Tclass> GetList<Tkey>(Expression<Func<Tclass, bool>> strWhere, Expression<Func<Tclass, Tkey>> strOrederBy = null, bool order = true)
146         {
147             var t = db.Set<Tclass>().Where(strWhere);
148             if (strOrederBy != null)
149             {
150                 if (order)
151                     t = t.OrderBy(strOrederBy);
152                 else
153                     t = t.OrderByDescending(strOrederBy);
154             }
155             return t.ToList();
156         }
157         #endregion
158 
159         #region 4.1 查询方法2 分页查询 +GetList<Tkey>(int indexPage, int sizePage, Expression<Func<Tclass, bool>> strWhere, Expression<Func<Tclass, Tkey>> strOrederBy = null, bool order = true)
160         /// <summary>
161         /// 4.1 查询方法2 分页查询
162         /// </summary>
163         /// <typeparam name="Tkey"></typeparam>
164         /// <param name="indexPage">页码</param>
165         /// <param name="sizePage">页容量</param>
166         /// <param name="strWhere">查询条件</param>
167         /// <param name="strOrederBy">排序字段</param>
168         /// <param name="order">是否升序</param>
169         /// <returns></returns>
170         public List<Tclass> GetList<Tkey>(int indexPage, int sizePage, Expression<Func<Tclass, bool>> strWhere, Expression<Func<Tclass, Tkey>> strOrederBy = null, bool order = true)
171         {
172             var t = db.Set<Tclass>().Where(strWhere);
173             if (strOrederBy != null)
174             {
175                 if (order)
176                     t = t.OrderBy(strOrederBy);
177                 else
178                     t = t.OrderByDescending(strOrederBy);
179             }
180             return t.Skip((indexPage - 1) * sizePage).Take(sizePage).ToList();
181         }
182         #endregion
183 
184         #region 提交 +save()
185         /// <summary>
186         /// 提交
187         /// </summary>
188         /// <returns></returns>
189         public int save()
190         {
191             return db.SaveChanges();
192         }
193         #endregion
194     }
195 }