EF 学习笔记
1、一次性添加映射
1 public class EntityDbContext : DbContext 2 { 3 public EntityDbContext() 4 : base("name=test2") 5 { } 6 7 8 /// <summary> 9 /// 通过反射一次性将表进行映射 10 /// </summary> 11 /// <param name="modelBuilder"></param> 12 protected override void OnModelCreating(DbModelBuilder modelBuilder) 13 { 14 15 var typesRegister = Assembly.GetExecutingAssembly().GetTypes() 16 .Where(type => !(string.IsNullOrEmpty(type.Namespace))).Where(type => type.BaseType != null && type.BaseType.IsGenericType && type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>)); 17 foreach (var type in typesRegister) 18 { 19 dynamic configurationInstance = Activator.CreateInstance(type); 20 modelBuilder.Configurations.Add(configurationInstance); 21 } 22 23 } 24 }
1 public class StudentMap : EntityTypeConfiguration<Student> 2 { 3 public StudentMap() 4 { 5 ToTable("Student"); 6 HasKey(d => d.ID); 7 8 //HasRequired(p => p.Course).WithRequiredDependent(i => i.Student); 9 //HasRequired(p => p.Course).WithOptional(i => i.Student); 10 11 HasRequired(p => p.Course).WithRequiredPrincipal(p => p.Student); 12 HasOptional(p => p.Course).WithRequired(p => p.Student); 13 14 /* 15 对于上述映射关系不太理解的话可以去上述给出链接文章。我只说明怎么去很好的理解这两组的意思，第一组 WithRequiredDependent 和第二组 16 WithRequiredPrincipal 一个是Dependent是依赖的意思说明后面紧接着的Student是依赖对象，而前面的Course是主体，而Principal 17 首先的意思，说明后面紧接着的是Student是主体，而Course是依赖对象。很显然在这个关系中课程是依赖学生的。所以映射选第二组 18 */ 19 } 20 }
2、延迟加载必须满足三个条件：
- this.Configuration.ProxyCreationEnabled = true;
- this.Configuration.LazyLoadingEnabled = true;
- 导航属性修饰符必须为Virtual
3、映射private/protected/internal属性：
1 public class Student 2 { 3 public string Id { get; set; } 4 public string Name { get; set; } 5 private string TestPrivate { get; set; } 6 internal string TestInternal { get; set; } 7 protected string TestProtected { get; set; } 8 ｝
1 public class BreakAwayContext : DbContext 2 { 3 public BreakAwayContext() 4 : base("name=BreakAwayContext") 5 { 6 this.Configuration.LazyLoadingEnabled = false;//对所有实体关闭惰性加载 7 this.Configuration.ProxyCreationEnabled = false;//禁止创建代理 8 9 DbInterception.Add(new NLogCommandInterceptor()); 10 } 11 12 protected override void OnModelCreating(DbModelBuilder modelBuilder) 13 { 14 ////<EF 6.0 15 //modelBuilder.Entities().Configure(c => 16 //{ 17 // var nonPublicProperties = c.ClrType.GetProperties(BindingFlags.NonPublic | BindingFlags.Instance); 18 19 // foreach (var p in nonPublicProperties) 20 // { 21 // c.Property(p).HasColumnName(p.Name); 22 // } 23 //}); 24 //EF 6.0 25 modelBuilder.Types().Configure(d => 26 { 27 var nonPublicProperties = d.ClrType.GetProperties(BindingFlags.NonPublic | BindingFlags.Instance); 28 foreach (var p in nonPublicProperties) 29 { 30 d.Property(p).HasColumnName(p.Name); 31 } 32 }); 33 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 34 } 35 36 public DbSet<Student> Students { get; set; } 37 38 }
4、各种查询：
条件查询
1 var sql = "select ID, Name, Age from Student where Name = @Name and Age = @Age"; 2 3 ctx.Database.SqlQuery<Student>( 4 sql, 5 new SqlParameter("@Name", Name), 6 new SqlParameter("@Age", Age));
非实体查询
ctx.Database.SqlQuery<int>("select Age from Student").ToList();
执行存储过程
ctx.Database.SqlQuery<Student>("dbo.GetList").ToList();
var param = new SqlParameter("Age", 5); var list = ctx.Database.SqlQuery<Student>("dbo.GetList @Age", param).ToList(); /*查询出的所有列必须对应返回实体中的所有字段，缺一不可，否则报错！在调用存储过程中，如果数据库是Sql 2005要在存储过程名称前加上 EXEC,否则报错*/
var name = new SqlParameter { ParameterName = "Name", Value = Name };
var currentpage = new SqlParameter { ParameterName = "PageIndex", Value = currentPage };
var pagesize = new SqlParameter { ParameterName = "PageSize", Value = pageSize };
var totalcount = new SqlParameter { ParameterName = "TotalCount", Value = 0, Direction = ParameterDirection.Output };
var list = ctx.Database.SqlQuery<Student>("Myproc @Name, @PageIndex, @PageSize, @TotalCount output",
name, currentpage, pagesize, totalcount);
totalCount = (int)totalcount.Value; /*获得要输出参数totalcount的值*/
1 public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : class 2 { 3 if (parameters != null && parameters.Length > 0) 4 { 5 for (int i = 0; i <= parameters.Length - 1; i++) 6 { 7 var p = parameters[i] as DbParameter; 8 if (p == null) 9 throw new Exception("Not support parameter type"); 10 11 commandText += i == 0 ? " " : ", "; 12 13 commandText += "@" + p.ParameterName; 14 if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output) 15 { 16 17 commandText += " output"; 18 } 19 } 20 } 21 22 var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList(); 23 24 25 bool acd = this.Configuration.AutoDetectChangesEnabled; 26 27 try 28 { 29 this.Configuration.AutoDetectChangesEnabled = false; 30 31 for (int i = 0; i < result.Count; i++) 32 result[i] = this.Set<TEntity>().Attach(result[i]); 33 } 34 finally 35 { 36 this.Configuration.AutoDetectChangesEnabled = acd; 37 } 38 39 return result; 40 }
var list = ctx.ExecuteStoredProcedureList<Student>("Myproc", pageindex, pagesize, totalcount);
5、EF在查询之前进行操作来忽略尾随空格
1 public class EFConfiguration : DbConfiguration 2 { 3 public EFConfiguration() 4 { 5 AddInterceptor(new StringTrimmerInterceptor()); 6 } 7 } 8 public class StringTrimmerInterceptor : IDbCommandTreeInterceptor 9 { 10 public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) 11 { 12 if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace) 13 { 14 var queryCommand = interceptionContext.Result as DbQueryCommandTree; 15 if (queryCommand != null) 16 { 17 var newQuery = queryCommand.Query.Accept(new StringTrimmerQueryVisitor()); 18 interceptionContext.Result = new DbQueryCommandTree( 19 queryCommand.MetadataWorkspace, 20 queryCommand.DataSpace, 21 newQuery); 22 } 23 } 24 } 25 26 private class StringTrimmerQueryVisitor : DefaultExpressionVisitor 27 { 28 private static readonly string[] _typesToTrim = { "nvarchar", "varchar", "char", "nchar" }; 29 30 public override DbExpression Visit(DbNewInstanceExpression expression) 31 { 32 var arguments = expression.Arguments.Select(a => 33 { 34 var propertyArg = a as DbPropertyExpression; 35 if (propertyArg != null&& _typesToTrim.Contains(propertyArg.Property.TypeUsage.EdmType.Name)) 36 { 37 return EdmFunctions.Trim(a); 38 } 39 40 return a; 41 }); 42 43 return DbExpressionBuilder.New(expression.ResultType, arguments); 44 } 45 } 46 }
6、扩展方法
1 public static class EFExt 2 { 3 /// <summary> 4 /// 获得实体的所有键的名称 5 /// </summary> 6 /// <param name="ctx"></param> 7 /// <param name="entity"></param> 8 /// <returns></returns> 9 static string[] GetKeyNames(this DbContext ctx, Type entity) 10 { 11 var metadata = ((IObjectContextAdapter)ctx).ObjectContext.MetadataWorkspace; 12 13 /*获得CLR type集合和medata OSpace之间的映射*/ 14 var objItemCollection = (ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace); 15 16 /*根据所给的CLR type获得实体的元数据即metadata*/ 17 var entitydata = metadata.GetItems<EntityType>(DataSpace.OSpace).Single(e => objItemCollection.GetClrType(e) == entity); 18 /*返回实体的所有键的集合*/ 19 return entitydata.KeyProperties.Select(p => p.Name).ToArray(); 20 21 } 22 /// <summary> 23 /// 获得实体对应的表名 24 /// </summary> 25 /// <param name="ctx"></param> 26 /// <param name="entity"></param> 27 /// <returns></returns> 28 static string GetTableName(this DbContext ctx, Type entity) 29 { 30 var metadata = ((IObjectContextAdapter)ctx).ObjectContext.MetadataWorkspace; 31 32 var storeItemCollection = metadata.GetItemCollection(DataSpace.SSpace); 33 34 var entitySetBase = storeItemCollection.GetItems<EntityContainer>().Single().BaseEntitySets.Where(e => e.Name == entity.Name).Single(); 35 36 string tableName = entitySetBase.MetadataProperties["Schema"].Value + "." + entitySetBase.MetadataProperties["Table"].Value; 37 38 return tableName; 39 } 40 /// <summary> 41 /// 获得实体的所有导航属性 42 /// </summary> 43 /// <param name="ctx"></param> 44 /// <param name="entity"></param> 45 /// <returns></returns> 46 static IEnumerable<PropertyInfo> GetNavigationPrpoperties(this DbContext ctx, Type entity) 47 { 48 var metadata = ((IObjectContextAdapter)ctx).ObjectContext.MetadataWorkspace; 49 50 var entityType = metadata.GetItems(DataSpace.OSpace).Where(d => d.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(d => d.Name == entity.Name).Single(); 51 52 return (entityType.NavigationProperties.Select(d => entity.GetProperty(d.Name)).ToList()); 53 54 } 55 }
7、二级缓存【参考官方Second Level Cace for EntityFramework】
1 public class EFConfiguration : DbConfiguration 2 { 3 public EFConfiguration() 4 { 5 var transactionHandler = new CacheTransactionHandler(new InMemoryCache()); 6 7 AddInterceptor(transactionHandler); 8 9 var cachingPolicy = new CachingPolicy(); 10 11 Loaded += 12 (sender, args) => args.ReplaceService<DbProviderServices>( 13 (s, _) => new CachingProviderServices(s, transactionHandler, 14 cachingPolicy)); 15 16 } 17 18 }
