使用VS2017创建EF框架实例

本文例子中使用环境：vs2017，sql server 2008

 一，创建EF项目

1，解决方案添加.Net Framework类库项目，在新建的项目下新建项：data->ADO.NET实体数据模型->来自数据库的EF设计器，填写数据库连接，选择包含的内容，完成创建。

       

 

二，创建接口及方法封装

此过程可参考文章《关于EF 通用增删改查的封装》，这里复制下代码。

using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Data;
using MySql.Data.MySqlClient;
/************************************************
◇作者： LowKeyC
◇说明： 定义一个EF通用的CRUD的接口
◇版本号：V1.0
◇创建日期：2017年6月22日  星期四
*****************************************************/

namespace EFCommon.SqlHelp
{
    public interface IRepository : IDisposable
    {

        /// <summary>
        /// 添加实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        bool Add<T>(T Entity) where T : class;

        /// <summary>
        /// 批量的进行添加实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        bool AddRange<T>(List<T> Entity) where T : class;


        /// <summary>
        /// 删除单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        bool Delete<T>(T Entity) where T : class;

        /// <summary>
        /// 根据查询条件进行删除单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereLambda"></param>
        /// <returns></returns>
        bool Delete<T>(Expression<Func<T, bool>> whereLambda) where T : class;
         

        /// <summary>
        ///单个对象的修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity">需要修改的对象</param>
        /// <returns></returns>
        bool Update<T>(T Entity) where T : class;


        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereLambda"></param>
        /// <param name="updateLambda"></param>
        /// <returns></returns>
        bool Update<T>(Expression<Func<T, bool>> WhereLambda, Expression<Func<T, T>> UpdateLambda) where T : class;


        /// <summary>
        /// 批量的修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        bool Update<T>(List<T> Entity) where T : class;


        /// <summary>
        /// 批量统一的进行更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model">需要修改的对象实体</param>
        /// <param name="WhereLambda">查询的条件</param>
        /// <param name="ModifiedProNames"></param>
        /// <returns></returns>
        bool Update<T>(T model, Expression<Func<T, bool>> WhereLambda, params string[] ModifiedProNames) where T : class;


        /// <summary>
        /// 根据主键进行查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ID"></param>
        /// <returns></returns>
        T FindByID<T>(dynamic ID) where T : class;

        /// <summary>
        /// 默认查询选择第一条数据,没有那么进行返回NULL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns>返回bool</returns>
        T GetFristDefault<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class;

        /// <summary>
        /// 查询所有的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        List<T> GetAll<T>(string Order = null) where T : class;

        /// <summary>
        /// 含有带条件的查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        List<T> GetAllQuery<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class;


        /// <summary>
        ///获取查询的数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        int GetCount<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class;

        /// <summary>
        /// 判断对象是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        bool GetAny<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class;


        /// <summary>
        /// 根据查询过条件进行分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TKey"></typeparam>
        /// <param name="PageIndex">当前页面</param>
        /// <param name="PageSize">页面的大小</param>
        /// <param name="TotalCount">总记录数</param>
        /// <param name="OrderBy">排序的条件</param>
        /// <param name="WhereLambda">查询条件</param>
        /// <param name="IsOrder">是否正序</param>
        /// <returns></returns>
        List<T> Pagination<T, TKey>(int PageIndex, int PageSize, out int TotalCount, Expression<Func<T, TKey>> OrderBy, Expression<Func<T, bool>> WhereLambda = null, bool IsOrder = true) where T : class;


        /// <summary>
        /// 根据查询条件进行做分页查询
        /// </summary>
        /// <typeparam name="T">查询的对象</typeparam>
        /// <param name="PageIndex">当前的页码</param>
        /// <param name="PageSize">每页的大小</param>
        /// <param name="TotalCount">总页数</param>
        /// <param name="ordering">排序条件</param>
        /// <param name="WhereLambda">查询条件</param>
        /// <returns></returns>
        List<T> Pagination<T>(int PageIndex, int PageSize, out int TotalCount, string ordering, Expression<Func<T, bool>> WhereLambda = null) where T : class;


        /// <summary>
        /// 根据查询条件进行转化
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        List<T> GetSelect<T>(Expression<Func<T, bool>> WhereLambda) where T : class;


        /// <summary>
        /// 执行存储过程或自定义sql语句--返回集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Sql"></param>
        /// <param name="Parms"></param>
        /// <param name="CmdType"></param>
        /// <returns></returns>
        List<T> QueryPro<T>(string Sql, List<MySqlParameter> Parms, CommandType CmdType = CommandType.Text) where T : class;


        /// <summary>
        /// 回滚
        /// </summary>
        /// <typeparam name="T"></typeparam>
        void RollBackChanges<T>() where T : class;

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Data.Entity;
using System.Linq.Expressions;
using System.Data;
using System.Linq.Dynamic;
using EntityFramework.Extensions;
using System.Reflection;
using System.Data.Entity.Infrastructure;
using MySql.Data.MySqlClient;
/************************************************
◇作者： LowKeyC  需要引用这个程序集:EntityFramework.Extended.6.1.0.168
◇说明： 实现EF通用的CRUD通用的接口  
◇版本号：V1.0   
◇创建日期：2017年6月22日 星期四
*****************************************************/
namespace EFCommon.SqlHelp
{
    public class Repository : IRepository, IDisposable
    {

