使用VS Code开发.NET Core 0009 - 仓储封装

一、创建 BaseRepository

   public class BaseRepository<TEntity, TKey> : IBaseRepository<TEntity, TKey> where TEntity : class, new () {

        private readonly DbSet<TEntity> _dbSet;
        public SecMonContext _dbContext { get; } = null;

        /// <summary>
        /// 连接字符串
        /// </summary>
        protected string _connectionString { get; set; }

        /// <summary>
        /// 数据库类型
        /// </summary>
        // private DatabaseType _dbType { get; set; }
        public BaseRepository (SecMonContext context) {
            _dbContext = context;
            _dbSet = _dbContext.Set<TEntity> ();
        }
        // public DatabaseFacade Database => _dbContext.Database;
        public IQueryable<TEntity> Entities => _dbSet.AsQueryable ().AsNoTracking ();
        public int SaveChanges () {
            return _dbContext.SaveChanges ();
        }
        public async Task<int> SaveChangesAsync () {
            return await _dbContext.SaveChangesAsync ();
        }
        public bool Any (Expression<Func<TEntity, bool>> whereLambda) {
            return _dbSet.Where (whereLambda).Any ();
        }

        #region 插入数据
        public bool Insert (TEntity entity, bool isSaveChange = true) {
            _dbSet.Add (entity);
            if (isSaveChange) {
                return SaveChanges () > 0;
            }
            return false;
        }
        public async Task<bool> InsertAsync (TEntity entity, bool isSaveChange = true) {
            _dbSet.Add (entity);
            if (isSaveChange) {
                return await SaveChangesAsync () > 0;
            }
            return false;
        }
        public bool Insert (List<TEntity> entitys, bool isSaveChange = true) {
            _dbSet.AddRange (entitys);
            if (isSaveChange) {
                return SaveChanges () > 0;
            }
            return false;
        }
        public async Task<bool> InsertAsync (List<TEntity> entitys, bool isSaveChange = true) {
            _dbSet.AddRange (entitys);
            if (isSaveChange) {
                return await SaveChangesAsync () > 0;
            }
            return false;
        }
        #endregion

        #region 删除
        public bool Delete (TEntity entity, bool isSaveChange = true) {
            _dbSet.Attach (entity);
            _dbSet.Remove (entity);
            return isSaveChange ? SaveChanges () > 0 : false;
        }
        public bool Delete (List<TEntity> entitys, bool isSaveChange = true) {
            entitys.ForEach (entity => {
                _dbSet.Attach (entity);
                _dbSet.Remove (entity);
            });
            return isSaveChange ? SaveChanges () > 0 : false;
        }

        public virtual async Task<bool> DeleteAsync (TEntity entity, bool isSaveChange = true) {

            _dbSet.Attach (entity);
            _dbSet.Remove (entity);
            return isSaveChange ? await SaveChangesAsync () > 0 : false;
        }
        public virtual async Task<bool> DeleteAsync (List<TEntity> entitys, bool isSaveChange = true) {
            entitys.ForEach (entity => {
                _dbSet.Attach (entity);
                _dbSet.Remove (entity);
            });
            return isSaveChange ? await SaveChangesAsync () > 0 : false;
        }
        #endregion

        #region 更新数据
        public bool Update (TEntity entity, bool isSaveChange = true, List<string> updatePropertyList = null) {
            if (entity == null) {
                return false;
            }
            _dbSet.Attach (entity);
            var entry = _dbContext.Entry (entity);
            if (updatePropertyList == null) {
                entry.State = EntityState.Modified; //全字段更新
            } else {

                updatePropertyList.ForEach (c => {
                    entry.Property (c).IsModified = true; //部分字段更新的写法
                });

            }
            if (isSaveChange) {
                return SaveChanges () > 0;
            }
            return false;
        }
        public bool Update (List<TEntity> entitys, bool isSaveChange = true) {
            if (entitys == null || entitys.Count == 0) {
                return false;
            }
            entitys.ForEach (c => {
                Update (c, false);
            });
            if (isSaveChange) {
                return SaveChanges () > 0;
            }
            return false;
        }
        public async Task<bool> UpdateAsync (TEntity entity, bool isSaveChange = true, List<string> updatePropertyList = null) {
            if (entity == null) {
                return false;
            }
            _dbSet.Attach (entity);
            var entry = _dbContext.Entry<TEntity> (entity);
            if (updatePropertyList == null) {
                entry.State = EntityState.Modified; //全字段更新
            } else {
                updatePropertyList.ForEach (c => {
                    entry.Property (c).IsModified = true; //部分字段更新的写法
                });

            }
            if (isSaveChange) {
                return await SaveChangesAsync () > 0;
            }
            return false;
        }
        public async Task<bool> UpdateAsync (List<TEntity> entitys, bool isSaveChange = true) {
            if (entitys == null || entitys.Count == 0) {
                return false;
            }
            entitys.ForEach (c => {
                _dbSet.Attach (c);
                _dbContext.Entry<TEntity> (c).State = EntityState.Modified;
            });
            if (isSaveChange) {
                return await SaveChangesAsync () > 0;
            }
            return false;
        }
        #endregion

        #region 查找
        public long Count (Expression<Func<TEntity, bool>> predicate = null) {
            if (predicate == null) {
                predicate = c => true;
            }
            return _dbSet.LongCount (predicate);
        }
        public async Task<long> CountAsync (Expression<Func<TEntity, bool>> predicate = null) {
            if (predicate == null) {
                predicate = c => true;
            }
            return await _dbSet.LongCountAsync (predicate);
        }
        public TEntity Get (TKey id) {
            if (id == null) {
                return default (TEntity);
            }
            return _dbSet.Find (id);
        }

        public TEntity Get (Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) {
            var data = isNoTracking ? _dbSet.Where (predicate).AsNoTracking () : _dbSet.Where (predicate);
            return data.FirstOrDefault ();
        }

        public async Task<TEntity> GetAsync (TKey id) {
            if (id == null) {
                return default (TEntity);
            }
            return await _dbSet.FindAsync (id);
        }
        public async Task<TEntity> GetAsync (Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) {
            var data = isNoTracking ? _dbSet.Where (predicate).AsNoTracking () : _dbSet.Where (predicate);
            return await data.FirstOrDefaultAsync ();
        }

        public async Task<List<TEntity>> GetListAsync (Expression<Func<TEntity, bool>> predicate = null, string ordering = "", bool isNoTracking = true) {
            var data = isNoTracking ? _dbSet.Where (predicate).AsNoTracking () : _dbSet.Where (predicate);
            if (!string.IsNullOrEmpty (ordering)) {
                // data = data.OrderByBatch(ordering);//TODO:
            }
            return await data.ToListAsync ();
        }
        public List<TEntity> GetList (Expression<Func<TEntity, bool>> predicate = null, string ordering = "", bool isNoTracking = true) {
            var data = isNoTracking ? _dbSet.Where (predicate).AsNoTracking () : _dbSet.Where (predicate);
            if (!string.IsNullOrEmpty (ordering)) {
                // data = data.OrderByBatch(ordering);//TODO:
            }
            return data.ToList ();
        }
        public async Task<IQueryable<TEntity>> LoadAsync (Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) {
            if (predicate == null) {
                predicate = c => true;
            }
            return await Task.Run (() => isNoTracking ? _dbSet.Where (predicate).AsNoTracking () : _dbSet.Where (predicate));
        }
        public IQueryable<TEntity> Load (Expression<Func<TEntity, bool>> predicate = null, bool isNoTracking = true) {
            if (predicate == null) {
                predicate = c => true;
            }
            return isNoTracking ? _dbSet.Where (predicate).AsNoTracking () : _dbSet.Where (predicate);
        }

        #endregion

        #region SQL语句
        public virtual void BulkInsert<T> (List<T> entities) { }
        public int ExecuteSql (string sql) {
            return _dbContext.Database.ExecuteSqlCommand (sql);
        }

        public Task<int> ExecuteSqlAsync (string sql) {
            return _dbContext.Database.ExecuteSqlCommandAsync (sql);
        }

        public int ExecuteSql (string sql, List<DbParameter> spList) {
            return _dbContext.Database.ExecuteSqlCommand (sql, spList.ToArray ());
        }

        public Task<int> ExecuteSqlAsync (string sql, List<DbParameter> spList) {
            return _dbContext.Database.ExecuteSqlCommandAsync (sql, spList.ToArray ());
        }

        public virtual DataTable GetDataTableWithSql (string sql) {
            throw new NotImplementedException ();
        }

        public virtual DataTable GetDataTableWithSql (string sql, List<DbParameter> spList) {
            throw new NotImplementedException ();
        }

        #endregion

        #region 自定义SQL执行
        public IQueryable<T> ExcuteQuery<T> (string sql, params object[] parameters) where T : class {

            return this._dbContext.Set<T> ().FromSqlRaw<T> (sql, parameters);
        }

        public int ExecuteSql2 (string sql) {

            return _dbContext.Database.ExecuteSqlRaw (sql);
        }

        #endregion

    }

