Entity Framework
EF有三种方式来进行数据操作及持久化。分别是Database-First,Model-First,Code-first
Database First
是基于已存在的数据库，利用某些工具（如Vs提供的EF设计器）创建实体类，数据库对象与实体类的匹配关系等，你也可以手动修改这些自动生成的代码及匹配文件。
Model First
这种方式是先利用某些工具（如VS的EF设计器）设计出实体数据模型及他们之间的关系，然后再根据这些实体、关系去生成数据库对象及相关代码文件。
Code First
这种方式需要先写一些代码，如实体对象，数据关系等，然后根据已有的代码描述，自动创建数据对象，这种方式在前一篇文章已经简单说过了。但其实这种方法与Model First是非常类似的。我们自己写的代码，其实就是用代码表示实体模型，而Model First是用可视化的方式描述了实体模型。
code First方法默认情况下是以命名空间.DBcontext类名来作为自动创建的数据库名称
code First这种方式也可以用于已存在的数据库，具体作法如下：
第一步：在app.config或者web.config中配置数据库连接字符串
<connectionStrings> <add name="PermissionEntities" connectionString="Data Source=(local); Database=PermissionData; User ID=sa; Password=123456;" providerName="System.Data.SqlClient" /> </connectionStrings>
第二步：是让DBcontext获取已存在数据库，在构造DBcontext时，指定使用这个连接字符串
/// <summary> /// The permission entities. /// </summary> public class PermissionEntities : DbContext { /// <summary> /// Gets or sets the users. /// </summary> public DbSet<User> Users { get; set; } /// <summary> /// Gets or sets the user role relationships. /// </summary> public DbSet<UserRoleRelationship> UserRoleRelationships { get; set; } /// <summary> /// Gets or sets the roles. /// </summary> public DbSet<Role> Roles { get; set; } /// <summary> /// Gets or sets the role permission relationships. /// </summary> public DbSet<RolePermissionRelationship> RolePermissionRelationships { get; set; } /// <summary> /// Gets or sets the permissions. /// </summary> public DbSet<Permission> Permissions { get; set; } /// <summary> /// Gets or sets the menu permissions. /// </summary> public DbSet<MenuPermission> MenuPermissions { get; set; } /// <summary> /// Gets or sets the module permissions. /// </summary> public DbSet<ModulePermission> ModulePermissions { get; set; } }
第三步:添加要匹配的类以及匹配关系。
/// <summary> /// 用户 /// </summary> public class User { /// <summary> /// 编号 /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 部门Id /// </summary> public Guid DepartmentId { get; set; } /// <summary> /// 用户名 /// </summary> [Required] [MaxLength(255)] [Column(TypeName = "Varchar")] public string UserName { get; set; } /// <summary> /// 密码 /// </summary> [Required] [MaxLength(32)] [Column(TypeName = "Char")] public string Password { get; set; } /// <summary> /// /昵称 /// </summary> [Required] [MaxLength(255)] public string NickName { get; set; } /// <summary> /// 邮件 /// </summary> [Required] [MaxLength(255)] [Column(TypeName = "Varchar")] public string Email { get; set; } /// <summary> /// 手机 /// </summary> [Required] [MaxLength(11)] [Column(TypeName = "Char")] public string Mobile { get; set; } /// <summary> /// 启用 禁用 /// </summary> [Required] public bool Enabled { get; set; } /// <summary> /// 是否是管理员账号 /// </summary> [Required] public bool IsAdministrator { get; set; } /// <summary> /// 注册时间 /// </summary> [Required] public DateTime RegisterDate { get; set; } /// <summary> /// 禁用登录Ip /// </summary> public string IpLimitation { get; set; } /// <summary> /// 备注 /// </summary> public string Remark { get; set; } /// <summary> /// Gets or sets the user role relationships. /// </summary> public ICollection<UserRoleRelationship> UserRoleRelationships { get; set; } } /// <summary> /// 用户角色关系 /// </summary> public class UserRoleRelationship { /// <summary> /// Id /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 角色Id /// </summary> [Required] public Guid RoleId { get; set; } /// <summary> /// 用户Id /// </summary> [Required] public Guid UserId { get; set; } /// <summary> /// Gets or sets the user. /// </summary> public virtual User User { get; set; } /// <summary> /// Gets or sets the role. /// </summary> public virtual Role Role { get; set; } } /// <summary> /// 角色 /// </summary> public class Role { /// <summary> /// 编号 /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 父角色Id /// </summary> public Guid? ParentId { get; set; } /// <summary> /// 角色名称 /// </summary> [Required] [MaxLength(255)] public string Name { get; set; } /// <summary> /// 是否禁用 /// </summary> [Required] public bool Enabled { get; set; } /// <summary> /// 备注 /// </summary> public string Remark { get; set; } /// <summary> /// Gets or sets the user role relationships. /// </summary> public ICollection<UserRoleRelationship> UserRoleRelationships { get; set; } /// <summary> /// Gets or sets the role permission relationships. /// </summary> public ICollection<RolePermissionRelationship> RolePermissionRelationships { get; set; } } /// <summary> /// 角色权限关系 /// </summary> public class RolePermissionRelationship { /// <summary> /// Gets or sets the id. /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 角色Id /// </summary> [Required] public Guid RoleId { get; set; } /// <summary> /// 权限Id /// </summary> [Required] public Guid PermissionId { get; set; } /// <summary> /// Gets or sets the role. /// </summary> public virtual Role Role { get; set; } /// <summary> /// Gets or sets the permission. /// </summary> public virtual Permission Permission { get; set; } } /// <summary> /// 权限 /// </summary> public class Permission { /// <summary> /// 编号 /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 权限名称 /// </summary> [Required] [MaxLength(255)] public string Name { get; set; } /// <summary> /// 是否禁用 /// </summary> [Required] public bool Enabled { get; set; } /// <summary> /// 备注 /// </summary> public string Remark { get; set; } /// <summary> /// Gets or sets the role permission relationships. /// </summary> public ICollection<RolePermissionRelationship> RolePermissionRelationships { get; set; } /// <summary> /// Gets or sets the menu permissions. /// </summary> public ICollection<MenuPermission> MenuPermissions { get; set; } } /// <summary> /// 菜单权限 /// </summary> public class MenuPermission { /// <summary> /// 编号 /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 菜单父级菜单 /// </summary> public Guid? ParentId { get; set; } /// <summary> /// 权限Id(属于那个权限) /// </summary> [Required] public virtual Guid PermissionId { get; set; } /// <summary> /// 名字 /// </summary> [Required] [MaxLength(255)] public string Name { get; set; } /// <summary> /// 菜单地址 /// </summary> [Required] [MaxLength(255)] public string Address { get; set; } /// <summary> /// 是否禁用 /// </summary> [Required] public bool Enabled { get; set; } /// <summary> /// 菜单类型 /// </summary> [Required] public byte Type { get; set; } /// <summary> /// 是否显示菜单 /// </summary> [Required] public bool Display { get; set; } /// <summary> /// 排序 /// </summary> [Required] public int Sort { get; set; } /// <summary> /// 深度 /// </summary> [Required] public int Depth { get; set; } /// <summary> /// 备注 /// </summary> public string Remark { get; set; } /// <summary> /// Gets or sets the module permissions. /// </summary> public ICollection<ModulePermission> ModulePermissions { get; set; } } /// <summary> /// 模块 /// </summary> public class ModulePermission { /// <summary> /// 编号 /// </summary> [Key] public Guid Id { get; set; } /// <summary> /// 父级模块Id /// </summary> public Guid? ParentId { get; set; } /// <summary> /// 菜单Id(属性那个菜单) /// </summary> [Required] public virtual Guid MenuPermissionId { get; set; } /// <summary> /// 名字 /// </summary> [Required] [MaxLength(255)] public string Name { get; set; } /// <summary> /// 模块内容(比如Id) /// </summary> [Required] [MaxLength(255)] public string Content { get; set; } /// <summary> /// 类型(增删改查) /// </summary> [Required] public byte Type { get; set; } /// <summary> /// 是否禁用 /// </summary> [Required] public bool Enabled { get; set; } /// <summary> /// 备注 /// </summary> public string Remark { get; set; } }
第四步：获取数据
/// <summary> /// 测试链接数据库. /// </summary> public static void AddPermission() { Role r = new Role { Name = "sdsd", Id = Guid.NewGuid(), Enabled = true, ParentId = Guid.NewGuid(), Remark = "sdsdsd" }; // 一定要引用 EntityFramework跟EntityFramework.SqlServer 这个2个dll // 也可以直接使用当前上下文实现(不推荐此方法) using (PermissionEntities p = new PermissionEntities()) { p.Roles.Add(r); p.SaveChanges(); } }
这种方式其实是Database First，只是用我们自己写的类替换了由可视化工具生成类及xml文件。
最终针对 底层仓储进行一下封装BaseRepository 此类定义为抽象类 主要提供增删改查的方法
/// <summary> /// The base repository. /// </summary> public abstract class BaseRepository : IDisposable { /// <summary> /// The db context. /// </summary> protected readonly DbContext DBContext; /// <summary> /// Initializes a new instance of the <see cref="BaseRepository"/> class. /// </summary> /// <param name="dbContext"> /// The db context. /// </param> protected BaseRepository(DbContext dbContext) { this.DBContext = dbContext; } /// <summary> /// The add entities. /// </summary> /// <param name="entity"> /// The entity. /// </param> /// <param name="isSubmit"> /// The is submit. /// </param> /// <typeparam name="T"> /// </typeparam> public abstract void AddEntities<T>(T entity, bool isSubmit = false) where T : class; /// <summary> /// The update entities. /// </summary> /// <param name="entity"> /// The entity. /// </param> /// <param name="isSubmit"> /// The is submit. /// </param> /// <typeparam name="T"> /// </typeparam> public abstract void UpdateEntities<T>(T entity, bool isSubmit = false) where T : class; /// <summary> /// The delete entities. /// </summary> /// <param name="entity"> /// The entity. /// </param> /// <param name="isSubmit"> /// The is submit. /// </param> /// <typeparam name="T"> /// </typeparam> public abstract void DeleteEntities<T>(T entity, bool isSubmit = false) where T : class; /// <summary> /// The load entities m. /// </summary> /// <param name="wherelambda"> /// The wherelambda. /// </param> /// <typeparam name="T"> /// </typeparam> /// <returns> /// The <see cref="IQueryable"/>. /// </returns> public abstract IQueryable<T> LoadEntitiesM<T>(Func<T, bool> wherelambda) where T : class; /// <summary> /// The load pager entities. /// </summary> /// <param name="pageSize"> /// The page size. /// </param> /// <param name="pageIndex"> /// The page index. /// </param> /// <param name="total"> /// The total. /// </param> /// <param name="whereLambda"> /// The where lambda. /// </param> /// <param name="isAsc"> /// The is asc. /// </param> /// <param name="orderByLambda"> /// The order by lambda. /// </param> /// <typeparam name="TSource"> /// </typeparam> /// <typeparam name="T"> /// </typeparam> /// <returns> /// The <see cref="IQueryable"/>. /// </returns> public abstract IQueryable<T> LoadPagerEntities<TSource, T>( int pageSize, int pageIndex, out int total, Func<T, bool> whereLambda, bool isAsc, Func<T, TSource> orderByLambda) where T : class; /// <summary> /// 释放资源 /// </summary> public virtual void Dispose() { if (this.DBContext != null) { this.DBContext.Dispose(); } } }
下面用sqlserver数据做演示 主要用SqlRepository 实现仓储基类
/// <summary> /// The sql repository. /// </summary> public class SqlRepository : BaseRepository { /// <summary> /// Initializes a new instance of the <see cref="SqlRepository"/> class. /// </summary> /// <param name="dbContext"> /// The db context. /// </param> public SqlRepository(DbContext dbContext) : base(dbContext) { } /// <summary> /// The add entities. /// </summary> /// <param name="entity"> /// The entity. /// </param> /// <param name="isSubmit"> /// The is submit. /// </param> /// <typeparam name="T"> /// </typeparam> public override void AddEntities<T>(T entity, bool isSubmit = false) { DBContext.Entry(entity).State = EntityState.Added; if (isSubmit) { DBContext.SaveChanges(); } } /// <summary> /// The update entities. /// </summary> /// <param name="entity"> /// The entity. /// </param> /// <param name="isSubmit"> /// The is submit. /// </param> /// <typeparam name="T"> /// </typeparam> public override void UpdateEntities<T>(T entity, bool isSubmit = false) { DBContext.Set<T>().Attach(entity); DBContext.Entry(entity).State = EntityState.Modified; if (isSubmit) { DBContext.SaveChanges(); } } /// <summary> /// The delete entities. /// </summary> /// <param name="entity"> /// The entity. /// </param> /// <param name="isSubmit"> /// The is submit. /// </param> /// <typeparam name="T"> /// </typeparam> public override void DeleteEntities<T>(T entity, bool isSubmit = false) { DBContext.Set<T>().Attach(entity); DBContext.Entry(entity).State = EntityState.Deleted; if (isSubmit) { DBContext.SaveChanges(); } } /// <summary> /// The load entities m. /// </summary> /// <param name="wherelambda"> /// The wherelambda. /// </param> /// <typeparam name="T"> /// </typeparam> /// <returns> /// The <see cref="IQueryable"/>. /// </returns> public override IQueryable<T> LoadEntitiesM<T>(Func<T, bool> wherelambda) { return DBContext.Set<T>().Where<T>(wherelambda).AsQueryable(); } /// <summary> /// The load pager entities. /// </summary> /// <param name="pageSize"> /// The page size. /// </param> /// <param name="pageIndex"> /// The page index. /// </param> /// <param name="total"> /// The total. /// </param> /// <param name="whereLambda"> /// The where lambda. /// </param> /// <param name="isAsc"> /// The is asc. /// </param> /// <param name="orderByLambda"> /// The order by lambda. /// </param> /// <typeparam name="TSource"> /// </typeparam> /// <typeparam name="T"> /// </typeparam> /// <returns> /// The <see cref="IQueryable"/>. /// </returns> public override IQueryable<T> LoadPagerEntities<TSource, T>(int pageSize, int pageIndex, out int total, Func<T, bool> whereLambda, bool isAsc, Func<T, TSource> orderByLambda) { var tempData = DBContext.Set<T>().Where<T>(whereLambda); total = tempData.Count(); // 排序获取当前页的数据 tempData = isAsc ? tempData.OrderBy(orderByLambda).Skip(pageSize * (pageIndex - 1)).Take(pageSize).AsQueryable() : tempData.OrderByDescending(orderByLambda).Skip(pageSize * (pageIndex - 1)).Take(pageSize).AsQueryable(); return tempData.AsQueryable(); } }
下面附上测试代码
/// <summary> /// The permission service. /// </summary> public class PermissionService { /* 测试环境 需要在本地创建一个数据库 名称见app.config */ /// <summary> /// 测试事务提交 /// </summary> public static void AddTransactionScope() { User u = new User { Id = Guid.NewGuid(), DepartmentId = Guid.NewGuid(), Email = "45514544@qq.com", Enabled = true, Mobile = "1254555555", NickName = "小久", Password = "sdsdsdsdsdsdsds", UserName = "kokkskskdj", RegisterDate = DateTime.Now, Remark = string.Empty, IsAdministrator = false, }; Role r = new Role { Name = "sdsd", Id = Guid.NewGuid(), Enabled = true, ParentId = Guid.NewGuid(), Remark = "sdsdsd" }; UserRoleRelationship ur = new UserRoleRelationship { Id = Guid.NewGuid(), RoleId = r.Id, UserId = u.Id }; using (BaseRepository repository = PermissionService.Factory()) { using (TransactionScope trans = new TransactionScope()) { try { repository.AddEntities(u); repository.AddEntities(r); repository.AddEntities(ur, true); trans.Complete(); } catch (Exception exception) { throw new Exception(exception.Message); } finally { trans.Dispose(); } } } } /// <summary> /// 测试链接数据库. /// </summary> public static void AddPermission() { Role r = new Role { Name = "sdsd", Id = Guid.NewGuid(), Enabled = true, ParentId = Guid.NewGuid(), Remark = "sdsdsd" }; // 一定要引用 EntityFramework跟EntityFramework.SqlServer 这个2个dll // 也可以直接使用当前上下文实现(不推荐此方法) using (PermissionEntities p = new PermissionEntities()) { p.Roles.Add(r); p.SaveChanges(); } } /// <summary> /// 测试查询 /// </summary> public static void Query() { using (BaseRepository repository = PermissionService.Factory()) { var reulst = repository.LoadEntitiesM<Role>(role => role.Enabled); } } /// <summary> /// 工厂方法 /// </summary> /// <returns>数据仓储</returns> public static BaseRepository Factory() { DbContext dbContext = new PermissionEntities(); // 默认sqlserver仓储 也可以改用mysql仓储 BaseRepository repository = new SqlRepository(dbContext); return repository; } }
