ORM框架学习之EF

---

首先推荐一篇很好的EF文章翻译，可以系统的学习一遍。
《Entity Framework 6 Recipes》中文翻译系列

EF使用体会

优点：

• 可以省去Ado.net复杂的管道连接代码。
• 通过简单的引用就能完成对数据库的连接和开发。
• 可以省去一些SQL语句的编写，对一些底层可能需要适配多数据库提供了一些方便。

缺点：

• 学习成本较高，如果不怎么懂SQL，索引的一些数据库知识，也写不太好相对优化的Linq查询，所有EF既要懂Sql又懂Linq是最好的。
• 比较大，EF的dll默认是连接SqlServer若要连接其它数据库引用其它数据库的DLL加起来10几M，而像Dapper这样的轻量级框架才100多K。
• 查询只支持数据结果对实体的转换，不支持DataTable。

资料汇总

EF连接

连接数据库的多种方法

EF配置

EF的霸气配置秒杀一切

EF进阶

1.学习LINQ To Sql

LINQ To Sql语法汇总

2.学习Expression

LINQ Expression高级篇

3.优化测试

EF查询百万级数据的性能测试

简单的类库

项目如图：

Data.Map中放相关的EntityTypeConfiguration
Data.Module中相关的实体

这里贴一下EFContext和EFDatabase的类

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;


