Net6Demo_Api/Net6Demo_Api.../BaseBusiness...

using System.Data;
using System.Linq.Expressions;
using System.Linq.Dynamic.Core;
using EFCore.Sharding;
using Microsoft.EntityFrameworkCore;
using Net6Demo_Api.Util;
using Microsoft.Extensions.Caching.Distributed;
using System.Linq;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using System.Data.Common;
using MySqlConnector;
using Net6Demo_Api.Entity;

namespace Net6Demo_Api.Business
{
    /// <summary>
    /// 描述：业务处理基类
    /// </summary>
    /// <typeparam name="T">泛型约束（数据库实体）</typeparam>
    public abstract class BaseBusiness<T> where T : class, new()
    {
        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db">注入数据库</param>
        public BaseBusiness(IDbAccessor db, IDistributedCache cache, bool iscache = false)
        {
            _db = db;
            _cache = cache;
            if (iscache)
            {
                try { cache.Refresh(CacheName); IsCache = true; } catch { IsCache = false; }
            }
        }

        #endregion

        #region 私有成员
        /// <summary>
        /// 是否开启缓存(表中数据较少并且变化较少时建议使用，默认不开启)
        /// </summary>
        protected bool IsCache { get; set; } = false;
        /// <summary>
        /// 缓存名称
        /// </summary>
        protected string CacheName { get; set; } = typeof(T).Name;
        /// <summary>
        /// 主键字段
        /// </summary>
        protected PropertyInfo? KeyFiled
        {
            get
            {
                return typeof(T).GetProperties().Where(x => x.GetCustomAttribute<KeyAttribute>() != null).FirstOrDefault();
            }
        }
        #endregion

        #region 外部属性

        /// <summary>
        /// 业务仓储接口(支持软删除),支持联表操作
        /// 注：若需要访问逻辑删除的数据,请使用IDbAccessor.FullRepository
        /// 注：仅支持单线程操作
        /// </summary>
        public IDbAccessor _db { get; }
        /// <summary>
        /// 缓存接口
        /// </summary>
        public IDistributedCache _cache;

        #endregion

        #region 存在数据

        /// <summary>
        /// 存在数据
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public bool Exists(Expression<Func<T, bool>> condition)
        {
            return _db.GetIQueryable<T>().Where(condition).Count() > 0;
        }

        /// <summary>
        /// 存在数据
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public async Task<bool> ExistsAsync(Expression<Func<T, bool>> condition)
        {
            return await _db.GetIQueryable<T>().Where(condition).CountAsync() > 0;
        }

        #endregion