        private readonly static  DbContext _DbContextHandle =new ahavadbEntities();//此处进行调用EF的DBContent 的实体类或者通过工厂化模式来进行调用。

        /// <summary>
        /// 添加一个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        public bool Add<T>(T Entity) where T : class
        {
            using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
            {
                _DbContextHandle.Set<T>().Add(Entity);
                int Count = _DbContextHandle.SaveChanges();
                Ts.Complete();
                return Count > 0;
            }
        }

        /// <summary>
        /// 批量的插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        public bool AddRange<T>(List<T> Entity) where T : class
        {
            using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
            {
                _DbContextHandle.Set<T>().AddRange(Entity);
                int Count = _DbContextHandle.SaveChanges();
                Ts.Complete();
                return Count > 0;
            }
        }

        /// <summary>
        /// 根据查询条件进行删除对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereLambda">查询条件</param>
        /// <returns></returns>
        public bool Delete<T>(Expression<Func<T, bool>> whereLambda) where T : class
        {
            using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
            {
                var EntityModel = _DbContextHandle.Set<T>().Where(whereLambda).FirstOrDefault();
                if (EntityModel != null)
                {
                    _DbContextHandle.Set<T>().Remove(EntityModel);
                    int Count = _DbContextHandle.SaveChanges();
                    Ts.Complete();
                    return Count > 0;
                }
                return false;
            }
        }


        /// <summary>
        /// 删除单个对象的实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity">实体对象</param>
        /// <returns></returns>
        public bool Delete<T>(T Entity) where T : class
        {
            using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
            {
                _DbContextHandle.Set<T>().Attach(Entity);
                _DbContextHandle.Set<T>().Remove(Entity);
                int Count = _DbContextHandle.SaveChanges();
                Ts.Complete();
                return Count > 0;
            }
        }

        /// <summary>
        /// 批量的进行更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        public bool Update<T>(List<T> Entity) where T : class
        {
            int Count = 0;
            using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
            {
                if (Entity != null)
                {
                    foreach (var items in Entity)
                    {
                        var EntityModel = _DbContextHandle.Entry(Entity);
                        _DbContextHandle.Set<T>().Attach(items);
                        EntityModel.State = EntityState.Modified;
                    }

                }
                Count = _DbContextHandle.SaveChanges();
                Ts.Complete();
            }

            return Count > 0;
        }


        /// <summary>
        /// 进行修改单个实体对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity">实体对象</param>
        /// <returns></returns>
        public bool Update<T>(T Entity) where T : class
        {
            using (TransactionScope Ts = new TransactionScope())
            {
                var EntityModel = _DbContextHandle.Entry<T>(Entity);
                _DbContextHandle.Set<T>().Attach(Entity);
                EntityModel.State = EntityState.Modified;
                int Count = _DbContextHandle.SaveChanges();
                Ts.Complete();
                return Count > 0;
            }
        }

        /// <summary>
        /// 批量的修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <param name="UpdateLambda"></param>
        /// <returns></returns>
        public bool Update<T>(Expression<Func<T, bool>> WhereLambda, Expression<Func<T, T>> UpdateLambda) where T : class
        {
            _DbContextHandle.Set<T>().Where(WhereLambda).Update<T>(UpdateLambda);
            return _DbContextHandle.SaveChanges() > 0;
        }


        /// <summary>
        /// 查询条件进行修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="WhereLambda"></param>
        /// <param name="ModifiedProNames"></param>
        /// <returns></returns>
        public bool Update<T>(T model, Expression<Func<T, bool>> WhereLambda, params string[] ModifiedProNames) where T : class
        {
            //查询要修改的数据
            List<T> ListModifing = _DbContextHandle.Set<T>().Where(WhereLambda).ToList();
            Type t = typeof(T);
            List<PropertyInfo> ProInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            Dictionary<string, PropertyInfo> DitProList = new Dictionary<string, PropertyInfo>();
            ProInfos.ForEach(p =>
            {
                if (ModifiedProNames.Contains(p.Name))
                {
                    DitProList.Add(p.Name, p);
                }
            });

            if (DitProList.Count <= 0)
            {
                throw new Exception("指定修改的字段名称有误或为空");
            }
            foreach (var item in DitProList)
            {
                PropertyInfo proInfo = item.Value;
                object newValue = proInfo.GetValue(model, null);
                //批量进行修改相互对应的属性
                foreach (T oModel in ListModifing)
                {
                    proInfo.SetValue(oModel, newValue, null);//设置其中新的值
                }
            }

            return _DbContextHandle.SaveChanges() > 0;
        } 
        /// <summary>
        /// 释放缓存
        /// </summary>
        public void Dispose()
        {
            _DbContextHandle.Dispose();
        }

        /// <summary>
        /// 查询单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ID">主键ID</param>
        /// <returns></returns>
        public T FindByID<T>(dynamic ID) where T : class
        {
            return _DbContextHandle.Set<T>().Find(ID) ?? null;
        }


