EntityFramwork 七七八八
Tip 技术的选型受技术先进性、技术持续性、技术普及度、推行力度的影响。 我也很无奈，一大把年纪了又要重新学一种ORMapping框架。
说实话，这是我用过的最复杂的ORMapping框架了。 EntityFramework 微软推出的ORMapping方案，可用代码来描述（CodeFirst） 实体类和数据库是对应的，可自动生成数据库表 实体类和数据操作是分开的。必须另外写一套代码操作数据。实体类可归为Model层，操作类可归为DAL（或BLL）层 EF6.x 目前只能通过 NuGet 进行管理，所以，首先确定你的 Visual Studio 中的 NuGet 为最新版本（最低支持 2.8.3，最新版本 3.0）。 本文以最新语法为准，与老版本区别之处可参考：http://blog.csdn.net/chenyq2008/article/details/41659205 又出来一个 EntityFramwork core(原先叫 EF 7，基于.net core)，区别见 http://www.cnblogs.com/n-pei/p/4274907.html http://www.cnblogs.com/xishuai/p/ef7-develop-note.html https://docs.microsoft.com/en-us/ef/efcore-and-ef6/index 架构 EDM （实体数据模型）：EDM包括三个模型，概念模型、 映射和存储模型。 概念模型 ︰ 概念模型包含模型类和它们之间的关系。独立于数据库表的设计。 存储模型 ︰ 存储模型是数据库设计模型，包括表、 视图、 存储的过程和他们的关系和键。 映射 ︰ 映射包含有关如何将概念模型映射到存储模型的信息。 LINQ to Entities ︰ LINQ to Entities 是一种用于编写针对对象模型的查询的查询语言。它返回在概念模型中定义的实体。 Entity SQL: Entity SQL 是另一种炉类似于L2E的言语，但相给L2E要复杂的多，所以开发人员不得不单独学习它。 Object Services(对象服务)：是数据库的访问入口，负责数据具体化，从客户端实体数据到数据库记录以及从数据库记录和实体数据的转换。 Entity Client Data Provider：主要职责是将L2E或Entity Sql转换成数据库可以识别的Sql查询语句，它使用Ado.net通信向数据库发送数据可获取数据。 ADO.Net Data Provider：使用标准的Ado.net与数据库通信 三种模式 CodeFirst 先定义实体类，用实体类生成数据库 Database First 从数据库生成实体类 Model First 使用Visual Studio实体设计器，设计ER，同时生成Entity类和DB 参考 http://www.cnblogs.com/libingql/p/3349866.html https://github.com/aspnet/EntityFramework/wiki https://docs.microsoft.com/en-us/ef/ http://www.cnblogs.com/xuf22/articles/5513283.html http://www.entityframeworktutorial.net/code-first-with-entity-framework.aspx http://www.cnblogs.com/guomingfeng/category/480047.html 安装 install-package entityframework ---------------------------------------------- 数据库上下文（DbContext） ---------------------------------------------- DbContext主要负责以下活动： EntitySet: Dbctx包含了所有映射到表的entities Querying：将Linq-To-Entities转译为Sql并发送到数据库 Change Tracking：从数据库获取entities后保留并跟踪实体数据变化 Persisting Data：根据entity状态执行Insert、update、delete命令 Caching：Dbctx的默认第一级缓存，在上下文中的生命周期中存储entity Manage Relationship：Dbctx在DbFirst模式中使用CSDL、MSL、SSDL管理对象关系，Code first中使用fluent api 管理关系 Object Materialization：Dbctx将物理表转成entity实例对象 示例 using System.Data.Entity; namespace App { public class AppContext : DbContext { // 构造函数，此处指定了数据库连接字符串 public AppContext() : base("Default") {} // 实体集合 public DbSet<Dept> Depts { get; set; } public DbSet<User> Users { get; set; } public DbSet<Role> Roles { get; set; } // 创建数据库 protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); Database.SetInitializer(new MigrateDatabaseToLatestVersion<AppContext, AppBoxMigrationConfiguration>()); } } } using (var ctx = newSchoolDBEntities()) { //Can perform CRUD operation using ctx here.. } ---------------------------------------------- 实体类(Entity) ---------------------------------------------- POCO类（不依赖于任何框架的实体类） using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace App { public class User : IKeyID { // 主键 [Key] public int ID { get; set; } // 必填;长度约束 [Required, StringLength(50)] public string Name { get; set; } // 可空属性 public DateTime? Birthday { get; set; } // 关联对象 public virtual ICollection<Role> Roles { get; set; } public virtual ICollection<Title> Titles { get; set; } public virtual Dept Dept { get; set; } } } 外键约束（父子表关系）RelationShip public virtual ICollection<Role> Roles { get; set; } ---------------------------------------------- DbSet ---------------------------------------------- usage public virtual DbSet<User> Users{get; set;} method Add(entity) : 添加 AsNoTracking<entity> : 不缓存，可增加性能 Attach(entity) : 附加实体 Find(int) : 查找（根据主键） Create : 创建（注意创建后还需Add操作） Include : 加载附加子对象 Remove : 删除实体 SqlQuery : 执行Sql语句 ---------------------------------------------- 标注 http://www.cnblogs.com/libingql/p/3352058.html ---------------------------------------------- Entity Framework Code First 约定 表名为：类名的英语复数形式，创建的表所有者为dbo 主键为：ID 或 类名 + ID 对于int类型的主键，会自动的设置其为自动增长列 用Annotation标注 可使用System.ComponentModel.DataAnnotations来指定表名、字段名、字段约束等信息 [Table("Product", Schema = "dbo")] // 表名、所有者 [Key] // 主键字段 [Column("MenuID")] // 字段名 [Required] // 字段必填 [TypeName = "MONEY")] // 字段类型 [StringLength(50)] // 字段长度 [DatabaseGenerated(DatabaseGeneratedOption.None)] // 主键不自动增长 [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // 主键自增长 [DatabaseGenerated(DatabaseGeneratedOption.Computed)] // 该列是通过其它列计算得出的，不会将其持久化到数据库中。只读？类似视图。 [NotMapped] // 属性不匹配数据库字段 [TimeStamp] // 时间戳。详见《并发处理》章节 [ConcurrencyCheck ] // 并发检测。详见《并发处理》章节 [ForeignKey("CatID")] // 外键名称。 [Index("TitleIndex", IsUnique = true)] // 唯一键 eg [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)] public Guid SocialSecurityNumber { get; set; } 用代码方式指定（FluentAPI） public class XXXContext : DbContext { ... protected override void OnModelCreating(DbModelBuilder modelBuilder) { // 设置 Blog 表 var entity = modelBuilder.Entity<Blog>(); entity .ToTable("MyBlog", "dbo") // 表名(EF6) .HasOptional(x => x.ParentMessage) // 表名(EF7) .HasKey(x => x.BlogId); // 主键 .HasKey(t => new { t.KeyID, t.CandidateID }) // 复合主键 .HasMany(r => r.Users) // 1对多关系 .WithMany(u => u.Roles) // .Map(x => x.ToTable("RoleUsers") // 关联表 .MapLeftKey("RoleID") // 左键关联 .MapRightKey("UserID")) // 右键关联 .Ignore(t => t.Remark) // 忽略属性 .HasRequired(t => t.Category) // Category的值来自外键 .WithMany(t => t.Products) // .HasForeignKey(d => d.CatID) // } ; // 设置 Blog.Title 字段 entity .Property(x => x.Title) // 字段 .IsRequired() // 必填 .HasMaxLength(50) // 最大长度 .HasColumnName("TitleColumn"); // 字段名 .HasColumnType("text") // 字段类型 .HasPrecision(18, 2) // 数字类型的精度设置 .GenerateValuesOnAdd(false) // 不自增 .ManyToOne(n => n.Entity, t => t.ChildEntities)// 多对1 .ForeignKey(t => t.EntityId) // 外键 ; // 可统一设置所有库表前缀 modelBuilder.Types().Configure(f => f.ToTable("TablePrefix_" + f.ClrType.Name)); } } 或更复杂一点，将配置独立建一个类 http://download.csdn.net/detail/zhanxueguang/8950589 新建表配置类 using System.Data.Entity.ModelConfiguration; public partial class BlogConfig : EntityTypeConfiguration<Blog> { public BlogConfig() { this.ToTable("Blog"); //表名 this.HasKey(x => x.BlogId); //设置主键 this.Property(x => x.Title).IsRequired().HasMaxLength(50); //设置字段属性，约束 not null， 最大长度50 this.Property(x => x.Content).HasColumnType("text"); //设置字段类型为 text this.Property(x => x.CreateTime).IsRequired(); // 设置字段约束 not null this.HasMany(x => x.Comments); // 设置导航字段， Blog: Comment --> one: many } } 注册配置类 using System; using System.Linq; using System.Data.Entity; using System.Reflection; using System.Data.Entity.ModelConfiguration; namespace CodeFirstSample { public partial class CodeFirstContext : DbContext { public CodeFirstContext() : base("CodeFirstContext"){} protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new BlogConfig()); base.OnModelCreating(modelBuilder); } // 获取相关模型的数据集，使用 new 关键字来隐藏父类的 Set<> 方法 public new IDbSet<TEntity> Set<TEntity>() where TEntity : class { return base.Set<TEntity>(); } } } ---------------------------------------------- 操作数据（CRUD） 三种查询方式 1) LINQ to Entities 2) Entity SQL 3) Native SQL ---------------------------------------------- Query LINQ to Entities： using (var ctx = newSchoolDBEntities()) { var query = ctx.Students.where(s => s.StudentName == "Bill"); var student = query.FirstOrDefault<Student>(); } var query = ctx.Customers.Where(r => r.Address.City == city); var query = ctx.Customers.Where("r.Address.City = '"+city+"'"); // 现在不能用了 var query = ctx.Customer.Where(r => r.CustomerID.Contains("V")); var query = ctx.Customer.Where("it.CustomerID LIKE @CustomerID", new ObjectParameter("CustomerID","%V%")); LINQ Query syntax: using (var ctx = new SchoolDBEntities()) { var query = from st in ctx.Students where st.StudentName == "Bill"select st; var student = query.FirstOrDefault<Student>(); } var query = from c in ctx.Customers where c.Address.City == city select c; Entity SQL: using (var ctx = new SchoolDBEntities()) { string sql = "SELECT VALUE st FROM SchoolDBEntities.Students AS st WHERE st.StudentName == 'Bill'"; var query = db.CreateQuery<Orders>("SELECT VALUE Orders FROM Orders WHERE Orders.OrderDate < @dt;", ps); } var query = ctx.CreateQuery<Customers>("SELECT VALUE c FROM Customers AS c WHERE c.Address.City = @city", new ObjectParameter("city", city)); Entity SQL & EntityClient var objctx = (ctx as IObjectctxAdapter).Objectctx; var student = objctx.CreateQuery<Student>(sqlString); var newStudent = student.First<Student>(); using (var conn = new EntityConnection("name=SchoolDBEntities")) { conn.Open(); EntityCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT VALUE st FROM SchoolDBEntities.Students as st where st.StudentName='Bill'"; var dict = new Dictionary<int, string>(); using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection)) { while (rdr.Read()) { int a = rdr.GetInt32(0); var b = rdr.GetString(1); dict.Add(a, b); } } } Native SQL（数据库平台相关，不推荐使用） using (var ctx = new SchoolDBEntities()) { var studentName = ctx.Students.SqlQuery("Select studentid, studentname, standardId from Student where studentname='Bill'").FirstOrDefault<Student>(); } 评价 解析步骤（都是解析为CommandTree，再解析为数据库依赖的sql语句，本质上并无区别） Linq to Entity ---> Command Tree --> SQL --> ADO.net DataProvider --> DB Entity SQL 都是数据库平台无关的 Entity SQL 转换的SQL性能会好一点 Linq to Entity 强类型特性，编译时检查 建议临时查询选择EntityCLient，如果是操作数据那只能采用ObjectService CRUD CREATE ctx.Users.Add(new User() { UserName = "New Student" }); ctx.SaveChanges(); READ var users = ctx.Users.ToList<User>(); var user = ctx.Person.Single(a = >a.Id == 1); ctx.Users.Where(...).ToList(); ctx.Users.Find(...).FirstOrDefault() 字符串比较 // where year >= yearFrom and year <= yearTo string yearFrom = this.txtYearFrom.Text; string yearTo = this.txtYearTo.Text; if (!string.IsNullOrEmpty(yearFrom) && !string.IsNullOrEmpty(yearTo)) q = q.Where(t => t.Year.CompareTo(yearFrom)>=0 && t.Year.CompareTo(yearTo)<=0); if (!string.IsNullOrEmpty(yearFrom) && string.IsNullOrEmpty(yearTo)) q = q.Where(t => t.Year.CompareTo(yearFrom) >= 0); if (string.IsNullOrEmpty(yearFrom) && !string.IsNullOrEmpty(yearTo)) q = q.Where(t => t.Year.CompareTo(yearTo) <= 0); UPDATE 方法一：先找到》更改属性》SaveChanges public UpdateUserName(int id, string userName) { var user = ctx.Users.Where(s => s.ID == id).FirstOrDefault<Student>(); user.UserName = userName; ctx.SaveChanges(); } 方法二：先设置键值》Attach》更改属性》SaveChanges（相当于直接写update语句） public UpdateUserName(int id, string userName) { User user = new User{ID = id}; var entity = ctx.Users.Attach(user); entity.UserName = userName; ctx.SaveChanges(); } 方法三：手动修改EntityState public UpdateUserName(int id, string userName) { User user = new User{ID = id, UserName=userName}; var entity = ctx.Entity(user); entity.State = EntityState.Modified; entity.Property("UserName").IsModified = true; ctx.SaveChanges(); } DELETE 方法1：先找到》再删除》SaveChanges var user = ctx.Users.FirstOrDefault(m => m.ID = id); ctx.Users.Remove(user); ctx.SaveChanges(); 方法2：自己创建对象》附加》删除》SaveChanges var user = new User{ID = id}; ctx.User.Attach(user); ctx.Remove(user); ctx.SaveChanges(); 方法3：自己创建对象》放入EF容器》修改状态》SaveChanges var user = new User{ID = id}; var entry = ctx.Entry(user); entry.State = EntityState.Deleted; ctx.SaveChanges(); 注：在创建对象的时候，需要指定主键列才行 方法4： 使用扩展 https://github.com/loresoft/EntityFramework.Extended ctx.Users.Delete(u => u.Id = id); 关联表 新增 int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text); item.Roles = new List<Role>(); foreach (int roleID in roleIDs) { Role role = new Role { ID = roleID }; DB.Roles.Attach(role); item.Roles.Add(role); } 删除 清除用户表中的部门字段数据 DB.Users.Include(u => u.Dept) .Where(u => userIDs.Contains(u.ID)) .ToList() .ForEach(u => u.Dept = null) ; 或者反过来（删除部门表的User列表信息） Dept dept = DB.Depts.Include(d => d.Users) .Where(d => d.ID == deptID) .FirstOrDefault(); foreach (int userID in userIDs) { User user = dept.Users.Where(u => u.ID == userID).FirstOrDefault(); if (user != null) dept.Users.Remove(user); } 更新 先删光，再添加 int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text); int[] addRoleIDs = roleIDs.Except(item.Roles.Select(r => r.RoleID)).ToArray(); int[] removeRoleIDs = item.Roles.Select(r => r.RoleID).Except(roleIDs).ToArray(); foreach (int roleID in addRoleIDs) { var role = new Role { RoleID = roleID }; DB.Roles.Attach(role); item.Roles.Add(role); } foreach (int roleID in removeRoleIDs) { item.Roles.Remove(item.Roles.Single(r => r.RoleID == roleID)); } 脱机实体以及状态 Attach Attach用来将某个已知存在于数据库中的实体重新加载到上下文中。SaveChanges不会尝试将Attached的实体插入到数据库中，因为这个实体假设已经存在于数据库中。 User admin = DB.Users.Where(u => u.Name == "admin").FirstOrDefault(); Dept dept = new Dept { ID = 1 }; DB.Depts.Attach(dept); admin.Dept = dept; DB.SaveChanges(); 如果实体已经被加载到EF上下文中，此时再次Attach同一个对象，就会出错: DB.Depts.Find(1); User admin = DB.Users.Where(u => u.Name == "admin").FirstOrDefault(); Dept dept = new Dept { ID = 1 }; DB.Depts.Attach(dept); admin.Dept = dept; DB.SaveChanges(); 解决办法: 先在EF的Local缓存中查找对象，如果找不到再Attach新对象 EntityState 有以下状态 Added Deleted Modified Unchanged Detached eg var student = new Student() { StudentName = "New Student" }; student.StudentAddress = new StudentAddress() { Address1 = "Address", City = "City1" }; using (var ctx = newSchoolDBEntities()) { ctx.Students.Attach(student); // 获取实体状态 EntityState var studentEntry = ctx.Entry(disconnectedStudent); var addressEntry = ctx.Entry(disconnectedStudent.StudentAddress); Console.WriteLine("Student EntityState: {0}",studentEntry.State); Console.WriteLine("StudentAddress EntityState: {0}",addressEntry.State); } local https://msdn.microsoft.com/en-us/data/jj592872 using (var context = new BloggingContext()) { // Load all blogs from the database into the context context.Blogs.Load(); // Add a new blog to the context context.Blogs.Add(new Blog { Name = "My New Blog" }); // Mark one of the existing blogs as Deleted context.Blogs.Remove(context.Blogs.Find(1)); // Loop over the blogs in the context. Console.WriteLine("In Local: "); foreach (var blog in context.Blogs.Local) { Console.WriteLine( "Found {0}: {1} with state {2}", blog.BlogId, blog.Name, context.Entry(blog).State); } // Perform a query against the database. Console.WriteLine("\nIn DbSet query: "); foreach (var blog in context.Blogs) { Console.WriteLine( "Found {0}: {1} with state {2}", blog.BlogId, blog.Name, context.Entry(blog).State); } } ---------------------------------------------- 多表查询 ---------------------------------------------- 多表关联查询 var q = (from exp in Common.Db.RptImportExports join invest in Common.Db.RptInvests on exp.Month equals invest.Month join consume in Common.Db.RptConsumes on exp.Month equals consume.Month select new { exp.Month, exp.ExpInc2, invest.LimitInvestInc, consume.LimitRetailInc2 }); if (!this.txtFrom.Text.IsNullOrEmpty()) q = q.Where(t => t.Month.CompareTo(this.txtFrom.Text) >= 0); if (!this.txtTo.Text.IsNullOrEmpty()) q = q.Where(t => t.Month.CompareTo(this.txtTo.Text) <= 0); 注意： join on 语句不能左右颠倒，会报错 q 是一个IQueryable<~a>匿名类，后面的Where语句会自动检测到Month属性 对应的Lambda表达式该怎么写？ ---------------------------------------------- 执行SQL ---------------------------------------------- 返回实体 using (var ctx = newSchoolDBEntities()) { //列名必需要Entity属性匹配 var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>(); } 返回非实体类型 using (var ctx = newSchoolDBEntities()) { //Get student name of string type string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=1").FirstOrDefault<string>(); } 执行SQL命令 using (var ctx = new SchoolDBEntities()) { int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student set studentname ='changed student by command' where studentid=1"); int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) values('New Student')"); int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student where studentid=1"); } ---------------------------------------------- 事务(Transaction) ---------------------------------------------- 隐式事务 try { Product prod1 = db.Products.First(p => p.ProductID == 4); Product prod2 = db.Products.First(p => p.ProductID == 5); prod1.UnitsInStock -= 3; prod2.UnitsInStock -= 5;// 错误:库存 数量的单位不能是负数 db.SubmitChanges(); // 要么全部成功要么全部失败 } catch (System.Data.SqlClient.SqlExceptione) { //执行异常处理 } 明显式事务（TransactionScope） using (Transcope ts = new TransactionScope()) { try { Product prod1 = db.Products.First(p => p.ProductID == 4); Product prod2 = db.Products.First(p => p.ProductID == 5); prod1.UnitsInStock -= 3; prod2.UnitsInStock -= 5;// 错误:库存 数量的单位不能是负数 db.SubmitChanges(); // 要么全部成功要么全部失败 } catch (System.Data.SqlClient.SqlExceptione) { //执行异常处理 } } ---------------------------- using (var context = new BlogDbContext()) { using (TransactionScope transaction = new TransactionScope()) { ...... context.SaveChanges(); ...... context.SaveChanges(); // 提交事务。如果本语句前出现任何错误，所有操作都会回滚。 transaction.Complete(); } } ---------------------------------------------- 缓存 ---------------------------------------------- EF 只是对查询条件做了缓存，而不是缓存查询的结果集 在DbContext生命周期内，实体类是可以访问的。 无比怀念 Gentle.net 框架，它是在Application级别进行缓存的。 DbContext未关闭前 EF维护了若干实体的状态(Entry.State) 可以根据状态来判断实体是否有更新，若无更新即可直接调出来使用 强制不缓存(System.Data.Entity.DbExtensions.AsNoTracking) var people = context.People.Where(p => p.PersonID > 100).AsNoTracking().ToList(); var monkey = from m in _dataContext.Monkeys.AsNoTracking() where m.MonkeyId == monkeyId select m) .FirstOrDefault(); 强制从数据库中更新缓存 var service = (serviceAgent as PersonAccountServiceAgent); var context = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)service.DBConn.DataBase).ObjectContext; context.Refresh(System.Data.Objects.RefreshMode.StoreWins, CurrentAccount); EF老版本没有缓存的，不知道EF的缓存可不可靠，不放心的话用HttpContext的缓存，如 if( HttpContext.Cache["temp"]==null) HttpContext.Cache["temp"] = DataContext.info.where(p => p.id == id).FirstOrDefault(); return HttpContext.Cache["temp"] as List<Object>(); 使用 EntityFrame.Extended 有缓存处理 详见 EntityFrame.Extened 章节 ---------------------------------------------- 并发处理 http://www.aichengxu.com/other/6164839.htm ---------------------------------------------- 并发处理 方案 悲观并发：A在读取某条记录时，就单独霸占了这个记录，B无法获? １鬯屎鲜菥赫ち业某『希换岽此浪侍狻? 乐观并发：A/B都可以读取某条记录时，若A修改了，B提交修改时会提示（可选择覆盖或取消） 说明 乐观锁并不适合处理高并发的场景，少量的数据冲突才是乐观并发的初衷。 悲观锁同样也不适合处理高并发，特别在加锁成本比较大的时候。 如果项目并发量确实大， 那就可以考虑采用其他技术实现，比如 消息队列等 EF CodeFirst 只支持乐观并发 步骤 （1）添加RowVersion字段，类型为TimeStamp，且一个类只能有一个此标示 [Timestamp] public byte [] RowVersion { get; set; } （2）EF更新实体时会先检查RowVersion，如果发现RowVersion不一致，则抛出DbUpdateConcurrencyException异常 （3）检测到异常后可考虑重加载数据 // 创建一个ctx取的第一条数据，修改 但是不提交 BlogContext fircontext = new BlogContext(); Blog firstblog = fircontext.Blogs.FirstOrDefault(); firstblog.BlogName = "哈哈，被改掉了" ; // 创建另一个ctx还是取第一条数据，修改并提交 BlogContext secContext = new BlogContext(); Blog secondBlog = secContext.Blogs.FirstOrDefault(); secondBlog.BlogAuthor = "JasonShen"; secContext.SaveChanges(); // 这个时候再提交第一个ctx所做的修改 try { // 这是后会发现现在的数据，已经和刚进入时发生了变化，故报错 fircontext.SaveChanges(); Console.WriteLine("保存成功" ); } catch(Exception e) { Console.WriteLine("保存失败" ); // 重新从数据库加载数据 e.Entries.Single().Reload(); Console.WriteLine(firstblog.BlogName); } 针对单个字段的并发 有些时候并不需要控制针对整条记录的并发，只需要控制某个列的数据 不会出现脏操作就ok 这个时候可使用ConcurrencyCheck 标示 public class User { ... [ConcurrencyCheck ] public string CertID { get; set; } } 以下代码中会捕捉到并发异常 BlogContext firContext = new BlogContext (); User u1 = firContext.Users.FirstOrDefault(); BlogContext secContext = new BlogContext (); User u2 = secContext.Users.FirstOrDefault(); u2.certID= "22222"; secContext.SaveChanges(); try { u1.certID = "33333" ; firContext.SaveChanges(); } catch (Exception e) { Console .WriteLine("并发报错" ); } ---------------------------------------------- 贪婪加载、懒惰加载、定向加载 ---------------------------------------------- 惰性加载：延迟加载对象关联的实体，用到时再加载，EF默认为LazyLoading var students = ctx.Students.ToList<Student>(); var student = students[0]; var address = std.StudentAddress; // 地址是动态加载的，此时才去查询 贪婪加载：使用Include()，自动加载关联实体 using (var ctx = new SchoolDBEntities()) { var res = (from s in ctx.Students.Include("Standard") where s.StudentName == "Student1" select s).FirstOrDefault<Student>(); } 定向加载：Reference()和Collection() 方法 using (var ctx = new SchoolDBEntities()) { ctx.Configuration.LazyLoadingEnabled = false; // 加载学生 IList<Student> studList = ctx.Students.ToList<Student>(); Student std = studList.Where(s => s.StudentID == 1).FirstOrDefault<Student>(); // 加载指定学生的Standard信息 ctx.Entry(std).Reference(s => s.Standard).Load(); // 加载指定学生的课程信息 ctx.Entry(std).Collection(s => s.Courses).Load(); } 禁用延迟加载 public PortalContext() : base("name=PortalContext") { this.Configuration.LazyLoadingEnabled = false; } ---------------------------------------------- 修改部分字段不重建表 ---------------------------------------------- 说明 http://www.tuicool.com/articles/EfIvey 用了Codefirst后最大的困扰就是数据变化引起数据库的删除再新建，这样会造成数据丢失 在EntityFrameWork5.0中引入了数据迁移功能能很好的解决这个问题。 步骤 PM> enable-migrations 自动生成 migration配置文件类 namespace NoteSystem.Migrations { using System; using System.Data.Entity; using System.Data.Entity.Migrations; using System.Linq; internal sealed class Configuration<TContext> : DbMigrationsConfiguration<TContext> where TContext:DbContext { public Configuration() { AutomaticMigrationsEnabled = true; AutomaticMigrationDataLossAllowed = true; } } } 修改数据库构造函数 public class XXXContext : DbContext { protected override void OnModelCreating(DbModelBuilder modelBuilder) { Database.SetInitializer(new MigrateDatabaseToLatestVersion<NoteDb, Configuration<NoteDb>>()); //Database.SetInitializer(new DropCreateDatabaseIfModelChanges<NoteDb>()); } } 对应的数据库中会多一个表 _MigrationHistory MigrationId ContextKey Model ProductVersion 每次修改Model代码后，会新增一条记录，并修改对应的表字段 注意，采用该方案后，不要手动去修改数据库字段。 ---------------------------------------------- 查看 EF 生成的 SQL ---------------------------------------------- (1) query.ToTraceString() (2) 调试时鼠标移到query上面，即可查看sql (3) 记录日志 this.Database.Log = (t) => Trace.WriteLine(t); http://www.genshuixue.com/i-cxy/p/7689447 记录到log： <interceptors> <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework"/> </interceptors> 记录到文件 <interceptors> <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework"> <parameters> <parameter value="C:\Temp\LogOutput.txt"/> </parameters> </interceptor> </interceptors> (4) 通过 sqlserver 的 SQL Profile来查看EF生成和执行的SQL语句 ---------------------------------------------- 外键关联 http://www.cnblogs.com/libingql/p/3353112.html ---------------------------------------------- 产品目录和产品 public class Category { public int CategoryID { get; set; } public string CategoryName { get; set; } public virtual ICollection<Product> Products { get; set; } } public class Product { public int ProductID { get; set; } public string ProductName { get; set; } public decimal UnitPrice { get; set; } public int CategoryID { get; set; } public virtual Category Category { get; set; } // virtual是为了启用延迟加载 } 会自动生成 Product.CategoryID ---(N:1)--- Category.CategoryID 删除Category某条记录会自动级联删除Product中的相关记录 手工指定外键 [ForeignKey("UserProfile")] public int UserID { get; set; } // EF6 modelBuilder.Entity<Product>() .HasForeignKey(d => d.CatID) // 外键 .HasRequired(t => t.Category) // 外键值来自Category表 .WithMany(t => t.Products) // Category对Product是1对多关系 ; // EF7 modelBuilder.Entity<ChildEntity>() .ManyToOne(n => n.Entity, t => t.ChildEntities) .ForeignKey(t => t.EntityId) ; modelBuilder.Entity<Entity>() .WithMany() .Map(x => x.MapKey("ParentID")) 禁止级联删除 modelBuilder.Entity<Product>() .HasForeignKey(d => d.CatID) // 外键 .HasRequired(t => t.Category) // 外键值来自Category表 .WithMany(t => t.Products) // Category对Product是1对多关系 .WillCascadeOnDelete(false) // 可禁止级联删除 ; 或统一禁止 protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); // 禁用默认表名复数形式 modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>(); // 禁用一对多级联删除 modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>(); // 禁用多对多级联删除 } 更详细（或复杂的）请查看http://www.cnblogs.com/libingql/p/3353112.html 创建删除数据库 string userMDF = System.IO.Path.Combine(userTempFolder, @"NewCreateDB.mdf"); string connStr = String.Format (@"DataSource=.SQLEXPRESS; AttachDbFilename={0};IntegratedSecurity=True; Connect Timeout=30;User Instance=True; Integrated Security = SSPI;",userMDF); NewCreateDB newDB = new NewCreateDB(connStr); newDB.CreateDatabase(); ... if (db.DatabaseExists()) Console.WriteLine("Northwind数据库存在"); else Console.WriteLine("Northwind数据库不存在"); ..... newDB.DeleteDatabase(); 继承 [Table(Name = "dbo.Contacts")] [InheritanceMapping(Code = "Unknown", Type = typeof(Contact), IsDefault = true)] [InheritanceMapping(Code = "Employee", Type = typeof(EmployeeContact))] [InheritanceMapping(Code = "Supplier", Type = typeof(SupplierContact))] [InheritanceMapping(Code = "Customer", Type = typeof(CustomerContact))] [InheritanceMapping(Code = "Shipper", Type = typeof(ShipperContact))] public partial class Contact : INotifyPropertyChanging, INotifyPropertyChanged { [Column(Storage = "_ContactID",IsPrimaryKey = true, IsDbGenerated = true)] public int ContactID{ } [Column(Storage = "_ContactType",IsDiscriminator = true)] public string ContactType{ } } public abstract partial class FullContact : Contact{ } public partial class EmployeeContact : FullContact{ } public partial class SupplierContact : FullContact{ } public partial class CustomerContact : FullContact{ } public partial class ShipperContact : Contact{ }
EntityFramework 性能优化和三方辅助库
-------------------------------------------------- 性能优化 http://www.cnblogs.com/nele/p/6118100.html -------------------------------------------------- 使用最新版EF nuget install-package EntityFramework 查看生成的 Sql 语句 VS 诊断工具 用SQL Server Profiler 工具监控运行的sql语句 数据库分页 var items = query.Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList(); 对只读数据不跟踪实体 var items = db.Person.Where(a => a.IsDeleted == false).AsNoTracking().ToList(); 对于只读操作，建议采用AsNoTracking，数据是Detached状态，不跟踪状态。 设置 Configuration.AutoDetectChangesEnabled = false; // 自动检测实体变更 Configuration.ValidateOnSaveEnabled = false; // 保存时不验证数据有效性 Configuration.LazyLoadingEnabled = false; // 禁止懒加载 Configuration.ProxyCreationEnabled = false; // ？ 使用合适的加载方式 懒惰加载: 附加表字段不加载 context.Configuration.ProxyCreationEnabled = true; context.Configuration.LazyLoadingEnabled = true; 导航属性被标记为virtual 按需加载: 加载附加表字段，尽可能减少数据库访问次数 var user = db.Person.Include(a => a.Roles); ToList()的位置 IQueryable返回的是查询表达式，也就是说生成了SQL查询语句但是却还没有与数据库进行交互。 ToList()方法是将生成的 IQueryable 接口生成的 sql 语句提交给数据库进行查询，再转化为对象列表 db.Person.Where(a => a.IsDeleted == false).ToList(); 使用SqlQuery时不跟踪实体 此方法获得的实体查询是在数据库（Database）上，实体不会被上下文跟踪。 var user = db.Database.SqlQuery<User>("select * from user", parameter).ToList(); 此方法获得的实体查询是被上下文跟踪，所以能直接赋值后SaveChanges()。 var user = db.Set<User>().SqlQuery("select * from user").ToList(); user.Last().Name = "makmong"; db.SaveChanges(); 尽量用ViewModel代替实体Model，减少获取字段 var query = db.User.Select(a => new { Id = a.Id, Name = a.Name }).ToList(); -------------------------------------------------- Entity Framework Extendeds https://github.com/loresoft/EntityFramework.Extended nuget install-package EntityFramework.Extended using EntityFramework.Extended; -------------------------------------------------- 直接删除 db.Users.Where(u => u.FirstName == "firstname").Delete(); db.Users.Delete(u => u.FirstName == "firstName"); // 已废弃 直接写更新语句 update user set name="newName" where firstName="kevin"; db.Users.Where(t => t.FirstName=="Kevin").Update(t => new User() {Name = "ceshi"}); db.Users.Update(t1 => t1.FirstName=="Kevin", t2 => new User() {Name = "ceshi"}); // 已废弃 查询一批数据并统计个数 nuget install-package EntityFramework.Extended var q = db.User.Where(t => t.Name.StartsWith("a")); var q1 = q.FutureCount(); var q2 = q.Skip(10).Take(10).Future(); var data = q2.ToList(); var count = q1.Value; 注：FutureXXX()方法都暂停执行语句，等到第一个ToList()语句出现，批量执行？ 所有Future函数中的查询包装到一个连接中执行 缓存 从缓存中取数据 var tasks = db.Tasks.Where(t => t.CompleteDate == null).FromCache(); // 使用默认缓存 var users = db.User.Where(u => u.Id > 5).FromCache(CachePolicy.WithDurationExpiration(TimeSpan.FromSeconds(30))); // 300秒缓存 可指定缓存标志 // cache assigned tasks var tasks = db.Tasks .Where(t => t.AssignedId == myUserId && t.CompleteDate == null) .FromCache(tags: new[] { "Task", "Assigned-Task-" + myUserId }); // some update happened to Task, expire Task tag CacheManager.Current.Expire("Task"); 自定义缓存方案 Locator.Current.Register<ICacheProvider>(() => new MemcachedProvider()); 移除缓存 RemoveCache() Audit 捕捉数据库实体变更。详见github var db = new TrackerContext(); var audit = db.BeginAudit(); ... db.SaveChanges(); var log = audit.LastLog; -------------------------------------------------- EFUtilities https://github.com/MikaelEliasson/EntityFramework.Utilities nuget install-package EFUtilities using EntityFramework.Utilities; -------------------------------------------------- 修改 using (var db = new YourDbContext()) { db.AttachAndModify(new BlogPost { ID = postId }).Set(x => x.Reads, 10); db.SaveChanges(); } IncludeEFU var result = db.Contacts .IncludeEFU(db, c => c.PhoneNumbers) .ToList(); var result = db.Contacts .IncludeEFU(db, x => x.PhoneNumbers .Where(n => n.Number == "10134") .OrderBy(p => p.ContactId) .ThenByDescending(p => p.Number)) .ToList() ; ForceDelete db.Database.ForceDelete() Bulk delete var count = EFBatchOperation .For(db, db.BlogPosts) .Where(b => b.Created < upper && b.Created > lower && b.Title == "T2.0") .Delete() ; Bulk insert EFBatchOperation.For(db, db.BlogPosts).InsertAll(list); Batch update EFBatchOperation.For(db, db.Comments).UpdateAll(commentsFromDb, x => x.ColumnsToUpdate(c => c.Reads)); var lines = csv.ReadAllLines().Select(l => l.Split(";")); var comments = lines.Select(line => new Comment{ Id = int.Parse(line[0]), Reads = int.Parse(line[1]) }); EFBatchOperation.For(db, db.Comments).UpdateAll(comments, x => x.ColumnsToUpdate(c => c.Reads)); EFBatchOperation.For(db, db.Comments).Where(x => x.Text == "a").Update(x => x.Reads, x => x.Reads + 1); -------------------------------------------------- LinqKit https://github.com/loresoft/EntityFramework.Extended -------------------------------------------------- -------------------------------------------------- LinqToExcel https://github.com/paulyoder/LinqToExcel nuget install-package LinqToExcel -------------------------------------------------- demo var excel = new ExcelQueryFactory("excelFileCsvFile"); var indianaCompanies = from c in excel.Worksheet<Company>() where c.State == "IN" select c; worksheet var oldCompanies = from c in repo.Worksheet<Company>("US Companies") //worksheet name = 'US Companies' where c.LaunchDate < new DateTime(1900, 1, 1) select c; 字段映射 excel.AddMapping<Company>(x => x.State, "Providence"); //maps the "State" property to the "Providence" column excel.AddMapping("Employees", "Employee Count"); //maps the "Employees" property to the "Employee Count" column var indianaCompanies = from c in excel.Worksheet<Company>() where c.State == "IN" && c.Employees > 500 select c; 或者 public class Company { [ExcelColumn("Company Title")] //maps the "Name" property to the "Company Title" column public string Name { get; set; } [ExcelColumn("Providence")] //maps the "State" property to the "Providence" column public string State { get; set; } [ExcelColumn("Employee Count")] //maps the "Employees" property to the "Employee Count" column public string Employees { get; set; } } where var indianaCompanies = from c in excel.Worksheet() where c["State"] == "IN" || c["Zip"] == "46550" select c; var largeCompanies = from c in excel.Worksheet() where c["EmployeeCount"].Cast<int>() > 500 select c; 无标题行 var indianaCompanies = from c in excel.WorksheetNoHeader() where c[2] == "IN" //value in 3rd column select c; 在Excel命名区域（NamedRange)中查找 var indianaCompanies = from c in excel.NamedRange<Company>("NamedRange") //Selects data within the range named 'NamedRange' where c.State == "IN" select c; 在Excel指定区域中查找 var indianaCompanies = from c in excel.WorksheetRange<Company>("B3", "G10") //Selects data within the B3 to G10 cell range where c.State == "IN" select c; 根据索引找工作表 var oldCompanies = from c in repo.Worksheet<Company>(1) //Queries the second worksheet in alphabetical order where c.LaunchDate < new DateTime(1900, 1, 1) select c; 字段处理 excel.AddTransformation<Company>(x => x.IsBankrupt, cellValue => cellValue == "Y"); var bankruptCompanies = from c in excel.Worksheet<Company>() where c.IsBankrupt == true select c; 获取工作表名/字段名 var worksheetNames = excel.GetWorksheetNames(); var columnNames = excel.GetColumnNames("worksheetName"); 一些全局设置 excel.TrimSpaces = TrimSpacesType.Both; excel.ReadOnly = true; excel.DatabaseEngine == DatabaseEngine.Ace; excel.StrictMapping = StrictMappingType.Both; excel.UsePersistentConnection = true;
EntityFramework & Expression
------------------------------------------------ Expression 表达式（二叉树） 参考 http://kb.cnblogs.com/page/42489/ http://kb.cnblogs.com/page/42509/5/ http://kb.cnblogs.com/page/42509/ ------------------------------------------------ eg Expression<Func<int, int, int>> expression = (a, b) => a * b + 2; var lambda = expression.Compile(); var result = lambda(1, 2); // 4 eg: 2+3 BinaryExpression body = Expression.Add(Expression.Constant(2), Expression.Constant(3)); // 2+3 Expression<Func<int>> expression = Expression.Lambda<Func<int>>(body, null); Expression -> Lambda Func<int> lambda = expression.Compile(); Console.WriteLine(lambda()); 概念 表达式树主要由下面四部分组成： 1、Body 主体部分 2、Parameters 参数部分 3、NodeType 节点类型 4、Lambda表达式类型 eg Expression<Func<int, int, int>> expr = (x, y) => { return x+y; }; 等价于 ParameterExpression p1 = Expression.Parameter(typeof(int), "a"); ParameterExpression p2 = Expression.Parameter(typeof(int), "b"); BinaryExpression exp = Expression.Multiply(p1, p2); var lamExp = Expression.Lambda<Func<int, int, int>>(exp, new ParameterExpression[] { p1, p2 }); 常见的表达式(详见 System.Linq.Expression类) 注：表达式常有Assign、Checked附加重载，表示是否检测功能，详见示例 表达式都有以下类别（覆盖了所有的C#表达式） BinaryExpression（二元表达式） Add/Substract/Multiply/Devide/Power : +-*/^ Equal/NotEqual/ReferenceEqual/ReferenceNotEqual : == != GreaterThan/GreaterThanOrEqual/LessThan/LessThanOrEqual : > >= < <= And/Or/OrElse/ExclusiveOr : && || Assign : Coalesce : LeftShift/RightShift : << >> MakeBinary : Modulo/ModuloAssign : MethodCallExpression IndexExpression UnaryExpression（单元表达式） Increment/PreIncrement/PostIncrement : ++ Decrement/PreDecrement/PostDecrement : -- IsFalse/IsTrue : ?? Not : ! Negate : - Quote : " Throw/Rethrow : TypeAs : as UnaryPlus : Unbox : ! OnesComplement : MakeUnary : Convert : BlockExpression GotoExpression DebugInfoExpression ConditionalExpression ConstantExpression DefaultExpression DynamicExpression InvocationExpression LabelExpression LambdaExpression ListInitExpression LoopExpression TryExpression MemberInitExpression NewExpression NewArrayExpression ParameterExpression RuntimeVariablesExpression SwitchExpression TypeBinaryExpression 常见的表达式 数学相关 Add/Substract/Multiply/Devide/Power Equal/NotEqual/ReferenceEqual/ReferenceNotEqual GreaterThan/GreaterThanOrEqual/LessThan/LessThanOrEqual 布尔相关 Equal/NotEqual/ReferenceEqual/ReferenceNotEqual GreaterThan/GreaterThanOrEqual/LessThan/LessThanOrEqual And/Or/OrElse/ExclusiveOr 反射相关 Constant Property Field Call 数组相关 ArrayAccess/ArrayIndex/ArrayLength/ ExpressionVisitor 若表达式为加法，改为减法 public class OperationsVisitor : ExpressionVisitor { public Expression Modify(Expression expression) { return Visit(expression); } protected override Expression VisitBinary(BinaryExpression b) { if (b.NodeType == ExpressionType.Add) { Expression left = this.Visit(b.Left); Expression right = this.Visit(b.Right); return Expression.Subtract(left,right); } return base.VisitBinary(b); } } a+b*2 变为 a-b*x Expression<Func<int, int, int>> lambda = (a, b) => a + b * 2; var operationsVisitor = new OperationsVisitor(); Expression modifyExpression = operationsVisitor.Modify(lambda); Console.WriteLine(modifyExpression.ToString()); IQueryable http://kb.cnblogs.com/page/42510/ EF中查询表达式（linq for sql 或 lamdba）返回的结果是IQueryable public interface IQueryable : IEnumerable { Type ElementType {get;} Expression Expression {get;} IQueryProvider Provider {get;} } public interface IQueryable<T> : IQueryable, IEnumerable<T>, IEnumerable { } public interface IQueryProvider { IQueryable CreateQuery(Expressiong expression); object Execute(Expression expression); IQueryable<T> CreateQuery<T>(Expression expression)' T Execute<T>(Expression expression); } 查询数组 List<String> myList = new List<String>() { "a", "ab", "cd", "bd" }; IEnumerable<String> query = from s in myList where s.StartsWith("a") select s; foreach (string s in query) ....; Expression in linq to sql select contactName IQueryable<Customer> custs =db.Customers; ParameterExpression param = Expression.Parameter(typeof (Customer), "c"); Expression selector =Expression.Property(param, typeof (Customer).GetProperty("ContactName")); Expression pred =Expression.Lambda(selector, param); Expression expr = Expression.Call(typeof(Queryable), "Select", new Type[] { typeof (Customer), typeof(string) }, Expression.Constant(custs), pred); IQueryable<string> query = db.Customers.AsQueryable().Provider.CreateQuery<string>(expr); 生成的 SQL语句为： SELECT [t0].[ContactName] FROM [dbo]. [Customers]AS [t0] ---------------------------------------------------------------- Where IQueryable<Customer> custs = db.Customers; ParameterExpression param = Expression.Parameter(typeof(Customer), "c"); Expression left = Expression.Property(param, typeof(Customer).GetProperty("City")); //c.City=="London" Expression right = Expression.Constant("London"); Expression filter = Expression.Equal(left, right); Expression pred = Expression.Lambda(filter, param); Expression expr = Expression.Call(typeof(Queryable), "Where", new Type[] { typeof(Customer) }, Expression.Constant(custs), pred); //Where(c=>c.City=="London") IQueryable<Customer> query = db.Customers.AsQueryable().Provider.CreateQuery<Customer>(expr); 生成的SQL语句为： SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax] FROM [dbo].[Customers] AS [t0] WHERE [t0].[City] = @p0
转载请注明出处：http://surfsky.cnblogs.com