namespace Data.EF
{
    //[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]  Mysql数据库配置
    public class EFContext : DbContext, IDisposable
    {
        public EFContext(string dbName)
            : base(dbName)
        {
            Database.SetInitializer<EFContext>(null);
            this.Configuration.AutoDetectChangesEnabled = false;
            this.Configuration.ValidateOnSaveEnabled = false;
            this.Configuration.LazyLoadingEnabled = false;
            this.Configuration.ProxyCreationEnabled = false;
        }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            string assembleFileName = Assembly.GetExecutingAssembly().CodeBase.Replace("Data.EF.DLL", "Data.Map.dll").Replace("file:///", "");
            Assembly asm = Assembly.LoadFile(assembleFileName);
            var typesToRegister = asm.GetTypes()
            .Where(type => !String.IsNullOrEmpty(type.Namespace))
            .Where(type => type.BaseType != null && type.BaseType.IsGenericType && type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
            foreach (var type in typesToRegister)
            {
                dynamic configurationInstance = Activator.CreateInstance(type);
                modelBuilder.Configurations.Add(configurationInstance);
            }
            base.OnModelCreating(modelBuilder);
        }
    }

}

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace Data.EF
{
    public class EFDatabase
    {
        /// <summary>
        /// 返回字符集
        /// </summary>
        /// <typeparam name="Table"></typeparam>
        /// <returns></returns>
        public DbSet<Table> Set<Table>() where Table : class
        {
            return dbcontext.Set<Table>();
        }

        #region 属性
        /// <summary>
        /// 获取 当前使用的数据访问上下文对象
        /// </summary>
        public DbContext dbcontext { get; set; }
        /// <summary>
        /// 事务对象
        /// </summary>
        public DbTransaction dbTransaction { get; set; }

        #endregion

        #region 构造函数

        public EFDatabase(string dataBase)
        {
            dbcontext = new EFContext(dataBase);
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 获取实体类键值（缓存）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        private static Hashtable GetPropertyInfo<T>(T entity)
        {
            Type type = entity.GetType();
            object CacheEntity = null;
            if (CacheEntity == null)
            {
                Hashtable ht = new Hashtable();
                PropertyInfo[] props = type.GetProperties();
                foreach (PropertyInfo prop in props)
                {
                    string name = prop.Name;
                    object value = prop.GetValue(entity, null);
                    ht[name] = value;
                }
                return ht;
            }
            else
            {
                return (Hashtable)CacheEntity;
            }
        }

        /// <summary>
        /// 存储过程语句
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="dbParameter">执行命令所需的sql语句对应参数</param>
        /// <returns></returns>
        private static string BuilderProc(string procName, params DbParameter[] dbParameter)
        {
            StringBuilder strSql = new StringBuilder("exec " + procName);
            if (dbParameter != null)
            {
                foreach (var item in dbParameter)
                {
                    strSql.Append(" " + item + ",");
                }
                strSql = strSql.Remove(strSql.Length - 1, 1);
            }
            return strSql.ToString();
        }

        private int Delete<T>(T entity) where T : class
        {
            dbcontext.Set<T>().Attach(entity);
            dbcontext.Set<T>().Remove(entity);
            return dbTransaction == null ? dbcontext.SaveChanges() : 0;
        }

        private int Delete<T>(IEnumerable<T> entities) where T : class
        {
            foreach (var entity in entities)
            {
                dbcontext.Set<T>().Attach(entity);
                dbcontext.Set<T>().Remove(entity);
            }
            return dbTransaction == null ? dbcontext.SaveChanges() : 0;
        }

        #endregion

        public EFDatabase BeginTrans()
        {
            DbConnection dbConnection = ((IObjectContextAdapter)dbcontext).ObjectContext.Connection;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            dbTransaction = dbConnection.BeginTransaction();
            return this;
        }

        public int Commit()
        {
            try
            {
                int returnValue = dbcontext.SaveChanges();
                if (dbTransaction != null)
                {
                    dbTransaction.Commit();
                    this.Close();
                }
                return returnValue;
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null )
                {
                    throw ex.InnerException;
                }
                throw;
            }
            finally
            {
                if (dbTransaction == null)
                {
                    this.Close();
                }
            }
        }

        public void Rollback()
        {
            this.dbTransaction.Rollback();
            this.dbTransaction.Dispose();
            this.Close();
        }

        public void Close()
        {
            dbcontext.Dispose();
        }

        public int ExecuteBySql(string strSql)
        {
            dbcontext.Database.CommandTimeout = 60;
            return dbcontext.Database.ExecuteSqlCommand(strSql);
        }

        public int ExecuteBySql(string strSql, params object[] dbParameter)
        {
            dbcontext.Database.CommandTimeout = 60;
            return dbcontext.Database.ExecuteSqlCommand(strSql, dbParameter);
        }

        public int ExecuteByProc(string procName)
        {
              return dbcontext.Database.ExecuteSqlCommand(BuilderProc(procName));
        }

        public int ExecuteByProc(string procName, System.Data.Common.DbParameter[] dbParameter)
        {
              return dbcontext.Database.ExecuteSqlCommand(BuilderProc(procName, dbParameter), dbParameter);
        }

        public T FindEntity<T>(object keyValue) where T : class
        {
            return dbcontext.Set<T>().Find(keyValue);
        }

        public T FindEntity<T>(Expression<Func<T, bool>> condition) where T : class, new()
        {
              return dbcontext.Set<T>().Where(condition).FirstOrDefault();
        }

        public IQueryable<T> FindList<T>(Expression<Func<T, bool>> condition) where T : class, new()
        {
            return dbcontext.Set<T>().Where(condition);
        }

        public IQueryable<T> FindList<T>(string strSql) where T : class
        {
            return dbcontext.Set<T>().SqlQuery(strSql).AsQueryable();
        }

        public IQueryable<T> FindList<T>(string strSql, DbParameter[] dbParameter) where T : class
        {
            return dbcontext.Set<T>().SqlQuery(strSql, dbParameter).AsQueryable();
        }

        public int Delete<T>(object propertyValue, string propertyName) where T : class
        {
              string TableName = typeof(T).Name;
              return this.ExecuteBySql("delete from " + TableName + " where " + propertyName + " = {0}", propertyValue);
        }

        public int Delete<T>(Expression<Func<T, bool>> condition) where T : class, new()
        {
            IEnumerable<T> entities = dbcontext.Set<T>().Where(condition).ToList();
            return entities.Count() > 0 ? Delete(entities) : 0;
        }

        public int Update<T>(T entity) where T : class
        {
            dbcontext.Set<T>().Attach(entity);
            Hashtable props = GetPropertyInfo<T>(entity);
            foreach (string item in props.Keys)
            {
                object value = dbcontext.Entry(entity).Property(item).CurrentValue;
                if (value != null)
                {
                    if (value.ToString() == "&nbsp;"||value.ToString().Trim()=="")
                        dbcontext.Entry(entity).Property(item).CurrentValue = null;
                    dbcontext.Entry(entity).Property(item).IsModified = true;
                }
            }
            return dbTransaction == null ? this.dbcontext.SaveChanges() : 0;
        }

        public int Update<T>(IEnumerable<T> entities) where T : class
        {
            foreach (var entity in entities)
            {
                this.Update(entity);
            }
            return dbTransaction == null ? this.dbcontext.SaveChanges() : 0;
        }

    }
}