        #region 增加数据

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        public int Insert(T entity)
        {
            if (IsCache) OutRedis().Wait();
            return _db.Insert(entity);
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        public async Task<int> InsertAsync(T entity)
        {
            if (IsCache) await OutRedis();
            return await _db.InsertAsync(entity);
        }

        /// <summary>
        /// 添加多条数据
        /// </summary>
        /// <param name="entities">实体对象集合</param>
        public int Insert(List<T> entities)
        {
            if (IsCache) OutRedis().Wait();
            return _db.Insert(entities);
        }

        /// <summary>
        /// 添加多条数据
        /// </summary>
        /// <param name="entities">实体对象集合</param>
        public async Task<int> InsertAsync(List<T> entities)
        {
            if (IsCache) await OutRedis();
            return await _db.InsertAsync(entities);
        }

        /// <summary>
        /// 批量添加数据,速度快
        /// </summary>
        /// <param name="entities"></param>
        public void BulkInsert(List<T> entities)
        {
            if (IsCache) OutRedis().Wait();
            _db.BulkInsert(entities);
        }

        #endregion

        #region 删除数据

        /// <summary>
        /// 删除指定主键数据
        /// </summary>
        /// <param name="key"></param>
        public int Delete(string key)
        {
            if (IsCache) OutRedis().Wait();
            return _db.Delete<T>(key);
        }

        /// <summary>
        /// 删除指定主键数据
        /// </summary>
        /// <param name="key"></param>
        public async Task<int> DeleteAsync(string key)
        {
            if (IsCache) await OutRedis();
            return await _db.DeleteAsync<T>(key);
        }

        /// <summary>
        /// 通过主键删除多条数据
        /// </summary>
        /// <param name="keys"></param>
        public int Delete(List<string> keys)
        {
            if (IsCache) OutRedis().Wait();
            return _db.Delete<T>(keys);
        }

        /// <summary>
        /// 通过主键删除多条数据
        /// </summary>
        /// <param name="keys"></param>
        public async Task<int> DeleteAsync(List<string> keys)
        {
            if (IsCache) await OutRedis();
            return await _db.DeleteAsync<T>(keys);
        }

        /// <summary>
        /// 删除指定条件数据
        /// </summary>
        /// <param name="condition">筛选条件</param>
        public int Delete(Expression<Func<T, bool>> condition)
        {
            if (IsCache) OutRedis().Wait();
            return _db.Delete(condition);
        }

        /// <summary>
        /// 删除指定条件数据
        /// </summary>
        /// <param name="condition">筛选条件</param>
        public async Task<int> DeleteAsync(Expression<Func<T, bool>> condition)
        {
            if (IsCache) await OutRedis();
            return await _db.DeleteAsync(condition);
        }

        #endregion

        #region 更新数据

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <param name="properties">更新的实体属性</param>
        public int Update(T entity, List<string> properties = null)
        {
            if (IsCache) OutRedis().Wait();
            if (properties == null || properties.Count == 0)
            {
                return _db.Update(entity);
            }
            else
            {
                return _db.Update(entity, properties);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <param name="properties">更新的实体属性</param>
        public async Task<int> UpdateAsync(T entity, List<string> properties = null)
        {
            if (IsCache) await OutRedis();
            if (properties == null || properties.Count == 0)
            {
                return await _db.UpdateAsync(entity);
            }
            else
            {
                return await _db.UpdateAsync(entity, properties);
            }
        }

        /// <summary>
        /// 更新多条数据
        /// </summary>
        /// <param name="entities">数据列表</param>
        /// <param name="properties">更新的实体属性</param>
        public int Update(List<T> entities, List<string> properties = null)
        {
            if (IsCache) OutRedis().Wait();
            if (properties == null || properties.Count == 0)
            {
                return _db.Update(entities);
            }
            else
            {
                return _db.Update(entities, properties);
            }
        }

        /// <summary>
        /// 更新多条数据
        /// </summary>
        /// <param name="entities">数据列表</param>
        /// <param name="properties">更新的实体属性</param>
        public async Task<int> UpdateAsync(List<T> entities, List<string> properties = null)
        {
            if (IsCache) await OutRedis();
            if (properties == null || properties.Count == 0)
            {
                return await _db.UpdateAsync(entities);
            }
            else
            {
                return await _db.UpdateAsync(entities, properties);
            }
        }

        /// <summary>
        /// 指定条件更新
        /// </summary>
        /// <param name="whereExpre">筛选表达式</param>
        /// <param name="set">更改属性回调</param>
        public int Update(Expression<Func<T, bool>> whereExpre, Action<T> set)
        {
            if (IsCache) OutRedis().Wait();
            return _db.Update(whereExpre, set);
        }

        /// <summary>
        /// 指定条件更新
        /// </summary>
        /// <param name="whereExpre">筛选表达式</param>
        /// <param name="set">更改属性回调</param>
        public async Task<int> UpdateAsync(Expression<Func<T, bool>> whereExpre, Action<T> set)
        {
            if (IsCache) await OutRedis();
            return await _db.UpdateAsync(whereExpre, set);
        }

        /// <summary>
        /// 使用SQL语句按照条件更新(指定字段)
        /// </summary>
        /// <param name="where">筛选条件</param>
        /// <param name="fieldvalues">字段值设置</param>
        /// <returns>影响条数</returns>
        public int UpdateSql(Expression<Func<T, bool>> where, Dictionary<string, object?> fieldvalues)
        {
            if (IsCache) OutRedis().Wait();

            List<(string, UpdateType, object)> values = new List<(string, UpdateType, object)>();
            foreach (var item in fieldvalues)
            {
                values.Add((item.Key, UpdateType.Equal, item.Value));
            }

            return _db.UpdateSql(where, values.ToArray());
        }

        /// <summary>
        /// 使用SQL语句按照条件更新(指定字段)
        /// </summary>
        /// <param name="where">筛选条件</param>
        /// <param name="fieldvalues">字段值设置</param>
        /// <returns>影响条数</returns>
        public async Task<int> UpdateSqlAsync(Expression<Func<T, bool>> where, Dictionary<string,object?> fieldvalues)
        {
            if (IsCache) await OutRedis();

            List<(string, UpdateType, object)> values = new List<(string, UpdateType, object)>();
            foreach (var item in fieldvalues)
            {
                values.Add((item.Key, UpdateType.Equal, item.Value));
            }

            return await _db.UpdateSqlAsync(where, values.ToArray());
        }

        #endregion

        #region 查询数据

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="keyValue">主键</param>
        /// <returns></returns>
        public T GetEntity(params object[] keyValue)
        {
            #region 启用缓存
            if (IsCache)
            {
                return GetIQueryable().Where(p => KeyFiled.GetValue(p).ToString() == keyValue[0].ToString()).FirstOrDefault();
            }
            #endregion
            return _db.GetEntity<T>(keyValue);
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="keyValue">主键</param>
        /// <returns></returns>
        public async Task<T> GetEntityAsync(params object[] keyValue)
        {
            #region 启用缓存
            if (IsCache)
            {
                return GetIQueryable().Where(p => KeyFiled.GetValue(p).ToString() == keyValue[0].ToString()).FirstOrDefault();
            }
            #endregion

            return await _db.GetEntityAsync<T>(keyValue);
        }

        /// <summary>
        /// 根据条件获取数量
        /// </summary>
        /// <returns></returns>
        public int GetCount(Expression<Func<T, bool>> condition)
        {
            return GetIQueryable().Where(condition).Count();
        }

        /// <summary>
        /// 根据条件获取数量
        /// </summary>
        /// <returns></returns>
        public async Task<int> GetCountAsync(Expression<Func<T, bool>> condition)
        {
            var query = GetIQueryable().Where(condition);

            if (IsCache) return query.Count();
            return await query.CountAsync();
        }

        /// <summary>
        /// 获取表的所有数据，当数据量很大时不要使用！
        /// </summary>
        /// <returns></returns>
        public List<T> GetList()
        {
            return GetIQueryable().ToList();
        }

        /// <summary>
        /// 根据条件获取列表，当数据量很大时不要使用！
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public List<T> GetList(Expression<Func<T, bool>> condition)
        {
            var query = GetIQueryable().Where(condition);
            return query.ToList();
        }

        /// <summary>
        /// 获取表的所有数据，当数据量很大时不要使用！
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> GetListAsync()
        {
            var query = GetIQueryable();
            if (IsCache) return query.ToList();
            return await query.ToListAsync();
        }

        /// <summary>
        /// 根据条件获取列表，当数据量很大时不要使用！
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> condition)
        {
            var query = GetIQueryable().Where(condition);
            if (IsCache) return query.ToList();
            return await query.ToListAsync();
        }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="page">分页信息</param>
        /// <param name="condition">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public async Task<ValueTuple<List<T>, int>> GetListPageAsync(PageInput page, Expression<Func<T, bool>> condition, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy)
        {
            var iq = GetIQueryable().Where(condition);
            var query = orderBy(iq).Skip((page.PageIndex - 1) * page.PageRows).Take(page.PageRows);
            if (IsCache)
            {
                return (query.ToList(), iq.Count());
            }
            var list = await query.ToListAsync();
            int total = await iq.CountAsync();

            return (list, total);
        }

        /// <summary>
        /// 获取实体对应的表，延迟加载，主要用于支持Linq查询操作
        /// </summary>
        /// <returns></returns>
        public virtual IQueryable<T> GetIQueryable()
        {
            if (IsCache)
            {
                var listCache = GetRedis().Result;
                if (listCache != null)
                {
                    return listCache.AsQueryable();
                }
            }
            return _db.GetIQueryable<T>().AsNoTracking();
        }

        #endregion

        #region 执行Sql语句

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <returns></returns>
        public int ExecuteSql(string sql, params (string, object)[] paras)
        {
            if (IsCache) OutRedis().Wait();
            return _db.ExecuteSql(sql, paras);
        }

        /// <summary>
        /// 执行sql异步
        /// </summary>
        /// <returns></returns>
        public async Task<int> ExecuteSqlAsync(string sql, params (string, object)[] paras)
        {
            if (IsCache) await OutRedis();
            return await _db.ExecuteSqlAsync(sql, paras);
        }

        /// <summary>
        /// 通过Sql语句查询返回DataSet,参数化查询
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataSet GetDataSetWithSql(string sql, List<DbParameter> parameters = null)
        {
            DbProviderFactory dbProviderFactory = MySqlConnectorFactory.Instance;
            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _db.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }

                    DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    cmd.Parameters.Clear();

                    return ds;
                }
            }
        }

        #endregion

        #region 缓存数据
        /// <summary>
        /// 获取缓存
        /// </summary>
        /// <returns></returns>
        private async Task<List<T>?> GetRedis()
        {
            var strList = await _cache.GetStringAsync(CacheName);
            if (strList != null)
            {
                return strList.ToObject<List<T>>();
            }
            else
            {
                return await InRedis();
            }
        }

        /// <summary>
        /// 插入缓存
        /// </summary>
        /// <returns></returns>
        private async Task<List<T>> InRedis()
        {
            var list = await _db.GetIQueryable<T>().AsNoTracking().ToListAsync();

            await _cache.SetStringAsync(CacheName, list.ToJson(), new DistributedCacheEntryOptions()
            {
                AbsoluteExpirationRelativeToNow = new TimeSpan(24, 0, 0)
            });

            return list;
        }

        /// <summary>
        /// 移除缓存
        /// </summary>
        private async Task OutRedis()
        {
            await _cache.RemoveAsync(CacheName);
        }
        #endregion
    }
}