二、创建 IBaseRepository

    public interface IBaseRepository<TEntity, TKey> where TEntity : class
    {
        //interface IRepository<TEntity,TKey> where TEntity : class
        #region 查找数据
        long Count(Expression<Func<TEntity, bool>> predicate = null);
        Task<long> CountAsync(Expression<Func<TEntity, bool>> predicate = null);

        TEntity Get(Expression<Func<TEntity, bool>> predicate, bool isNoTracking);
        Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> predicate, bool isNoTracking);


        Task<TEntity> GetAsync(TKey id);

        IQueryable<TEntity> Load(Expression<Func<TEntity, bool>> predicate, bool isNoTracking);
        Task<IQueryable<TEntity>> LoadAsync(Expression<Func<TEntity, bool>> predicate, bool isNoTracking);

        List<TEntity> GetList(Expression<Func<TEntity, bool>> predicate, string ordering, bool isNoTracking);
        Task<List<TEntity>> GetListAsync(Expression<Func<TEntity, bool>> predicate, string ordering, bool isNoTracking);

        #endregion

        #region 插入数据
        bool Insert(TEntity entity, bool isSaveChange);
        Task<bool> InsertAsync(TEntity entity, bool isSaveChange);
        bool Insert(List<TEntity> entitys, bool isSaveChange = true);
        Task<bool> InsertAsync(List<TEntity> entitys, bool isSaveChange);
        #endregion

        #region 删除(删除之前需要查询)
        bool Delete(TEntity entity, bool isSaveChange);
        bool Delete(List<TEntity> entitys, bool isSaveChange);
        Task<bool> DeleteAsync(TEntity entity, bool isSaveChange);
        Task<bool> DeleteAsync(List<TEntity> entitys, bool isSaveChange = true);
        #endregion

        #region 修改数据
        bool Update(TEntity entity, bool isSaveChange, List<string> updatePropertyList);
        Task<bool> UpdateAsync(TEntity entity, bool isSaveChange, List<string> updatePropertyList);
        bool Update(List<TEntity> entitys, bool isSaveChange);
        Task<bool> UpdateAsync(List<TEntity> entitys, bool isSaveChange);
        #endregion

        #region 执行Sql语句
        void BulkInsert<T>(List<T> entities);
        int ExecuteSql(string sql);
        Task<int> ExecuteSqlAsync(string sql);
        int ExecuteSql(string sql, List<DbParameter> spList);
        Task<int> ExecuteSqlAsync(string sql, List<DbParameter> spList);
        DataTable GetDataTableWithSql(string sql);

        DataTable GetDataTableWithSql(string sql, List<DbParameter> spList);
        #endregion
    }