EntityFramework 基类重写

/* 
 * ------------------------------------------------------------------------------
 * 
 * 创 建 者：F_Gang                     @2019
 * 
 * 创建日期：2019-08-27 17:21:31              
 * 
 * 机器名称：DESKTOP-QUSP01L
 * 
 * 版 本 号：4.0.30319.42000
 * 
 * 功能描述：
 * 
 * ------------------------------------------------------------------------------
 */

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using XinLianXin.Utility;

namespace MyProject.DAL
{
    /// <summary>
    /// EF DataAccessLayer 基类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class BaseicService<T> where T : class, new()
    {
        #region 基础
        /// <summary>
        /// 上下文
        /// </summary>
        protected readonly BaseDBContext dB;

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="db"></param>
        public BaseicService(BaseDBContext db)
        {
            dB = db;
        }

        public void Dispose()
        {
            dB.Dispose();
        }
        #endregion

        #region Add
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns>返回新增实体对象，考虑到使用新增主键编号</returns>
        public T AddEntity(T t)
        {
            dB.Entry<T>(t).State = EntityState.Added;
            if (dB.SaveChanges() > 0)
                return t;
            return null;
        }

        /// <summary>
        /// 批量增加多条数据到一张表（事务处理）
        /// </summary>
        /// <param name="ts">实体对象集合</param>
        /// <returns></returns>
        public bool AddEntity(IQueryable<T> ts)
        {
            dB.Set<T>().AddRange(ts);
            return dB.SaveChanges() > 0;
        }
        #endregion

        #region Modify
        /// <summary>
        /// 编辑一条数据，返回bool 类型
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool ModifyEntity(T t)
        {
            dB.Entry<T>(t).State = EntityState.Modified;
            return dB.SaveChanges() > 0;
        }

        /// <summary>
        /// 批量修改
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public bool ModifyEntity(IQueryable<T> ts)
        {
            ts.ForEachAsync(o => { dB.Entry<T>(o).State = EntityState.Modified; });
            return dB.SaveChanges() > 0;
        }

        /// <summary>
        /// 修改一条数据,会修改指定列的值
        /// </summary>
        /// <param name="t">要修改的实体对象</param>
        /// <param name="proNames">要修改的属性名称</param>
        /// <returns></returns>
        public bool ModifyEntity(T t, params string[] proNames)
        {
            DbEntityEntry<T> dbe = dB.Entry<T>(t);

            //先将所有属性状态标记为未修改
            dbe.State = EntityState.Unchanged;

            //将要修改的属性状态标记为修改
            proNames.ToList().ForEach(o => dbe.Property(o).IsModified = true);

            return dB.SaveChanges() > 0;
        }

        /// <summary>
        /// 根据条件批量修改指定的列
        /// </summary>
        /// <param name="t"></param>
        /// <param name="conditions">lambda</param>
        /// <param name="proNames"></param>
        /// <returns></returns>
        public bool ModifyEntity(T t, Expression<Func<T, bool>> conditions, params string[] proNames)
        {
            var entitys = dB.Set<T>().Where(conditions).ToList();
            PropertyInfo[] proinfos = t.GetType().GetProperties();
            List<PropertyInfo> list = new List<PropertyInfo>();
            proinfos.ToList().ForEach(o => { if (proNames.Contains(o.Name)) list.Add(o); });
            entitys.ForEach(c => { list.ForEach(o => { object value = o.GetValue(t, null); o.SetValue(c, value, null); }); });
            return dB.SaveChanges() > 0;
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除一个实体对象
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool DeleteEntity(T t)
        {
            dB.Entry<T>(t).State = EntityState.Deleted;
            return dB.SaveChanges() > 0;
        }

        /// <summary>
        /// 根据条件删除单个实体对象
        /// </summary>
        /// <param name="conditions">lambda</param>
        /// <returns></returns>
        public bool DeleteSingleObj(Expression<Func<T, bool>> conditions)
        {
            var obj = dB.Set<T>().FirstOrDefault(conditions);
            if (obj == null) throw new Exception("您要删除的内容已不存在");
            dB.Entry<T>(obj).State = EntityState.Deleted;
            return dB.SaveChanges() > 0;
        }

        /// <summary>
        /// 根据条件批量删除
        /// </summary>
        /// <param name="conditions">lambda</param>
        /// <returns></returns>
        public bool BatchDeletion(Expression<Func<T, bool>> conditions)
        {
            var datas = dB.Set<T>().Where(conditions).ToList();
            dB.Set<T>().RemoveRange(datas);
            return dB.SaveChanges() > 0;
        }
        #endregion

        #region Select
        /// <summary>
        /// 根据条件查询实体对象
        /// </summary>
        /// <param name="conditions">lambda</param>
        /// <returns></returns>
        public T GetEntityByQuery(Expression<Func<T, bool>> conditions)
        {
            return dB.Set<T>().FirstOrDefault(conditions);
        }

        /// <summary>
        /// 根据条件查询所有
        /// </summary>
        /// <param name="conditions">lambda</param>
        /// <returns></returns>
        public IQueryable<T> GetAll()
        {
            return dB.Set<T>().AsQueryable();
        }

        /// <summary>
        /// 根据条件查询所有
        /// </summary>
        /// <param name="conditions">lambda</param>
        /// <returns></returns>
        public IQueryable<T> GetListByQuery(Expression<Func<T, bool>> conditions)
        {
            return dB.Set<T>().Where(conditions).AsQueryable();
        }

        /// <summary>
        /// 根据条件查询列表
        /// </summary>
        /// <typeparam name="S">Sort fields</typeparam>
        /// <param name="conditions">lambda</param>
        /// <param name="orderByLambds"></param>
        /// <param name="isAsc">是否倒叙</param>
        /// <returns></returns>
        public IQueryable<T> GetListByQuery<TKey>(Expression<Func<T, bool>> conditions, Expression<Func<T, TKey>> orderByLambds, bool isAsc)
        {
            var temp = dB.Set<T>().Where(conditions);
            if (isAsc)
                return temp.OrderBy(orderByLambds).AsQueryable();
            else
                return temp.OrderByDescending(orderByLambds).AsQueryable();
        }

        #region 带分页查询
        /// <summary>
        /// 带分页查询
        /// </summary>
        /// <typeparam name="S"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions">查询条件</param>
        /// <param name="orderKey">排序字段</param>
        /// <param name="isAsc">是否倒叙</param>
        /// <param name="rows">返回总条数</param>
        /// <param name="totalPage">返回总页数</param>
        /// <returns></returns>
        public IQueryable<T> GetPageListByQuery<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> conditions, Expression<Func<T, TKey>> orderKey, bool isAsc, out int rows, out int totalPage)
        {
            var temp = dB.Set<T>().Where(conditions);
            rows = temp.Count();

            totalPage = GetTotalPage(rows, pageSize);

            if (isAsc)
                temp = temp.OrderBy(orderKey);
            else
                temp = temp.OrderByDescending(orderKey);

            temp = temp.Skip(pageSize * (pageIndex - 1)).Take(pageSize).AsQueryable();

            return temp;
        }
        #endregion

        #region 传统sql结合EF分页实现查询 fg 2019年8月28日
        /// <summary>
        /// 传统sql结合EF分页实现查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="where">条件</param>
        /// <param name="orderKey">排序字段</param>
        /// <param name="isAsc">是否倒叙</param>
        /// <param name="rows">返回总条数</param>
        /// <param name="totalPage">总页数</param>
        /// <returns></returns>
        public IQueryable<T> GetPageListByQuery(int pageIndex, int pageSize, string sql, string where, string orderKey, bool isAsc, out int rows, out int totalPage)
        {
            if (string.IsNullOrEmpty(sql))
                throw new Exception("sql 存在空内容，请核实后在尝试");

            sql = sql + " WHERE 1 = 1 " + where;
            if (!string.IsNullOrEmpty(orderKey))
                sql += " ORDER BY " + orderKey;
            if (!isAsc)
                sql += " DESC ";

            var temp = dB.Database.SqlQuery<T>(sql).AsQueryable();
            rows = temp.Count();
            totalPage = GetTotalPage(rows, pageSize);
            temp = temp.Skip(pageSize * (pageIndex - 1)).Take(pageSize);
            return temp;
        }

        #region 计算分页总页数
        /// <summary>
        /// 返回总页数
        /// </summary>
        /// <param name="rows">总统条数</param>
        /// <param name="pageSize">页大小</param>
        /// <returns></returns>
        private int GetTotalPage(int rows, int pageSize)
        {
            if (rows % pageSize == 0)
                return rows / pageSize;
            return rows / pageSize + 1;
        }
        #endregion
        #endregion
        #endregion

        #region 显式Tran 事务 
        /// <summary>
        /// 显式执行事务
        /// </summary>
        /// <param name="dics"></param>
        /// <returns></returns>
        public int ExeTran(IDictionary<string, DbParameter[]> dics)
        {
            int result = 0;
            string outmsg = string.Empty;
            DbConnection con = ((IObjectContextAdapter)dB).ObjectContext.Connection;
            using (DbTransaction tran = con.BeginTransaction())
            {
                try
                {
                    // 使用传统的执行事务的方法                
                    foreach (var dic in dics)
                    {
                        if (dic.Value != null)
                            result += dB.Database.ExecuteSqlCommand(dic.Key, dic.Value);
                        else
                            result += dB.Database.ExecuteSqlCommand(dic.Key, dic.Value);
                    }

                    tran.Commit();
                    return result;

                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    LogHelper.Default.WriteError(string.Format("事务执行失败：") + ex.Message +
                        string.Format("\n执行内容:" + JsonHelper.ToJSON(dics, out outmsg)));
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
        }
        #endregion
    }
}

 

自定义排序用法：

 　　　　string outmsg = string.Empty;

            try
            {
                int rows = 0;
                int totalPath = 0;
                //                              排序字段类型                  查询条件                排序条件                
                return newsDAL.GetPageListByQuery<string>(pageInde, pageSize, o => o.c_user_id == 1, o => o.zhaiyao, false, out rows, out totalPath).ToList();
            }
            catch (Exception ex)
            {
                outmsg = ex.Message;
                return null;
            }