        /// <summary>
        /// 获取全部数据的列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Order">排序</param>
        /// <returns></returns>
        public List<T> GetAll<T>(string Order = null) where T : class
        {
            return Order != null ? _DbContextHandle.Set<T>().OrderBy(Order).ToList() ?? null : _DbContextHandle.Set<T>().ToList() ?? null;
        }

        /// <summary>
        ///根据查询条件进行查询列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        public List<T> GetAllQuery<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class
        {
            return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).ToList() ?? null : _DbContextHandle.Set<T>().ToList() ?? null;
        }

        /// <summary>
        ///判断对象是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        public bool GetAny<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class
        {
            return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).Any() : _DbContextHandle.Set<T>().Any();
        }

        /// <summary>
        /// 获取查询条件的记录数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        public int GetCount<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class
        {
            return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).Count() : _DbContextHandle.Set<T>().Count();
        }


        /// <summary>
        /// 获取单条的记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        public T GetFristDefault<T>(Expression<Func<T, bool>> WhereLambda = null) where T : class
        {
            return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).FirstOrDefault() ?? null : _DbContextHandle.Set<T>().FirstOrDefault() ?? null;
        }


        /// <summary>
        /// 查询对象的转化
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="WhereLambda"></param>
        /// <returns></returns>
        public List<T> GetSelect<T>(Expression<Func<T, bool>> WhereLambda) where T : class
        {
            return _DbContextHandle.Set<T>().Where(WhereLambda).ToList() ?? null;
        }

        /// <summary>
        ///根据查询条件进行分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页的大小</param>
        /// <param name="TotalCount">总记录数</param>
        /// <param name="ordering">排序条件</param>
        /// <param name="WhereLambda">查询条件</param>
        /// <returns></returns>
        public List<T> Pagination<T>(int PageIndex, int PageSize, out int TotalCount, string Ordering, Expression<Func<T, bool>> WhereLambda = null) where T : class
        {
            //分页的时候一定要注意 Order 一定在Skip 之前
            var QueryList = _DbContextHandle.Set<T>().OrderBy(Ordering);
            if (WhereLambda != null)
            {
                QueryList = QueryList.Where(WhereLambda);
            }

            TotalCount = QueryList.Count();
            return QueryList.Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList() ?? null;
        }

        /// <summary>
        ///根据查询条件进行分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页的大小</param>
        /// <param name="TotalCount">总记录数</param>
        /// <param name="OrderBy">排序条件</param>
        /// <param name="WhereLambda">查询的条件</param>
        /// <param name="IsOrder"></param>
        /// <returns></returns>
        public List<T> Pagination<T, TKey>(int PageIndex, int PageSize, out int TotalCount, Expression<Func<T, TKey>> OrderBy, Expression<Func<T, bool>> WhereLambda = null, bool IsOrder = true) where T : class
        {
            //分页的时候一定要注意 Order一定在Skip 之前
            IQueryable<T> QueryList = IsOrder == true ? _DbContextHandle.Set<T>().OrderBy(OrderBy) : _DbContextHandle.Set<T>().OrderByDescending(OrderBy);

            if (WhereLambda != null)
            {
                QueryList = QueryList.Where(WhereLambda);
            }

            TotalCount = QueryList.Count();
            return QueryList.Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList() ?? null;
        }


        /// <summary>
        /// 执行存储过程的SQL 语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Sql">执行的SQL语句</param>
        /// <param name="Parms">SQL 语句的参数</param>
        /// <param name="CmdType"></param>
        /// <returns></returns>
        public List<T> QueryPro<T>(string Sql, List<MySqlParameter> Parms, CommandType CmdType = CommandType.Text) where T : class
        {
            //进行执行存储过程
            if (CmdType == CommandType.StoredProcedure)
            {
                StringBuilder paraNames = new StringBuilder();
                foreach (var item in Parms)
                {
                    paraNames.Append($" @{item},");
                }
                Sql = paraNames.Length > 0 ? $"exec {Sql} {paraNames.ToString().Trim(',')}" : $"exec {Sql} ";
            }
            return _DbContextHandle.Set<T>().SqlQuery(Sql, Parms.ToArray()).ToList();
        }


        /// <summary>
        /// 进行回滚
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public void RollBackChanges<T>() where T : class
        {
            var Query = _DbContextHandle.ChangeTracker.Entries().ToList();

            Query.ForEach(p => p.State = EntityState.Unchanged);
        }

    }
}

因本人使用的是sql server，因此修改这部分的mysql，改用System.Data.SqlClient。采用上面的代码，应该会报错在OrderBy(string),因为构建的是LambdaExpression 类型的，这位大神给出了很好的解决方案，一起去看看吧——c# 扩展方法 奇思妙用 高级篇 九：OrderBy(string propertyName, bool desc)

三，使用注意事项

1，需要把EF的配置拷贝到主程序配置里。

2，在主程序配置中添加entityFramework的节点配置

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb"/>
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
    </providers>
  </entityFramework>

3，在主程序中NuGet包中添加下EntityFramework